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 ?
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.