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) )
AS 
DECLARE	@TSQL NVARCHAR(MAX) ,
	@Params NVARCHAR(MAX) ;

SET @TSQL = N'SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.[Name] AS StateProvinceName,
        a.PostalCode
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:

ExecPlan

 

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:

Properties

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:

ExecPlan2

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.

7 thoughts on “sp_executesql, Parameters and Parameter Sniffing

  • Paul Clark

    “Parameter sniffing absolutely applies to sp_executesql.”
    I would be grateful if you could expand on this. I have read plenty of documents where the use of sp_executesql is claimed as a possible solution for parameter sniffing. If you could elaborate I would be grateful. Thanks for your attention.

  • Not sure what else to say. You can see that using different parameter values resulted in different execution plans. That’s because the distribution of the data as shown in the statistics is different for those values. These are parameters like any other and they can be sniffed. There’s nothing special about sp_executesql that changes this.

  • Jason

    I think I’m running into a parameter sniffing issue while using sp_executesql.

    We have a big SP we’ve inherited that builds a giant SELECT statement based on about 20 parameters, and then runs that statement with sp_executesql.

    Normally when I have a SP like this I would do the standard “declare local variables and set them to the passed in values” trick (or perhaps slap a WITH RECOMPILE on the SP).

    Is there a similar “trick” you can use to make SQL not cache any plans when using sp_executesql inside a single SP?

    The best thought I have is to put something inside the SP that does a DBCC FREEPROCCACHE for any plan handles that match the signature of the call to sp_executesql, but that somehow seems like a redneck solution.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.