Sergiy (8/31/2016)
Grant Fritchey (8/31/2016)
Sergiy (8/30/2016)
JALLYKAMOZE (8/29/2016)
What is the best and permanent way to take care of parameter sniffing?A clustered or covering index having the column used for the range selection as a first column in the index definition.
How does that help out of date statistics or skewed data/statistics? It's not a permanent fix for those situations.
Range selection, if it's along clustered index, will choose clustered index seek regardless of statistics.
Btw, skewed or perfectly maintained statistics won't make any difference, if the values of perameters are not "visible" to optimiser when the query is compiled. Recompilation will only help if parameters are explicitly included into a dynamic SQL query.
I'd be shocked if your guaranteed behavior regardless of statistics, especially regardless of statistics.
Parameters are visible. It's why it's called parameter sniffing. Variables are not visible except during a recompile. Parameters come from stored procedure parameter definitions, or prepared statement parameter definitions. Variables are local variables, regardless of the type of query they're running within.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning