4

Suppose we have a poorly performing stored procedure with 6 parameters. If one of the six parameters is transferred to a local variable within the stored procedure, is that enough to disable parameter sniffing or is it necessary to transfer all 6 parameters that're passed to the stored procedure into local variables within the stored procedure?

John Odom
  • 1,189
  • 2
  • 20
  • 35
knot22
  • 2,648
  • 5
  • 31
  • 51
  • 3
    Read up on [SQL Parameter Sniffing](http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/). This will give you a clear understanding on what parameter sniffing is. – John Odom May 05 '15 at 19:20
  • 1
    SQL Server. Version 8 to be exact. – knot22 May 05 '15 at 19:21
  • What specific problem are you trying to resolve? Are some or all of the parameters "optional"? Is it a [catch-all query](http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/)? – Dave Mason May 05 '15 at 19:38
  • 3
    The gospel of [parameter sniffing](http://sommarskog.se/query-plan-mysteries.html) – billinkc May 05 '15 at 19:38
  • I think knot22 already demonstrates a level of understanding of parameter sniffing from his question. I would say to knot22, you should just test it out. Give another parameter that you know should give a different plan.. Check the cache to see if the count increased where it shouldnt have. – uh_big_mike_boi May 05 '15 at 19:40
  • @DMason - yes, 3 out of the 6 parameters are optional. – knot22 May 05 '15 at 19:41
  • The second book of the gospel according to erland is [Dynamic Search Conditions](http://sommarskog.se/dyn-search.html). Highly recommend it – billinkc May 05 '15 at 19:42
  • @Mike_L - Yes, I have been testing out the sproc and when one of the non-optional parameters is transferred to a local variable and the local variable is used in the WHERE clause the sproc's performance improves tremendously (drops from 142,000 ms to 200 ms). Was hoping to get some input from Stackoverflow to see if parameter sniffing can be disabled by just one local variable. Other sprocs I've seen at work that handle parameter sniffing do so by transferring all the sproc's variables to local variables. – knot22 May 05 '15 at 19:45

2 Answers2

7

Per Paul White's comment, assigning a variable to a local variable is a workaround from older versions of SQL Server. It won't help with sp_executesql, and Microsoft could write a smarter parser that would invalidate this workaround. The workaround works by confusing the parser about a parameter's value, so in order for it to work for each parameter, you'd have to store each parameter in a local variable.

More recent versions of SQL Server have better solutions. For an expensive query that is not run often, I'd use option (recompile). For example:

SELECT *
FROM YourTable
WHERE col1 = @par1 AND col2 = @par2 AND ...
OPTION (RECOMPILE)

This will cause the query planner to recreate ("recompile") a plan every time the stored procedure is called. Given the low cost of planning (typically below 25ms) that is sensible behavior for expensive queries. It's worth 25ms to check if you can create a smarter plan for specific parameters to a 250ms query.

If your query is run so often that the cost of planning is nontrivial, you can use option (optimize for unknown). That will cause SQL Server to create a plan that it expects to work well for all values of all parameters. When you specify this option, SQL Server ignores the first values of the parameters, so this literally prevents sniffing.

SELECT *
FROM YourTable
WHERE col1 = @par1 AND col2 = @par2 AND ...
OPTION (OPTIMIZE FOR UNKNOWN)

This variant works for all parameters. You can use optimize for (@par1 unknown) to prevent sniffing for just one parameter.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Problem is with having to support multiple versions of SQL Server in software. I had to support 2005 up to 2012, and ended up using the "assign to local variable" trick. It still works on 2012, can't speak for later versions. – TT. May 05 '15 at 21:15
  • OPTION (OPTIMIZE FOR UNKNOWN) worked for this stored procedure to improve performance. – knot22 May 05 '15 at 21:33
  • Prior to adding this query hint the act of transferring one of the non-optional parameters to a local variable and using that in the WHERE clause also improved the stored procedure's performance to around 200 ms. This suggests the work around of assigning a variable to a local variable for just one parameter sufficiently disables parameter sniffing. However, this test result conflicts with Andomar's last sentence in the first paragraph. – knot22 May 05 '15 at 21:38
  • It might be that only the value of first parameter influences the query plan. What happens when you only store the last parameter in a local variable? – Andomar May 06 '15 at 06:23
  • @Andomar: I think you're on to something here. When the last parameter was stored in a local variable the stored procedure reverted to performing as poorly as before. – knot22 May 06 '15 at 15:45
0

We have a quite compley query which 5 CTEs and an union already known for parameter sniffing from its design.

We opted for OPTION RECOMPILE to solve it and it works fairly good.

  • After 2 years we created a high available cluster and separated the report server.
  • All works well for 1 year and now pressured by the Covid19 measures we did shut down for 30 days.
  • Server is on but all activity goes quite.
  • Meanwhile we need to truncate the database because of extreme log size and data growth, taking out of available groups and re adding to availability.

Recently this query shows parameter sniffing activity and no remedy works except one: EXEC sp_updatestats.

EXEC sp_updatestats works for me, and now I have time to find a proper solution for a permanent fix.

surfmuggle
  • 5,527
  • 7
  • 48
  • 77
Ayan_84
  • 645
  • 1
  • 6
  • 18