O'Reilly logo

Guru's Guide to SQL Server Architecture and Internals, The by Ken Henderson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Parameter Sniffing

Prior to compiling an execution plan for a stored procedure, SQL Server attempts to “sniff” (i.e., discern) the values of the parameters being passed into it and use those values when compiling the plan. When these values are being used to filter a query (i.e., as part of a WHERE or HAVING predicate), this allows the optimizer to produce a more precise execution plan tailored to the values being passed into the stored procedure (using the statistics histograms for the columns they're used to filter) rather than based solely on the average density of the relevant table columns. Generally speaking, this is a good thing and results in improved performance over older versions of SQL Server.

You can get into trouble, however, when ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required