SQL Server - parameter sniffing
Asked Answered
C

2

13

I've read many articles about parameter sniffing, but it's not clear if this is good or bad. Can anyone explain this with a simple example.

Is there a way to automatically detect that wrong plan was assigned to a specific statement?

Thanks in advance.

Carswell answered 20/12, 2013 at 8:36 Comment(4)
Check this.Depilate
@Sergi: is this applying to ad hoc statements also?Carswell
when actual number of rows return for each parameter are almost same.not much variation,then one can afford to ignore it . Also i think declaring dummy variable are best . Am I wrong ?Hulton
I wrote an indepth post on this with examples here SQL Server Parameter Sniffing In DepthTedric
S
20

It is good but can be bad sometimes.

Parameter sniffing is about the query optimizer using the value of the provided parameter to figure out the best query plan possible. One of many choices and one that is pretty easy to understand is if the entire table should be scanned to get the values or if it will be faster using index seeks. If the value in your parameter is highly selective the optimizer will probably build a query plan with seeks and if it is not the query will do a scan of your table.

The query plan is then cached and reused for consecutive queries that have different values. The bad part of parameter sniffing is when the cached plan is not the best choice for one of those 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 into T(Value) values(1);

insert into T(Value)
select 2
from sys.all_objects;

T is a table with a couple of thousand rows with a non clustered index on Value. There is one row where value is 1 and the rest has the value 2.

Sample query:

select *
from T 
where Value = @Value;

The choices the query optimizer has here is either to do a Clustered Index Scan and check the where clause against every row or use an Index Seek to find to rows that match and then do a Key Lookup to get the values from the columns asked for in the column list.

When the sniffed value is 1 the query plan will look like this:

enter image description here

And when the sniffed value is 2 it will look like this:

enter image description here

The bad part of parameter sniffing in this case happens when the query plan is built sniffing a 1 but executed later on with the value of 2.

enter image description here

You can see that the Key Lookup was executed 2352 times. A scan would clearly be the better choice.

To summarize I would say that parameter sniffing is a good thing that you should try to make happen as much as possible by using parameters to your queries. Sometimes it can go wrong and in those cases it is most likely due to skewed data that is messing with your statistics.

Update:

Here is a query against a couple of dmv's that you can use to find what queries are most expensive on your system. Change to order by clause to use different criteria on what you are looking for. I think that TotalDuration is a good place to start.

set transaction isolation level read uncommitted;

select top(10)
  PlanCreated       = qs.creation_time,
  ObjectName        = object_name(st.objectid),
  QueryPlan         = cast(qp.query_plan as 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
from sys.dm_exec_query_stats as qs
  cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
  cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
--order by ExecutionCount desc
--order by TotalRW desc
order by TotalDurationMS desc
--order by AvgDurationMS desc
;
Sagittate answered 20/12, 2013 at 9:5 Comment(10)
Is there a way to automatically detect that wrong plan was assigned to a specific statement? Simple and concise, thank you!Carswell
@Carswell Yes, you can use the dmv sys.dm_exec_query_stats to have a look at what queries are having trouble in your system.Sagittate
how? should i look for statements that have the same query hash, but different query plan hash, with a lot of logical reads?Carswell
@Carswell added a query you can use to the answer.Sagittate
with this query, I have to take most expensive queries and analyse one by one if there is a wrong plan associated with that query, case when I should remove that plan from cache? If yes, how can I influence the chosen plan?Carswell
@user3104183: here it is how you could influence query plan choice: sqlhint.com/sqlserver/sqlserver/how-to/…Statocyst
@Carswell What actions you need to take with queries that is giving you issues must be decided on a case by case basis. There is no "catch all" answer there. The query I provided will of course not only capture parameter sniffing issues. It will capture the once that is most expensive to you. If you come to the conclusion that it is a sniffing problem there are different ways to go about fixing that.Sagittate
@MikaelEriksson, after that it applies the link provided by Sergi. Thank you so much for this interesting discussion and for your time!!!Carswell
@Carswell Have a look at RECOMPILE Hints and Execution Plan Caching and Why Parameter Sniffing Can Slow Down Queries (video)Sagittate
Adding OPTION (RECOMPILE) to the query would help in situations where you have widely varying cardinalitiesWernsman
S
0

Yes, sometime it is good or bad.

The Many time query optimizer chooses old query plan for execution because it stores this plan into the cache for frequently running queries. Now what happened when old query plan has table scan parameter which is require to change for index scanning after increasing records.

I found that in my situation query optimizer use old query plan instead of to create a new query plan.The query optimizer was using an old query plan from the query cache. I have created very interesting post on Parameter Sniffing. Please visit this url: http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/

Socialize answered 21/8, 2015 at 19:51 Comment(0)

© 2022 - 2025 — McMap. All rights reserved.