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?