sp_executesql hangs for a particular stored procedure

Question

sp_executesql hangs for a particular stored procedure

Ajay Prakash

SSC Eights!

Points: 891
More actions
January 18, 2008 at 5:08 am

#185757

We have a randomly reoccurring issue (that cannot be easily replicated) on our Microsoft SQL Server 2005 - 9.00.3050.00 (Intel X86) Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2).
Server boxes (both Production and Development) where the query cache appears to corrupt itself resulting in the query and never returns. The query is not being blocked by any locks (and in fact the situation occurs when no other connections to the database) and as far as we can see the query itself does not execute.
The situation occurs when using sp_executesql with or without parameters. Restarting Sql Server or taking the database offline and then bringing it back online corrects the problem. More importantly (for diagnostic purposes) if you modify the parameter names passed into the stored procedure (thereby creating a different cache entry) the situation does not occur.
Kindest Regards,
Ajay Prakash

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

sp_executesql is probably being used after building a dynamic sql string...

the inherent problem with that is you could execute a command which makes data changes, but makes so many changes that the statistics are no longer valid . and when the stored query plan tries to run, the statistics is used previously are no longer valid, and the result is a long running as in never returns) query.

the step immediately before the line of code calling sp_executesql is most likely the culprit...you might want to post it here for more peer review. We might be able to help remove the dynamic sql to change it to static sql, which would help the query plan a bit.

just before the offending sp_executesql is called, add a line to update the statistics of each table the query would be selecting frim...I'll bet you'll get an immediate improvement.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ajay Prakash SSC Eights! Points: 891 More actions · Answer 2

Thanks for the suggestion. 🙂 Your answer hit the bull eye.

However I could not get the query from Application team.

DBCC FREEPROCCACHE resolved the issue for the time being.

Could you pls suggest how can we change the query with some example so as to avoid such problems.

Kindest Regards,

Ajay Prakash

Lowell SSC Guru Points: 323518 More actions · Answer 3

there's so many variables, it's hard to give a good answer without reviewing the code;

In general, here's something to look at:

A lot of times, dynamic sql is used in order to centralize code into one stored proc...the problem is, if the procedure does more than one thing, you could have the issues above.

Procedures benefit from saved execution plans.

For example, if it runs an update and a query based on zip code, the plan is optimized on zip code.

if it then builds a different query based on say , county, the old plan based on zip code's not relevant to the new query.

Have the team look at the procedure, and see if it is doing more than one thing based on different passed parameters....if it's updating based on whether one parameter or another, ideally you'd want to pull out the logical paths in sub-procedures that are called by athe master procedure, instead of dynamic sql in a single proc.

If you can provide more details, we could go beyond general ideas.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ajay Prakash SSC Eights! Points: 891 More actions · Answer 4

Thanks again. Your explanation will work as a good guideline for me to for further investigation.

Kindest Regards,

Ajay Prakash

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Instead of doing the FREEPROCCACHE thingy, which affects the whole server, just do a forced recompile in the proc. The other thing this sounds like is "Parameter Sniffing". Sometimes it's good, sometimes (like maybe in this case), it's very bad.

There's a boat load of information on the WEB about "parameter sniffing" and how to prevent it in cases like this... Google or Yahoo it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)