THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Execution plan re-use, sp_executesql and TSQL variables

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/execution-plan-re-use-sp_executesql-and-tsql-variables/

Published Friday, August 29, 2008 9:34 AM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Michael Zilberstein said:

That's a parameter sniffing problem. I my reporting application I use OPTION(RECOMPILE) hint on statement level for statements that run on unevenly distributed data. Those statements don't run hundreds times a second, e.g. I don't pay too much for recompilation overheard. On the other hand cost of inefficient execution plan (in case of reuse - without RECOMPILE hint) would be much higher. Those are online reports, so couple of milliseconds delay for all users is still cheaper than seconds or even tens of a seconds delay for part of the users.

August 29, 2008 6:39 AM
 

TiborKaraszi said:

I wouldn't call parameter sniffing a "problem" per se. I would say that parameter sniffing is a behaviour that one should be aware of and from that awareness one can decide whether one like that behaviour and want to explore it or if one want to avoid it (using for instance OPTION RECOMPILE).

My point, however, with this post was more to question such a blanket statement such as DTA was reported in that newsgroup post to have made. And in general question various rules of thumbs that we all find here and there...

August 29, 2008 7:12 AM
 

Ranga said:

Very good analysis...

--Ranga

September 3, 2008 4:24 PM
 

r5d4 said:

Definitely adding this to my list of things to try.

Made excellent reading.

r

September 10, 2008 7:21 PM
 

ibrahim Özbek said:

OPTION RECOMPILE is not the best way. Because as u can understand it recompiles executionplan so it takes time. I have a query like this normal query takes less than 1 second query with sp_executesql takes 37 seconds :) wtih RECOMPILE 27 seconds :))

September 19, 2008 10:25 AM
 

TiborKaraszi said:

I would not call OPTION RECOMPLIE either a "good" or a "bad" option. It is an option and *used in the right situation* it can be very valuable.

If you have a query where compile time is very high and difference in execution time is low, then we would *not* want to use this option.

But what about the other way around? Say that compile time is only a few milliseconds, and difference in execution time is several seconds, or even minutes? Here we definitely have a candidate for OPTION RECOMPILE (although there are also other options for us).

September 22, 2008 1:18 AM
 

gmasselli said:

We had the exact problem some user here reported: same query using sp_executesql ran in 21 seconds, ad-hoc from mgmt studio less than a second. Reading the white paper was extremely useful for understanding how, internally, Sql Server was working the queries; thanks for the link!

Our situation was resolved by increasing the sampling size of the statistics for two of our tables used in the query. Once this was done both queries used the same query plan and, more importantly, both executed in under a second. Problem solved!

December 18, 2008 11:29 AM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement