SQL Server - сниффинг параметров

Я читал много статей о сниффинге параметров, но не ясно, хорошо это или плохо. Может кто-нибудь объяснить это на простом примере.

Есть ли способ автоматически обнаружить, что неправильный план был назначен определенному утверждению?

Заранее спасибо.

Источник

2 ответа

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

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

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

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

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,

Вы можете видеть, что Поиск ключей был выполнен 2352 раза. Сканирование, несомненно, будет лучшим выбором.

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

Обновить:

Вот запрос к паре 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
;
Источник

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

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

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

Источник
Другие вопросы по тегам