Parameter Sniffing On Conditional Statements

Kendra Little explains that SQL Server will cache parameter values for invalid statements:

The first time that dbo.ReviewFlags is executed after the database comes online, it’s with an invalid parameter, like this:

  • EXEC dbo.ReviewFlags @Flag = null;
  • GO

This is caught by the IF block, hits the RAISERROR, and goes down to the THROW block, and the output is:

  • Msg 50000, Level 11, State 1, Procedure ReviewFlags, Line 8 [Batch Start Line 70]
  • @Flag must be a value between 1 and 5

But even though SQL Server didn’t execute the SELECT statement, it still compiled it. And it also cached the plan.

Read on to understand the trouble this can cause, as well as a few ways of solving the problem.  This is a special case of parameter sniffing problems, but the solutions are the same as in the general case.

Related Posts

Prioritizing Rows In A Union

Daniel Hutmacher shows several ways to combine data from multiple sources, prioritized by source: For the sake of simplicity, I’ll assume that our example data has a clustered index on the “primary key” that we want to use to determine which rows have already been included in the set – in our example, the primary […]

Read More

SQL Server 2017 Finds Plan Regressions

Jovan Popovic shows off some automatic tuning functionality in SQL Server 2017: Plan change regression happens when SQL Database changes a plan for some T-SQL query, and the new plan has the worse performance than the previous one. SQL Server 2017 has Automatic Tuning feature that enables you to easily find plan change regressions and fix them. […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

July 2017
MTWTFSS
« Jun  
 12
3456789
10111213141516
17181920212223
24252627282930
31