sp_executesql, Parameters and Parameter Sniffing

, 2013-09-05

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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads