sp_executesql()

Question

sp_executesql()

PHXHoward

SSCrazy Eights

Points: 8389
More actions
February 15, 2012 at 4:55 pm

#264516

Hi,
Does anyone know why or how sp_executesql() affects query performance?
I found today that a select statement took 11 minutes to complete when it was inside sp_executesql().
Taking it out of the sp_executesql() then running it allowed it to complete in 1 second.
Putting the query into the tuning adviser suggested some new stats and indexes for and 84% improvement. After applying the new stats and indexes, the query completed in 1 second even when inside the sp_executesql().
Can anyone explain why this stored procedure impacts performance so much?
Thanks for reading.
Howard

Viewing 8 posts - 1 through 7 (of 7 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Was the sp_executeSQL statement parameterized? It might be caching a bad plan the same way bad parameter sniffs affect queries. This would be my most likely assumption. I'd treat it the same way you'd treat fixing a parameter sniff. New and updated statistics along with the new indexes probably help with the poor plan as well.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

I had a situation a few months ago in which a search proc with a bunch of messy conditional logic was replaced with fully parameterized dynamic sql. The proc in question gets called about 7-8,000 times an hour in peak hours. For 3 days (easily hundreds of thousands of calls) the new proc was fantastic ... each execution plan for the different parameter sets was very clean and we were getting the performance gained hoped for. Then, all of the sudden, the plans started going to hell and were not at all consistent with the plans that were generated for the same statements executed outside of the dynamic sql. We rolled back to the old slow (but consistent) proc and I am still trying to figure out what happened and what I can do to prevent it from killing my server.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

PHXHoward (2/15/2012)
Hi,
Does anyone know why or how sp_executesql() affects query performance?
I found today that a select statement took 11 minutes to complete when it was inside sp_executesql().
Taking it out of the sp_executesql() then running it allowed it to complete in 1 second.
Putting the query into the tuning adviser suggested some new stats and indexes for and 84% improvement. After applying the new stats and indexes, the query completed in 1 second even when inside the sp_executesql().
Can anyone explain why this stored procedure impacts performance so much?
Thanks for reading.
Howard

Not only does the use of sp_executesql() help protect from SQL Injection, it also strongly encourages parameter sniffing so that nearly identical queries that only have parameter value changes can use the same execution plan instead of having to build a new one like "normal" dynamic SQL would.

But parametr sniffing isn't a performance panacea. Craig kind of hit on it a bit in his post. If something changes, the performance plan may no longer be optimal. And it can be nearly anything that changes but what probably happend is that the proc was driven out of cache by an overnight maintenance plan and someone in the morning used a parameter that produced a new execution plan that just isn't the best thing to use for other values. It could also have simply reached a "tipping" point by the addition of even a single row and you now need that new index you found.

Sometimes a proc with a recompile isn't so bad after all. 🙂

--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)

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 4

Thanks for the replies. Interestingly with or without parameters, it still executes very slowly when inside the SP.

Going to look at the execution plan both ways tomorrow to see if it sheds light.

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

PHXHoward (2/15/2012)
Thanks for the replies. Interestingly with or without parameters, it still executes very slowly when inside the SP.
Going to look at the execution plan both ways tomorrow to see if it sheds light.

That's quite a bit different than your original description.

Chances are, then, that you have a lot of stuff going on and page life expectency is short. Stuff is falling out of cache and you may have to do a bit of a rewrite so it doesn't require as many resources. Looking at the execution plan as you say is an excellent place to start.

--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)

Jared SSC Guru Points: 61793 More actions · Answer 6

Certainly sounds like parameter sniffing. Can you post the DML for the query? I'd like to see what is going on there.

Jared
CE - Microsoft

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 7

Running outside the sp, it completes in 1 - 2 seconds but has a message in the execution plan that adding a new index could improve performance.

Adding the index as recomended reduces the execution time of the sp from 12 minutes to 2 seconds to match the non-sp result.

With or without paramaters in this case has no impact on the performance inside sp_executesql().