SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

sp_executesql, Parameters and Parameter Sniffing

I’m honestly not crazy about dynamic T-SQL within stored procedures. There are just a few too many opportunities to mess it up with dire circumstances to your server and your data. However, I absolutely recognize that dynamic T-SQL may be needed, and, in some situations, the best way to solve a problem. If you must use dynamic T-SQL, there are ways that are much more efficient than others. The very best thing you can do if you need to build dynamic strings to execute T-SQL in your stored procedures is use sp_executesql.

The main reason I advocate for sp_executesql is because you can build out completely dynamic strings of T-SQL, but, you can still take advantage of parameters. Parameters help you avoid a chat with the parents of Bobby Tables and can get you much more plan reuse. However, it’s worth noting that, because these are parameters, you’re going to have to deal with the good, and the bad, of parameter sniffing. Let’s see it at work.

Here’s a stored proc that uses sp_executesql (for no good reason, but this is just an example):

CREATE PROCEDURE dbo.DynamicAddressByCity ( @City NVARCHAR(30) )
	@Params NVARCHAR(MAX) ;

SET @TSQL = N'SELECT  a.AddressID,
        sp.[Name] AS StateProvinceName,
FROM    Person.Address AS a
        JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City;'

SET @Params = N'@City NVARCHAR(30)';

EXECUTE sp_executesql @TSQL, @Params, @City = @City;

If I execute this query:

EXEC dbo.DynamicAddressByCity @City = N'London';

Then I’ll get an execution plan that looks like this:



If I were to re-execute the query using a different parameter:

EXEC dbo.DynamicAddressByCity @City = N'Mendon';

I’ll end up with the same execution plan. If we look at the properties for the SELECT operator:


We can see that the plan was compiled using a parameter value of ‘London’ and executed using a parameter value of  ’Mendon.’ I already hear people saying, “So what?” Well, let’s remove the query from cache. I’ll query the Dynamic Management Objects (DMO) to get the plan handle and then remove it from cache using FREEPROCCACHE with the plan handle. Now, we’ll re-execute the query but using the parameter value of ‘Mendon.’ Here is the resulting execution plan:


Yeah, different right. That’s because of the differences in the statistics between the two values. ‘London’ will return over four hundred rows while ‘Mendon’ will only return two. Those differences, accurately portrayed within the statistics for the column, result in different execution plans because the parameters were ‘sniffed’.

Parameter sniffing absolutely applies to sp_executesql. While you should be using those parameters, don’t lose sight of the fact that this could lead to bad parameter sniffing.

For lots more on query tuning, if you’re in the Dallas area, I’ll be putting on an all-day pre-conference seminar on Friday, November 1, 2013, before SQL Saturday 255. You can go here to sign up for the event. Hurry, the early bird offer is going to expire soon.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...