sql-server - SQL Server - 参数嗅探

Question

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

有没有办法自动检测到特定语句分配了错误的计划？

提前致谢。

Answer 1

这很好，但有时也可能很糟糕。

参数嗅探是指查询优化器使用提供的参数值来找出可能的最佳查询计划。许多选择之一，也是很容易理解的一个选择是，是否应该扫描整个表来获取值，或者使用索引查找是否会更快。如果参数中的值具有高度选择性，优化器可能会构建一个带有搜索的查询计划，如果不是，查询将扫描您的表。

然后，查询计划将被缓存并重新用于具有不同值的连续查询。参数嗅探的不好的部分是当缓存的计划不是这些值之一的最佳选择时。

示例数据:

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。

示例查询:

select *
from T 
where Value = @Value;

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

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

当嗅探到的值为2时，它将如下所示:

在这种情况下，当构建查询计划时嗅探 1 但稍后使用 2 的值执行时，参数嗅探的不良部分就会发生。

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

总而言之，我想说参数嗅探是一件好事，您应该通过在查询中使用参数来尽可能多地实现它。有时它可能会出错，在这种情况下，很可能是由于数据倾斜而扰乱了您的统计数据。

更新:

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

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
;