Кэш планов и параметризация запросов в MSSQL SERVER

Административные скрипты MS SQL > Кэш планов и параметризация запросов в MSSQL SERVER
21.05.2018 14:24:25



Статья:

Процессор запросов (query processor), который и занимается выполнением SQL-запросов, поступивших на SQL-сервер, и выдачей их результатов клиенту, состоит из двух основных компонентов:

  1. Оптимизатор запросов (Query Optimizer).
  2. Исполнитель запросов (Relational Engine).

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

  1. Синтаксический анализатор (Parser) просматривает инструкцию SELECT и разбивает ее на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы, а также производит нормализацию запроса.
  2. Из синтаксического анализатора данные попадают на вход компонента Algebrizer, который выполняет семантический анализ текста. Algebrizer проверяет существование указанных в запросе объектов базы данных и их полей, корректность использования операторов и выражений запроса, и извлекает из кода запроса литералы, для обеспечения возможности использования автоматической параметризации.
    Например, именно поэтому запрос, имеющий в секции SELECT поля, не содержащиеся ни в агрегатных функциях, ни в секции GROUP BY, пройдёт в SQL Server Management Studio (SSMS) проверку по Ctrl+F5 (синтаксический анализ), но свалится с ошибкой при попытке запуска по F5 (не пройдёт семантический анализ).
  3. Далее Algebrizer строит дерево разбора запроса с описанием логических шагов, необходимых для преобразования исходных данных к желаемому результату. Для дерева запроса извлекаются метаданные объектов запроса (типы данных, статистика индексов и т.д.), производятся неявные преобразования типов (при необходимости), удаляются избыточные операции (например, ненужные или избыточные соединения таблиц).
  4. Затем оптимизатор запросов анализирует различные способы, с помощью которых можно обратиться к исходным таблицам. И выбирает ряд шагов, которые, по мнению оптимизатора, возвращают результаты быстрее всего и используют меньше ресурсов. В дерево запроса записывается последовательность этих полученных шагов и из конечной, оптимизированной версии дерева генерируется план выполнения запроса.

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

Посмотреть кэш планов можно:

select  * from sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) -- покажет sql запрос
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) -- покажет закэшируемый план
Посмотреть текущий размер всего кэша планов и кэша планов запросов можно с помощью следующего запроса:
declare
@TotalCacheSize int,
@QueriesCacheSize int
 
select
  @TotalCacheSize = SUM(CAST(size_in_bytes as bigint)) / 1048576,
  @QueriesCacheSize = SUM(CAST((case 
                                  when objtype in (''Adhoc'', ''Prepared'') 
                                  then size_in_bytes else 0 
                                end) as bigint)) / 1048576
from sys.dm_exec_cached_plans
 
select 
  ''Queries'' as ''Cache'', 
  @QueriesCacheSize as ''Cache Size (MB)'', 
  CAST(@QueriesCacheSize * 100 / @TotalCacheSize as int) as ''Percent of Total''
union all
select 
  ''Total'' as ''Cache'', 
  @TotalCacheSize as ''Cache Size (MB)'', 
  100 as ''Percent of Total''
option(recompile)

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

