question

AleyDede avatar image
AleyDede asked

SQL Server Parameters Make Dynamic Query Slow

I ran into a weird problem caused by the parameters passed into dynamic SQL. The dynamic SQL had parameters all through it like so: SET @sql = @sql + N'AND ( Book.ID = @paramBookID) ' IF (NOT @AuthorID IS NULL) SET @sql = @sql + N'AND (Book.AuthorID = @paramAuthorID) ' And I then passed in the parameters to the sp_executeSql call like so: -- Execute the parameterized dynamic SQL query DECLARE @params NVARCHAR(500) = N'@paramBookID int, @paramAuthorID int' EXEC sp_executesql @sql, @params, @paramBookID = @BookID, @paramAuthorID = @AuthorID The dynamic query was very slow, and at first I thought it was the dynamic SQL execution that was causing the problem, so I created the dynamic SQL and copied it over to SSMS. I then declared all the variables at the top of the query and it executed much faster (about a second, instead of 1+ minutes) I then did some more fiddling around, and replaced a line where there was a variable with the actual value of the variable: Replaced: AND (Book.AuthorID = @paramAuthorID) With: AND (Book.AuthorID = 1471409) The query started running slow again. Not sure why this caused problems and made the query run so slow. I even tried this: AND (Book.AuthorID = CAST(1471409 AS INT)) because I thought maybe it was some sort of typing issue, but that didn’t help either, it still ran slow, So in the end I ended up making a slight modification to the dynamic sql to copy all the parameters over into local variables in the dynamic SQL: SET @sql = N'DECLARE @BookId INT = @paramBookID ' + N'DECLARE @AuthorID INT = @paramAuthorID ' + @sql The query now runs WAY faster, and I still have no explanation as to why. Does anyone have any insight into this weird solution I stumbled upon?
sql querydynamic-sqlexecute-sql-taskdynamic sqlint
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The phrase you're looking for is "Parameter Sniffing". Someone will be along shortly to explain it better than I ever could.
2 Likes 2 ·
@ThomasRushton I wish I could give you 10000 karma points for that statement. Personally, I think you won the internet for today with it ;-)
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
Not sure why no one wants to write the answer, so I will. Parameter sniffing is a process in SQL Server directly related to parameters (and local variables during a recompile). What happens is that the values passed to parameters are used against the statistics of the tables in question to identify specific values and the number of rows returned. With specific numbers of rows, the optimizer makes specific choices in how it's going to resolve the query. In most circumstances, this is all a positive and good thing. It sometimes goes wrong. This can be caused by out of date statistics or by data skew. The optimizer will think that your query has few/many rows and will build an execution plan, but your query actually has many/few rows for all the other possible values passed to it so the execution plan performs very poorly. Solutions when parameter sniffing goes wrong are around understanding if you need a generic, sampled execution plan, or a specific one. You can use the query hint OPTIMIZE FOR UNKOWN for the generic approach, or OPTIMIZE FOR some value to get a specific plan. There's a lot more to it. I wrote a chapter for the MVP Deep Dives II on parameter sniffing. I also have a one hour presentation I give on it.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.