Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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)

share|improve this answer
    
I'll definitely give that a shot and report back. –  Ducain Feb 8 '11 at 16:33
    
Ok, marking this as the answer, because it is most definitely parameter sniffing - once I made changes to the queries in question and disabled this - runs like a dream. One other question for you if I could though - we have literally hundreds of stored procedures. Is it necessary for me to do these changes to them all, or is there a better global option for disabling the parameter sniffing? Thanks MUCH. –  Ducain Feb 8 '11 at 17:46
    
Sorry, don't know of a global way... –  gbn Feb 8 '11 at 17:54
    
10-4. Ouch. :-) –  Ducain Feb 8 '11 at 17:59
    
Just because I don't know does not mean there is no way. Ask on Stackoverflow...? –  gbn Feb 8 '11 at 18:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.