Detecting Possible Parameter Sniffing Occurences with DMV
Published Jan 15 2019 05:12 PM 397 Views
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*/


SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

((CASE statement_end_offset


ELSE QS.statement_end_offset END

- QS.statement_start_offset)/2) + 1) AS statement_text ,

ST.text as 'full batch',




from sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST


/*'strictly smaller than' will rule out any 0 values from resultset*/

@cpufactor * min_worker_time < max_worker_time


@iofactor * min_logical_reads < max_logical_reads


@durationfactor * min_elapsed_time < max_elapsed_time


Guillaume Fourrat

Escalation Engineer
Version history
Last update:
‎Jan 15 2019 05:12 PM
Updated by: