Detecting Possible Parameter Sniffing Occurences with DMV

  • [アーティクル]

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

Comments

  • Anonymous
    September 09, 2016
    Nice post! Thks!