SQL Server - параметр sniffing

Ответ 1

Это хорошо, но иногда бывает плохо.

Параметр sniffing относится к оптимизатору запросов, используя значение предоставленного параметра для определения наилучшего возможного плана запроса. Один из многих вариантов и один, который довольно легко понять, это то, что вся таблица должна быть отсканирована, чтобы получить значения, или если она будет быстрее с использованием запросов индекса. Если значение в вашем параметре очень избирательно, оптимизатор, вероятно, построит план запроса с запросами, и если он не будет выполнять запрос, сканирование вашей таблицы.

План запроса затем кэшируется и повторно используется для последовательных запросов, имеющих разные значения. Плохая часть параметра sniffing - это когда кешированный план не лучший выбор для одного из этих значений.

Пример данных:

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 - таблица с несколькими тысячами строк с индексом без кластеризации по значению. Существует одна строка, где значение 1, а остальное значение имеет значение 2.

Пример запроса:

select *
from T 
where Value = @Value;

Выбор оптимизатора запросов может состоять в том, чтобы выполнить Clustered Index Scan и проверить предложение where для каждой строки или использовать индексный поиск, чтобы найти строки, которые соответствуют, а затем выполнить поиск ключа, чтобы получить значения из столбцов в списке столбцов.

Когда значение sniffed 1, план запроса будет выглядеть следующим образом:

И когда значение sniffed 2 будет выглядеть так:

Незначительная часть параметра sniffing в этом случае происходит, когда план запроса строится, обнюхивая 1, но позже исполняя его со значением 2.

Вы можете видеть, что Key Lookup выполнялся 2352 раза. Лучшим выбором будет сканирование.

Подводя итог, я бы сказал, что параметр sniffing - это хорошая вещь, которую вы должны попытаться сделать как можно больше, используя параметры для своих запросов. Иногда это может пойти не так, и в таких случаях это, скорее всего, связано с искаженными данными, которые возились с вашей статистикой.

Update:

Вот запрос против пары dmv, которую вы можете использовать, чтобы найти, какие запросы наиболее дороги в вашей системе. Измените в порядке заказа, чтобы использовать разные критерии для того, что вы ищете. Я думаю, что 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
;

Ответ 2

Да, иногда это хорошо или плохо.

Многопользовательский оптимизатор запросов выбирает старый план запроса для выполнения, потому что он хранит этот план в кеше для часто выполняемых запросов. Теперь, что произошло, когда в старом плане запроса был параметр сканирования таблицы, который требуется изменить для сканирования индекса после увеличения записей.

Я обнаружил, что в моей ситуации оптимизатор запросов использует старый план запросов вместо создания нового плана запросов. Оптимизатор запросов использовал старый запрос из кеша запросов. Я создал очень интересный пост по параметру Sniffing. Пожалуйста, посетите этот URL: http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/