I've been encountering intermittent database performance issues where basically the CPU spikes to 90+ percent somewhat randomly.
After running activity monitor I'm noticing when the CPU is spiked high there are a few queries that seem extremely higher than the rest of the queries in terms up CPU, and Executions/min. They all were quering various RSD Tables/Views.
Didn't put two and two together until I realized this activity is from the RSD Dashboard. It's unreasonably slow if we take into consideration the amount of data that is being queried. Event data and queries should be way more expensive if we think of shear amount of data. So delving into it more I've noticed that the RSDSubnet view runs two functions RSDFN_SubnetContainsRogueSystems and RSDFN_IsSubnetCovered... It turns out the IsSubnetCovered appears to be the culprit. Without going further I can't say whether this is the main reason for the CPU spikes, but it's just one obvious expensive query that I believe could be streamlined.
My primary questions are:
1. Is everyone experiencing 'slow' downs when the dashboard or related 'covered' systems queries are run?
2. If we have a suggestion on speeding up the RSDFN_IsSubnetCovered function has anyone seen them implement things in the 'idea's forum?
Yes we are also seeing the expensive RSD queries. We have had to go as far as putting ePO on it's own DB server and instance as it was causing major problems with other databases that were on the same server. This only started happening relatively recently. I can't say for certain when but I think one of the 4.6 release introduced this issue. It would be nice to have it fixed.
Did you manage to resolve this or does anyone else have any ideas or suggections for resolving this ?
Message was edited by: HawkEye4077 on 4/3/13 3:35:00 AM CDT
I haven't heard of anything particularly related to those two functions... the first thing that springs to mind is index fragmentation: if you reindex the DB, does it improve matters?
We recently ran an idex rebuild and that didn't seem to make any real difference. The affect can be clearly seen when entering the Detected Systems page as the subnets box (top left) takes much longer to populate, sometimes it takes almost as long as the default page refresh i.e. the box completes and seconds later the page refreshes.
This only seemed to start after a relatively recent upgrade. Maybe it is related to upgrading through several versions ?
We've somewhat addressed the issue, the left hand 'subnets box' still takes a while to load, but it is no longer affecting the SQL Server itself.
It's not an indexing issue, I've found that it has to do with Scalar function calls within calls. It's a known SQL Server source of slow downs. A temp table or other method would create their results much faster.
First we removed the numerous unnecessary sensors. We had a very large number of sensors installed on everything. That seemed to have a major effect.
Next we noted that there is a 'Detected Source' table that adds an entry everytime a source is detected by any sensor. This was growing fast based on the large number of sensors in each subnet. This doesn't appear to ever have gotten purged I do not believe the 'detected systems' purge clears this table as well.
After performing both, no more SQL Server issues. Note that the subnets box still takes a whilte to load.