SPROC hangs in SQL Server 2005

Question

I get a problem with SQL Server 2005, where a stored procedure seems to randomly hang/lock, and never return any result.

What the stored procedure does is to call a function, which in turn makes a union of two different functions – returning the same type of data, but from different criteria. Nothing advanced. I don’t think it’s the functions hanging, because there are other SPROCs that call the same functions without a problem, even when the first one has locked up.

After the SPROC hangs, any further attempts to call it will result in a time out – not in the call itself, but the response time will be too great, as no result is returned the code will throw an exception.

It has happened at least three times in two months in a relatively low-load system. Restarting SQL Server solves the situation, but I don’t regard that as a “solution” to the problem.

I’ve looked for information, and found something about the query cache going corrupt. However, that was in regard to dynamic SQL strings, which my problem is not. I guess it could still be the query cache.

Has anyone had the same problem, and if so, what did you do about it (don’t say “restart SQL Server every morning” ;) )? Is there any way of debugging the issue to try and find exactly what and where things go wrong? I can’t recreate the problem, but when it appears again it would be good if I knew where to take a closer look.

I don't think it makes any difference, but just for the record, the SPROC is called from .NET 3.5 code, using the Entity Franework. I say it doesn't make a difference, because when I've tested to just execute the SPROC directly from SQL Server Management Studio, no result is returned either.

Answer 1

Are your STATISTICS up to date? One of the common causes of an cached query plan that is incorrect, is out of date statistics.

Do you have a regularly scheduled index rebuild job?

Answer 2

Hi,

Did you verify the SQL Server log..? Defenetly the cause for the problem is been logged.. atleast you can get some hint about that. pls check that.

This excellent MSDN Article SQL Server technical bulletin - How to resolve a deadlock explains the steps needed to identify and resolve the deadlock issues in very detail.

Cheers

Answer 3

It's most likely parameter sniffing

Restarting SQL server clears the plan cache. If you rebuild statistics or indexes the problem will also go away "ALTER INDEX" and "sp_updatestats"

I suggest using "parameter masking" (not WITH RECOMPILE!) to get around it

SO answer already by me:

• One
• Two