SQL Server - 参数嗅探 [英] SQL Server - parameter sniffing

问题描述

我读过很多关于参数嗅探的文章，但不清楚这是好是坏.谁能用一个简单的例子解释一下.

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?

提前致谢.

推荐答案

这很好，但有时也很糟糕.

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.

示例数据:

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 是一个有几千行的表，在 Value 上有一个非聚集索引.有一行的值为 1，其余的值为 2.

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.

示例查询:

select *
from T 
where Value = @Value;

查询优化器在这里的选择是执行聚集索引扫描并针对每一行检查 where 子句，或者使用索引查找查找匹配的行，然后执行键查找以从列中获取值在列列表中要求.

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.

当嗅探值为 1 时，查询计划将如下所示:

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

当嗅探到的值为 2 时，它看起来像这样:

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

在这种情况下，参数嗅探的坏部分发生在查询计划是通过嗅探 1 构建的，但稍后使用 2 的值执行时.

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.

您可以看到 Key Lookup 执行了 2352 次.扫描显然是更好的选择.

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.

更新:

这是针对几个 dmv 的查询，您可以使用它们来查找系统上哪些查询开销最大.更改为 order by 子句以对您要查找的内容使用不同的标准.我认为 TotalDuration 是一个很好的起点.

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
;

这篇关于SQL Server - 参数嗅探的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！