SQL Server - parameter sniffing -

i've read many articles parameter sniffing, it's not clear if or bad. can explain simple example.

is there way automatically detect wrong plan assigned specific statement?

thanks in advance.

it can bad sometimes.

parameter sniffing query optimizer using value of provided parameter figure out best query plan possible. 1 of many choices , 1 pretty easy understand if entire table should scanned values or if faster using index seeks. if value in parameter highly selective optimizer build query plan seeks , if not query scan of table.

the query plan cached , reused consecutive queries have different values. bad part of parameter sniffing when cached plan not best choice 1 of values.

sample data:

create table t (   id int identity primary key,   value int not null,   anothervalue int null );  create index ix_t_value on t(value);  insert t(value) values(1);  insert t(value) select 2 sys.all_objects; 

t table couple of thousand rows non clustered index on value. there 1 row value 1 , rest has value 2.

sample query:

select * t  value = @value; 

the choices query optimizer has here either clustered index scan , check clause against every row or use index seek find rows match , key lookup values columns asked in column list.

when sniffed value 1 query plan this:

and when sniffed value 2 this:

the bad part of parameter sniffing in case happens when query plan built sniffing 1 executed later on value of 2.

you can see key lookup executed 2352 times. scan better choice.

to summarize parameter sniffing thing should try make happen as possible using parameters queries. can go wrong , in cases due skewed data messing statistics.

update:

here query against couple of dmv's can use find queries expensive on system. change order clause use different criteria on looking for. think totalduration place start.

set transaction isolation level read uncommitted;  select top(10)   plancreated       = qs.creation_time,   objectname        = object_name(st.objectid),   queryplan         = cast(qp.query_plan xml),   querytext         = substring(st.text, 1 + (qs.statement_start_offset / 2), 1 + ((isnull(nullif(qs.statement_end_offset, -1), datalength(st.text)) - qs.statement_start_offset) / 2)),   executioncount    = qs.execution_count,   totalrw           = qs.total_logical_reads + qs.total_logical_writes,   avgrw             = (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count,   totaldurationms   = qs.total_elapsed_time / 1000,   avgdurationms     = qs.total_elapsed_time / qs.execution_count / 1000,   totalcpums        = qs.total_worker_time / 1000,   avgcpums          = qs.total_worker_time / qs.execution_count / 1000,   totalclrms        = qs.total_clr_time / 1000,   avgclrms          = qs.total_clr_time / qs.execution_count / 1000,   totalrows         = qs.total_rows,   avgrows           = qs.total_rows / qs.execution_count sys.dm_exec_query_stats qs   cross apply sys.dm_exec_sql_text(qs.sql_handle) st   cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp --order executioncount desc --order totalrw desc order totaldurationms desc --order avgdurationms desc ;