First published on MSDN on Sep 05, 2016
Hi All,
Parameter Sniffing is a scenario which most DBAs had to handle at some point. Usually the main challenge is to detect it, as the toolkit to address it is well known and documented. To help in the detection phase, here's a rather simple query that will just report queries which have a large gap between their minimum and maximum resource consumption values (100 by default). Although not guaranteed parameter sniffing occurences, the content of the short list the query will return should receive a quick review : that means that a given plan has been executed with massively different resource usage, and one could assume that one same plan shouldn't be optimal over that whole range of situation.
By enabling the filter for Duration and resource consumption (CPU, IO or both) one can filter out the impact of blocking (which
doesn'
t increase resource consumption).
Looking at resultset's rowcount variance is advised, but TOP and Aggregates queries will have a stable rowcount that may mask a vastly different amount of data accessed to produce the result, so a lack of difference is not a reliable indicator (a large difference, on the other hand, is a further confirmation).
The 100 factor can easily be tweaked for finer or coarser grain detection depending on environments.
declare @cpufactor int
declare @iofactor int
declare @durationfactor int
/* parameter sniffing will usually lead to large CPU, IO and Duration increases.
checking duration only might fail to distinguish it from blocking problems*/
set @cpufactor=100 /* 0 disables*/
set @iofactor=100 /* 0 disables*/
set @durationfactor = 100 /* 0 disables*/
select
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text ,
ST.text as 'full batch',
query_hash,
query_plan_hash,
*
from sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
where
/*'strictly smaller than' will rule out any 0 values from resultset*/
@cpufactor * min_worker_time < max_worker_time
and
@iofactor * min_logical_reads < max_logical_reads
and
@durationfactor * min_elapsed_time < max_elapsed_time
Thanks,
Guillaume Fourrat
Escalation Engineer