Для анализа кэша планов запросов мы будем использовать следующий запрос(временную таблицу #plans_summary  мы  будем использовать дальше ниже запросами для выборки интересующих нас данных в нужных разрезах.)   :

USE master
 
declare
@QueryLike varchar(20) = '''' --Like-фильтр для поиска отдельных запросов
 
if OBJECT_ID(''tempdb..#plans_summary'') is not null drop table #plans_summary; 
 
with  
  /*Статистика времени выполнения */
  execution_stats(plan_handle, subqueries, creation_time, last_execution_time, total_worker_time, average_worker_time, total_elapsed_time, average_elapsed_time, query_plan_hash, query_hash) as
  (
  select 
    qs.plan_handle, 
    COUNT(*) as subqueries,
    MIN(qs.creation_time) as creation_time,
    MAX(qs.last_execution_time) as last_execution_time,
    CAST(SUM(qs.total_worker_time)/1000.0 as decimal(10,2)) as total_worker_time, --процессорное время
    CAST(SUM(qs.total_worker_time / qs.execution_count)/1000.0 as decimal(10,2)) as average_worker_time, 
    CAST(SUM(qs.total_elapsed_time)/1000.0 as decimal(10,2)) as total_elapsed_time, --фактическое время
    CAST(SUM(qs.total_elapsed_time / qs.execution_count)/1000.0 as decimal(10,2)) as average_elapsed_time,
    case when COUNT(*) = 1 then MIN(qs.query_plan_hash) else null end as query_plan_hash,
    case when COUNT(*) = 1 then MIN(qs.query_hash) else null end as query_hash
  from 
    sys.dm_exec_query_stats qs
  group by qs.plan_handle
  ),
  plans_summary([db_name], objtype, usecounts, compile_time, compile_cpu, total_worker_time, average_worker_time, total_elapsed_time, average_elapsed_time, query_text, size_in_kb, plan_subqueries, stats_subqueries, creation_time, last_execution_time, plan_handle, parameterized_plan_handle, query_plan_hash, query_hash, query_plan) as
  (
  select
    DB_NAME(CONVERT(int, epa.value)) as [db_name],
    cp.objtype,
    cp.usecounts,
    CAST(0.00 as numeric(15,2))as compile_time,
    CAST(0.00 as numeric(15,2)) as compile_cpu,
    es.total_worker_time, 
    es.average_worker_time, 
    es.total_elapsed_time, 
    es.average_elapsed_time, 
    st.text as query_text,
    cp.size_in_bytes / 1024 as size_in_kb,
    CAST(0 as int) as plan_subqueries,
    es.subqueries as stats_subqueries,
    es.creation_time, 
    es.last_execution_time, 
    cp.plan_handle,
    case 
      when es.subqueries is null
      then qp.query_plan.value(''declare namespace D="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//D:StmtSimple)[1]/@ParameterizedPlanHandle'', ''NVARCHAR(128)'')
      else null
    end,
    es.query_plan_hash, 
    es.query_hash,
    qp.query_plan
  from
    sys.dm_exec_cached_plans cp
    left join execution_stats es on (cp.plan_handle = es.plan_handle)
    cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
    cross apply sys.dm_exec_plan_attributes(cp.plan_handle) epa
    cross apply sys.dm_exec_sql_text(cp.plan_handle) st
  where 
    (cp.objtype in (''Adhoc'', ''Prepared'')) and -- Отфильтровать кэш по планам запросов
    (epa.attribute = ''dbid'') and 
    (DB_NAME(CONVERT(int, epa.value)) not in (''msdb'', ''master'', ''model'')) and -- Исключить из статистики запросы из системных БД
    ((@QueryLike = '''') or (st.text like @QueryLike)) -- Применить like-фильтр, если он задан
  )
 
  select * into #plans_summary from plans_summary
 
  /*Добавить статистику по компиляции планов*/
  update #plans_summary set
    plan_subqueries = t.subqueries,
    compile_cpu = t.compile_cpu,
    compile_time = t.compile_time
  from 
    #plans_summary ps
    join
    (
    select 
      SUM(query_plan_nodes.query_plan_node.value(''(@CompileTime)[1]'', ''int'')) as compile_time,
      SUM(query_plan_nodes.query_plan_node.value(''(@CompileCPU)[1]'', ''int'')) as compile_cpu, 
      ps.plan_handle,
      COUNT(*) as subqueries
    from 
      #plans_summary ps
      cross apply ps.query_plan.nodes(''declare namespace D="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //D:QueryPlan'') as query_plan_nodes(query_plan_node)
    group by ps.plan_handle
    ) t on (ps.plan_handle = t.plan_handle)
  option (recompile)
  select * from #plans_summary 
   option (recompile)
Примечание: Для работы запроса требуется уровень совместимости БД, в контексте которой будет выполняться запрос, не меньше 100 и 
SQL-сервер версии не ниже SQL Server 2008. Если ваша БД меньшего уровня совместимости, но имеющийся SQL-сервер - SQL Server 2008 или выше, 
то запрос будет работать, поскольку в начале запроса имеется инструкция, задающая контекст базы master для выполнения запроса.

Параметр всего один – это переменная @QueryLike varchar(20).  Она служит для поиска в кэше планов статистики для конкретного запроса, по указанному набору символов текста запроса. Если переменная не задана, то выводится статистика по всем планам запросов. Чуть ниже мы рассмотрим пример использования этого параметра.

Результатом выполнения запроса является заполненная временная таблица #plans_summary, содержащая следующие данные (столбцы) по статистике кэша планов:

  • db_name: Наименование базы данных, в контексте которой выполнялся запрос, для которого сгенерирован план;
  • objtype: Тип объекта, для которого сгенерирован план. Хотя в кэше планов могут храниться планы для 11 типов объектов, нам интересны всего два:
    • Adhoс – это значение говорит о том, что план был сгенерирован для нерегламентированного (произвольного, не универсального) запроса. Как правило планы именно таких запросов и являются малополезными пожирателями оперативной памяти на SQL-сервере. В реальной рабочей среде далеко не для каждого Adhoc-запроса план может быть использован повторно – для этого запрос должен быть или очень простым, чтобы удовлетворять условиям простой автоматической параметризации (об этом в следующей статье) или должен в точности совпадать с ранее использованным запросом;
    • Prepared – это значение говорит о том, что данный план был сгенерирован для заранее подготовленного (параметризованного) запроса. В большинстве случае такие планы могут быть использованы повторно, но бывают и исключения. В конце статьи описаны причины, приводящие к созданию дубликатов планов выполнения для параметризованных запросов;
  • usecounts: Счётчик количества выполнений данного плана. Для единожды исполненного запроса равен единице. Для каждого случая исполнения запроса, для которого в кэше был найден подходящий план, счётчик для найденного плана увеличивается на единицу;
  • compile_time: Общее время, в миллисекундах, затраченное на компиляцию плана выполнения;
  • compile_cpu: Процессорное время, в миллисекундах, затраченное на компиляцию плана выполнения;
  • total_worker_time: Процессорное время, в миллисекундах, затраченное на выполнение плана с момента компиляции, с учетом количества повторных использований плана (сумма времени каждого выполнения плана);
  • average_worker_time: Среднее процессорное время, в миллисекундах, затраченное на каждое выполнение плана;
  • total_elapsed_time: Общее время, в миллисекундах, затраченное на выполнение плана с момента компиляции, с учетом количества повторных использований плана. Учитывает время, потраченное всеми ресурсами SQL-сервера, необходимыми для выполнения запроса – процессор, память, устройства ввода/вывода;
  • average_elapsed_time: Среднее общее время, в миллисекундах, затраченное на каждое выполнение плана;
  • query_text: Текст запроса, для которого был сгенерирован план;
  • size_in_kb: Количество памяти, в килобайтах, занимаемое планом в кэше планов (в оперативной памяти);
  • plan_subqueries: Количество скомпилированных планов, содержащихся в общем плане запроса. Равно единице для простых запросов и больше единицы – для пакетных запросов;
  • stat_subqueries: Количество инструкций запроса, которые были выполнены при обращениях к плану. Равно единице для простых запросов и может быть больше единицы для пакетных запросов. Отличие от plan_subqueriesв том, что в stat_subqueries учитываются только выполненные инструкции запроса. Например, если в запросе есть операторы ветвления, содержащие разные подзапросы, и какие-то ветви запроса ни разу не выполнялись, то невыполнявшиеся инструкции не учитываются. Каждая инструкция учитывается один раз, повторное выполнение инструкций при повторном выполнении всего запроса, не увеличивает счетчик;
  • creation_time: Дата и время создания плана выполнения запроса. Это поле можно использовать для оценки текучки планов запросов в кэше;
  • last_execution_time: Дата и время последнего использования плана выполнения запроса;
  • plan_handle: Токен, ссылающийся на текущий скомпилированный план. Может быть использован для более детального анализа статистики плана;
  • parameterized_plan_handle: Токен, ссылающийся на план выполнения для автопараметризованного запроса. Заполняется только для автопараметризованных запросов;
  • query_hash: Хэш запроса, на основании которого скомпилирован план выполнения. Данный хэш создаётся из дерева разбора запроса, после прохождения запроса через процедуры синтаксического и семантического анализа, нормализации и упрощения (Parser и Algebrizer), что позволяет использовать этот хэш для поиска планов не только идентичных (без учёта литералов) запросов, но и семантически схожих. Это поле мы и будем использовать для поиска планов похожих запросов.
    • query_plan_hash: Хэш плана запроса. Данный хэш создаётся из скомпилированного плана выполнения запроса, что позволяет использовать этот хэш для поиска схожих по алгоритму выполнения планов запросов. Когда запросы с одинаковыми значениями query_hash выполняются на различных данных, оптимизатор запросов может выбрать для этих запросов разные планы выполнения из-за разницы в количестве элементов в результатах запроса. К примеру, для двух одинаковых запросов с разными значениями в условиях выборки (секция where) оптимизатор может использовать разные способы поиска по индексу, что даст разные значения query_plan_hash при одинаковых значениях query_hash;
    • query_plan: Содержит представление скомпилированного плана запроса в формате xml. Предназначается для более детального изучения плана и соответствующего ему запроса.

Примечания:

  1. В колонках total_worker_time, average_worker_time, total_elapsed_time, average_elapsed_time, stats_subqueries, creation_time, last_execution_time, query_hash, query_plan_hash могут содержаться значения NULL, в тех случаях, когда:
    • Adhoc-запрос прошел процедуру автопараметризации;
    • В запросе содержится только вызов процедуры;
    • Запрос не содержит инструкций SELECT, INSERT, UPDATE, DELETE;
  2. Также в колонках query_hash и query_plan_hash будут значения NULL для пакетных запросов, т.к. в случае пакетных запросов хэш запроса и хэш плана запроса высчитываются для каждой инструкции пакета;
  3. Обычно общее время выполнения плана (total_elapsed_time) и процессорное время выполнения плана (total_worker_time) примерно равны друг другу. Но есть и обратные ситуации:
    • Общее время больше процессорного времени. Общее время выполнения будет превышать процессорное в тех случаях, когда для выполнения запроса потребовалось ожидание данных от дисковой подсистемы или когда запрос ждал окончания какой-нибудь блокировки;
    • Процессорное время больше общего времени. Как бы странно это ни звучало, но бывают планы запросов, для которых процессорное время выполнения запроса превышает общее время. И это, на самом деле, хорошие планы выполнения запросов (при условии, что общее время выполнения не превышает разумных величин). Такие ситуации возникают в тех случаях, когда при выполнении запроса процессору запросов не пришлось ждать дисковую подсистему или окончания блокировок, и выполнение запроса, при этом, было распараллелено на два или более процессорных ядра.
      Пример: Некий запрос выполнялся на двух ядрах в течение пяти миллисекунд. Общее время выполнения запроса, в этом случае, составит пять миллисекунд. А процессорное время - десять миллисекунд (по пять на ядро).

Статистика по ресурсам, потребляемым Adhoc-запросами

Одноразовые планы выполнения

select 
  SUM(case when parameterized_plan_handle is null then 1 end) as ''Count Normal'',
  SUM(case when parameterized_plan_handle is not null then 1 end) as ''Count Shell'',
  CAST(SUM(compile_time) / 1000 as numeric(15,2)) as ''Compile Time (sec)'',
  CAST(SUM(compile_cpu) / 1000 as numeric(15,2)) as ''Compile CPU Time (sec)'',
  CAST(SUM(total_elapsed_time) / 1000 as numeric(15,2)) as ''Elapsed Time (sec)'',
  CAST(SUM(total_worker_time) / 1000 as numeric(15,2)) as ''Worker Time (sec)'',
  CAST(SUM(case when parameterized_plan_handle is null then size_in_kb else 0 end) /1024 as numeric(15,2)) as ''Size Normal (MB)'',
  CAST(SUM(case when parameterized_plan_handle is not null then size_in_kb else 0 end) /1024 as numeric(15,2)) as ''Size Shell (MB)'',
  CAST(DATEDIFF(MI, MIN(creation_time), GETDATE()) / 60.0 as numeric(15,2)) as ''TTL (hours)''
from #plans_summary
where (objtype = ''Adhoc'') and (usecounts = 1)
option (recompile)

Список полей:

  • Count Normal: Количество обычных планов Adhoc-запросов в кэше;
  • Count Shell: Количество планов-заглушек в кэше;
  • Compile Time (sec): Общее время, затраченное на компиляцию планов (у планов-заглушек равно нулю);
  • Compile CPU (sec): Процессорное время, затраченное на компиляцию планов (у планов-заглушек равно нулю);
  • Elapsed Time (sec): Общее время, затраченное на выполнение планов (у планов-заглушек равно нулю);
  • Worker Time (sec): Процессорное время, затраченное на выполнение планов (у планов-заглушек равно нулю);
  • Size Normal (MB): Суммарный размер обычных планов в кэше;
  • Size Normal (MB): Суммарный размер планов-заглушек в кэше;
  • TTL (hours): Время жизни самого старого плана в текущей выборке. Высчитывается из даты создания и относится только к обычным планам.

Что интересного можно увидеть в результатах этого запроса?

Время компиляции планов одноразовых запросов в несколько раз больше времени их выполнения - это не очень хорошо, но в данном случае не страшно, т.к. цифры всё равно небольшие, в пределах нескольких минут.  Самых интересных показателей два - это Size Normal (MB) и TTL (hours). Одноразовые планы занимают больше половины всего кэша планов (размер кэша на момент выполнения запроса - 3,2Гб, что составляет 10% от объёма оперативной памяти сервера), а невысокое время жизни одноразовых запросов (меньше двух часов) говорит о том, что кэш забит под завязку и в нём идёт постоянная ротация планов - вытеснение старых планов и компиляция новых.

Несмотря на то, что планов-заглушек почти в 7,5 раз меньше, чем обычных планов, места в кэше планы-заглушки занимают примерно в пятьдесят раз меньше. Это объясняется тем, что планы-заглушки - это не полноценные планы, а по сути ссылки на автопараметризованные планы и поэтому они занимают заметно меньший объём.

Повторно используемые планы выполнения

select 
  SUM(case when parameterized_plan_handle is null then 1 end) as ''Count Normal'',
  SUM(case when parameterized_plan_handle is not null then 1 end) as ''Count Shell'',
  CAST(SUM(compile_time) / 1000 as numeric(15,2)) as ''Compile Time (sec)'',
  CAST(SUM(compile_cpu) / 1000 as numeric(15,2)) as ''Compile CPU Time (sec)'',
  CAST(SUM(total_elapsed_time) / 1000 as numeric(15,2)) as ''Elapsed Time (sec)'',
  CAST(SUM(total_worker_time) / 1000 as numeric(15,2)) as ''Worker Time (sec)'',
  CAST(SUM(case when parameterized_plan_handle is null then size_in_kb else 0 end) /1024 as numeric(15,2)) as ''Size Normal (MB)'',
  CAST(SUM(case when parameterized_plan_handle is not null then size_in_kb else 0 end) /1024 as numeric(15,2)) as ''Size Shell (MB)''
from #plans_summary
where (objtype = ''Adhoc'') and (usecounts > 1)
option (recompile)
В результатах данного запроса наблюдается обратная от одноразовых планов ситуация - общее время выполнения запросов значительно превышает время компиляции планов, что объясняется тем, что планы выполнения запросов в данном случае был скомпилированы один раз, а выполнены несколько раз. Объём повторно используемых планов для Adhoc-запросов занимает четверть всего кэша. И, что довольно интересно, процессорное время выполнения запросов почти в три раза превышает фактическое время, т.е. какая-то часть запросов, составляющих эту статистику, выполняется с распараллеливанием. При более длительной работе SQL-сервера статистика по объёму занимаемого места в кэше значительно смещается от одноразовых планов к повторно используемым, а общее время выполнения начинает заметно превышать процессорное (сказывается тот факт, что далеко не все запросы распараллеливаются, да и не все распараллеливаемые запросы выполняются с использованием параллелизма. )

Поиск планов идентичных и схожих запросов

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

Планы схожих Adhoc-запросов

select
  query_hash as ''Query Hash'',
  COUNT(*) as ''Count'',
  CAST(SUM(compile_time) / 1000 as numeric(15,2)) as ''Compile Time (sec)'',
  CAST(SUM(compile_cpu) / 1000 as numeric(15,2)) as ''Compile CPU Time (sec)'',
  CAST(SUM(total_elapsed_time) / 1000 as numeric(15,2)) as ''Elapsed Time (sec)'',
  CAST(SUM(total_worker_time) / 1000 as numeric(15,2)) as ''Worker Time (sec)'',    
  CAST(SUM(size_in_kb) /1024 as numeric(15,2)) as ''Size (MB)''
from #plans_summary
where (query_hash is not null) and (objtype = ''Adhoc'') and (parameterized_plan_handle is null)
group by GROUPING sets (query_hash, ())
having (COUNT(*) > 1)
order by 2 desc
option(recompile)

Первая строка - это суммарный итог по всей выборке, по этой же причине поле query_hash для первой строки равно NULL.

count - Для этих запросов было скомпилировано столько-то планов выполнения.

Посмотреть более подробную статистику по конкретному запросу:

select 
  usecounts,
  compile_time,
  total_worker_time,
  total_elapsed_time, 
  size_in_kb,
  query_plan
from #plans_summary 
where 
  (query_hash = 0xB744007B8668CD58) and 
  (total_worker_time > total_elapsed_time)
option(recompile)

Если щёлкнуть по ссылке, указанной в поле query_plan, то можно посмотреть графическое отображение плана запроса и сам запрос:

 

Вывести список query_hash для тех запросов, планы выполнения для которых не только занимают место в кэше, но и на компиляцию которых времени уходит больше, чем на выполнение.

Запрос (с сортировкой по разнице между временем компиляции и выполнения):

select
  query_hash as ''Query Hash'',
  COUNT(*) as ''Count'',
  SUM(compile_time) as ''Compile Time (msec)'',
  SUM(total_elapsed_time) as ''Elapsed Time (msec)'',
  SUM(size_in_kb) as ''Size (KB)''
from #plans_summary
where (query_hash is not null) and (objtype = ''Adhoc'') and (parameterized_plan_handle is null)
group by query_hash
having (SUM(compile_time) - SUM(total_elapsed_time)) > 0
order by SUM(compile_time) - SUM(total_elapsed_time) desc
option(recompile)
Детально глянуть запрос можно
select 
  usecounts,
  compile_time,
  total_worker_time,
  total_elapsed_time, 
  size_in_kb,
  query_plan
from #plans_summary 
where 
  (query_hash = 0x79C8001CF7FA6669)
option(recompile)
 

Планы схожих параметризованных запросов

Дублирующиеся планы выполнения могут создаваться SQL-сервером не только для Adhoc, но и для параметризованных запросов.

Выполним следующий запрос:

select
  query_hash as ''Query Hash'',
  COUNT(*) as ''Count'',
  CAST(SUM(compile_time) / 1000 as numeric(15,2)) as ''Compile Time (sec)'',
  CAST(SUM(compile_cpu) / 1000 as numeric(15,2)) as ''Compile CPU Time (sec)'',
  CAST(SUM(total_elapsed_time) / 1000 as numeric(15,2)) as ''Elapsed Time (sec)'',
  CAST(SUM(total_worker_time) / 1000 as numeric(15,2)) as ''Worker Time (sec)'',   
  CAST(SUM(size_in_kb) /1024 as numeric(15,2)) as ''Size (MB)''
from #plans_summary
where (query_hash is not null) and (objtype = ''Prepared'')
group by GROUPING sets (query_hash, ())
having (COUNT(*) > 1)
order by 2 desc
option(recompile)

Причин создания дублирующихся планов выполнения для параметризованных запросов может быть несколько:

  1. Разные настройки сеанса, задаваемые инструкцией SET при выполнении одного и того же запроса. Например инструкции SET наверняка будут разными при выполнении запроса в Директуме и в SSMS. Но в нашем случае  эта причина роли не играет, т.к. абсолютное большинство планов генерируются из запросов, выполняемых в Директуме.
  2. Различный регистр букв и разное число пробелов в запросах. Например для запросов SELECT Field FROM SomeTable и select  field  from  sometable будут созданы два идентичных плана выполнения.
  3. Выполнение одного и того же запроса в контексте разных баз данных.
  4. Экономность автоматического режима параметризации. При выполнении автоматической параметризации SQL-сервер выбирает минимально необходимый тип данных для используемых в запросе литералов (за исключением строковых литералов, для них устанавливается тип данных varchar(8000)).
    Пример: Если в БД с принудительной автопараметризацией последовательно выполнить запросы select Field from SomeTable where SomeID = 1 и select Field from SomeTable where SomeID = 10000, то  помимо планов-заглушек будут созданы два параметризованных плана выполнения. Один с типом данных smallint, для используемого в запросе литерала, и второй с типом данных tinyint. При выполнении запросов в обратном порядке всё равно будут созданы два параметризованных плана выполнения с такими же типами данных для литералов.
  5. Самая распространённая причина (в нашем случае) дублирования планов параметризованных запросов - обращение к объектам БД без указания используемой схемы. Если запрос ссылается на объект базы данных без указания схемы, то SQL-сервер первым делом ищет этот объект в схеме текущего пользователя, и только потом в схеме dbo. Если объект в схеме пользователя найден, то для запроса будет создан новый план выполнения, даже если идентичный план выполнения для идентичного запроса уже сгенерирован для другого пользователя или для схемы dbo.