We have a sizable SQL database (24 GB) on which our users run reports. Some of the reports are intensive, but have generally ran quickly – couple of minutes.
We are seeing an issue in recent weeks where these same reports will take 20 minutes, which doesn't make sense – none of the report SQL has been changed.
During my investigation, I find that there are hundreds (currently over 300) sleeping tasks in the activity monitor. We have around 30 users on workstations, and maybe a dozen web users (using a portal website that accesses this data). So 300 sleeping processes seems crazy to me.
The other bit of random info is that if we restart the SQL server, the reports run like lightning. After a couple of hours though – crap.
I'm trying to track this issue down, and I'm not sure how to, but I suppose the question at hand is this: could these sleeping processes that pile up be degrading performance enough to cause a slowdown like this?
Thanks much.
Best Answer
Sleeping processes consume some memory (a few dozen k for each) but 300 isn't many.
If the reports are lightening fast on restart, it's possible you have parameter sniffing.
You can test for this for running
sp_updatestats: this invalidates cached execution plans and should make the reports whizzy again.Parameter sniffing itself can be solved by masking or hints (plenty of info on StackOverflow)