Заметки Дмитрия Пилюгина о Microsoft SQL Server 

Twitter RSS
Home SQL Server (все заметки) Медленно в приложении, быстро в SSMS (часть 2)
formats

Медленно в приложении, быстро в SSMS (часть 2)

Собираем информацию для решения проблем прослушивания параметров

Вы уже узнали, как может получиться так, что хранимая процедура, которая выполняется в приложении медленно, при таком же вызове из SQL Server Management Studio выполняется быстро: из-за разных настроек ARITHABORT вы получаете разные записи в кэше, а т.к. SQL Server использует прослушивание параметров, вы можете получать разные планы выполнения.

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

EXEC sp_recompile ваша_проблемная_процедура

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

Но если проблема продолжает периодически происходить — и, к сожалению, это наиболее вероятный случай — вам придется сделать более глубокий анализ, и в данной ситуации вы не должны использовать sp_recompile или перекомпилировать процедуру каким-либо другим способом. Вы должны сохранить медленный план, чтобы потом изучить его, и найти для каких значений параметров был построен плохой план. Это тема данной главы.

Перед тем как я продолжу, небольшое замечание: выше я рекомендовал вам изменить настройку ARITHABORT на OFF при подключении по умолчанию из SSMS, чтобы избежать сбивающих с толку ситуаций. Но на самом деле есть небольшой недостаток в том чтобы иметь те же настройки что и приложение: вы можете не понять, что эта проблема возникла из-за прослушивания параметров. Но если вы выработаете у себя привычку, при исследовании проблем производительности, запускать ваши процедуры с настройкой ARITHABORT как ON так и OFF, вы сможете с легкостью определять имеет ли прослушивание параметров отношение к проблеме.

Собираем необходимые сведения

Все проблемы производительности требуют сведений. Если у вас нет сведений, вы попадете в ситуацию, о которой поет Bryan Ferry в песне Sea Breezes из альбома Roxy Music:
We’ve been running round in our present state
Hoping help will come from above
But even angels there make the same mistakes

Если у вас нет фактов, даже ангелы не смогут вам помочь. Основные сведения, которые вам нужно выяснить относительно проблемы производительности вызванной прослушиванием параметров это:
1. Какая инструкция выполняется медленно?
2. Как отличаются планы выполнения?
3. Какие значения параметров прослушал SQL Server?
4. Каковы определения таблиц и индексов?
5. Как выглядит статистика распределения? Актуальна ли она?

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

Какая инструкция медленная?

Первым в списке является определить медленную инструкцию — в большинстве случаев, проблема кроется лишь в одной инструкции. Если процедура и так состоит из одной инструкции — то задача тривиальна. Иначе, вы можете использовать Profiler чтобы выяснить это; колонка Duration подскажет вам. Отследите вызов процедуры из приложения или, запустив ее из Management Studio (с настройкой ARITHABORT OFF!) и отфильтруйте по своему собственному spid.

Так же один из вариантов, использовать процедуру sqltrace, написанную Lee Tudor, которую я рад представить у себя на сайте. sqltrace принимает пакет SQL в качестве параметра, запускает трассировку на сервере, запускает пакет, останавливает трассировку и выводит общий результат. Так же у процедуры есть набор входных параметров, чтобы определять как сортировать вывод.

Получаем планы выполнения и параметры при помощи Management Studio

Во многих случаях вы можете получить план выполнения, просто выполнив процедуру в Management Studio, включив опцию Include Actual Execution Plan (вы можете найти ее в меню Query). Это хорошо работает до тех пор, пока процедура не начинает включать в себя множество запросов. В этом случае, вкладка Execution Plan становится слишком перегруженной, чтобы с ней работать. Мы посмотрим на альтернативные варианты в следующем разделе.

Обычно вам нужно запустить процедуру так:

SET ARITHABORT ON
go
EXEC that_very_sp 4711, 123, 1
go
SET ARITHABORT OFF
go
EXEC that_very_sp 4711, 123, 1

В данном случае предполагается, что приложение выполняется с настройками по умолчанию, в таком случае первый вызов даст хороший план — потому что план построится с учетом прослушанных тут же параметров — а второй вызов будет использовать плохой план, который уже есть в кэше. Чтобы определить используемые ключи кэша, вы можете запустить запрос из раздела «Разные планы для разных установок», чтобы посмотреть значения для плана(ов) из кэша. (Если вы уже запускали процедуру из Management Studio у вас может быть уже две записи. Колонка execution_count в sys.dm_exec_query_stats, может помочь вам отличать какая запись из кэша была использована; та что имеет меньшее значение, скорее всего, относится к запуску из SSMS).

Как только вы получили планы, вы можете с легкостью посмотреть для каких прослушанных значений параметров они были построены. Кликните правой кнопкой на самый левый оператор в плане — тот что содержит SELECT, INSERT и т.д. — выберите Properties. Откроется окно со свойствами. Вот пример как оно может выглядеть:


Первое свойство Parameter Compiled Value — это прослушанное значение, которое, так или иначе, вызывает вашу проблему. Если вы знаете свое приложение и типичный сценарий, который оно использует, вы можете уже немедленно понять, в чем дело, глядя на значение. Возможно, что нет, но вы, по крайней мере, теперь знаете, что есть ситуация, когда приложение вызывает процедуру со странным нетипичным значением.

Обратите внимание, что вы так же можете видеть некоторые опции, которые являются ключами кэша. Однако, будьте осторожны, т.к. в SQL 2005 есть ошибка, из-за которой опции SET всегда показываются как отключенные. Это ошибка движка и поэтому версия SSMS не имеет значения.

Хотя Management Studio предоставляет очень хороший интерфейс для изучения планов выполнения, я, тем не менее, хочу обратить внимание на более продвинутую альтернативу SQL Sentry Plan Explorer, бесплатный инструмент от SQL Sentry, поставщика инструментов для SQL Server. Их Plan Explorer позволяет вам просматривать план различными способами, а так же проще переключаться между запросами, если их много.

Когда вы смотрите на план выполнения, обычно далеко не очевидно, какая его часть является действительно затратной. Но хороший указатель это толщина стрелочек. Чем толще стрелка, тем больше строк передается следующему оператору. И если вы смотрите на действительный план выполнения, то толщина стрелок основана на показателе actual number of rows. Так же, я обычно не обращаю внимания на процентную стоимость. Это только оценки, и они могут завести не туда, особенно если где-то в плане есть большая ошибка в оценке.

Получение плана запроса и параметров непосредственно из кэша планов

К сожалению, использовать SSMS для получения плана запроса и параметров не всегда возможно. Плохой запрос может выполняться дольше чем у вас хватит на то терпения, или процедура включает в себя слишком много инструкций, которые выглядят как беспорядочная куча в SSMS. Не в самую последнюю очередь это так же может быть и процедура содержащая цикл, выполняющийся много раз, в таком случае даже Sentry Plan Explorer может стать бесполезным.

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

Запрос

Этот запрос вернет инструкции, значения прослушанных параметров и планы выполнения для хранимой процедуры:

DECLARE @dbname    nvarchar(256),
        @procname  nvarchar(256)
SELECT @dbname = 'Northwind',
       @procname = 'dbo.List_orders_11'

; WITH basedata AS (
   SELECT qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          epa.value AS set_options, qp.query_plan,
          charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
             AS paramstart,
          charindex('</ParameterList>', qp.query_plan) AS paramend
   FROM   sys.dm_exec_query_stats qs
   CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
   CROSS  APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
                                            qs.statement_start_offset,
                                            qs.statement_end_offset) qp
   CROSS  APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
   WHERE  est.objectid  = object_id (@procname)
     AND  est.dbid      = db_id(@dbname)
     AND  epa.attribute = 'set_options'
), next_level AS (
   SELECT stmt_start, set_options, query_plan,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END)
          END AS Statement,
          CASE WHEN paramend > paramstart
               THEN CAST (substring(query_plan, paramstart,
                                   paramend - paramstart) AS xml)
          END AS params
   FROM   basedata
)
SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement,
       CR.c.value('@Column', 'nvarchar(128)') AS Parameter,
       CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],
       CAST (query_plan AS xml) AS [Query plan]
FROM   next_level n
CROSS  APPLY   n.params.nodes('ColumnReference') AS CR(c)
ORDER  BY n.set_options, n.stmt_start, Parameter

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

Вывод

Чтобы посмотреть, как работает запрос, вы можете использовать этот тестовый пакет (и да, примеры становятся все более и более изощренными по мере нашего продвижения):

CREATE PROCEDURE List_orders_11 @fromdate datetime,
                                @custid   nchar(5) AS
SELECT @fromdate = dateadd(YEAR, 2, @fromdate)
SELECT *
FROM   Orders
WHERE  OrderDate > @fromdate
  AND  CustomerID = @custid
IF @custid = 'ALFKI' CREATE INDEX test ON Orders(ShipVia)
SELECT *
FROM   Orders
WHERE  CustomerID = @custid
  AND  OrderDate > @fromdate
IF @custid = 'ALFKI' DROP INDEX test ON Orders
go
SET ARITHABORT ON
EXEC List_orders_11 '19980101', 'ALFKI'
go
SET ARITHABORT OFF
EXEC List_orders_11 '19970101', 'BERGS'

После выполнения этого пакета, вы можете запустить запрос приведенный выше. Когда я запустил, то увидел такой результат:

Колонки:

SET – атрибуты set_options для плана. Как я говорил ранее, это битовая маска. На этой картинке, вы видите два наиболее вероятных значения. 251 — это значения по-умолчанию, а 4347 — значения по-умолчанию + ARITHABORT ON. Если у вас другие значения, вы можете использовать функцию setoptions, чтобы перевести битовую маску.

Pos – позиция запроса в хранимой процедуре, количество символов посчитанное от начала инструкции создающей процедуру, включая любой комментарии предшествующие CREATE PROCEDURE. Не особенно полезна сама по себе, но служит для сортировки инструкций в том порядке, в котором они появляются в процедуре.

Statement – инструкция. Заметьте, что инструкции повторяются по одной для каждого параметра в запросе.

Parameter – Имя параметра. Показываются только параметры, присутствующие в данной инструкции. Как следствие, инструкции, которые не используют параметров — не выводятся вовсе.

Sniffed Value – значение параметра в момент компиляции, это то значение, которое прослушал оптимизатор, когда строил план. В отличии от вкладки Properties, вы не увидите здесь реального значения параметра. Как я уже упоминал ранее, прослушанное значение параметра может отличаться для разных инструкций в процедуре, и вы можете видеть этот пример на картинке.

Query Plan – план запроса. Если у вас SQL Server Management Studio 2008, вы можете кликнуть на XML документ, и увидеть графический план непосредственно. Если у вас SSMS 2005, вы увидите лишь XML документ. Вы можете сохранить его с расширением .sqlplan, и потом открыть заново, чтобы увидеть графическое представление. Как я уже отмечал выше, это только оценочный план выполнения. Вы не можете включить никаких текущих значений из кэша.

Объяснение запроса

Запрос использует несколько DMV, с которыми могут быть знакомы не все читатели. Он так же использует некоторую технику для работы с XQuery, с которой вы можете быть незнакомы. Это отвлечет вас от темы и понадобится слишком много времени, чтобы глубоко разобраться в этом запросе, поэтому я дам краткие пояснения. Если запрос и его объяснение не укладывается у вас в голове, не переживайте из-за этого. Так как если вы разобрались с его результатами, то он по-прежнему будет вам полезен.

Запрос использует два CTE (Common Table Expression — Обобщенное Табличное Выражение). Первое CTE, basedata, включает доступ к DMV. Мы уже видели все эти представления, кроме sys.dm_exec_text_query_plan. Так же мы достаем из sys.dm_exec_query_stats две дополнительные колонки statement_start_offset и statement_end_offset. Они выделяют инструкцию, и мы передаем их в sys.dm_exec_text_query_plan, чтобы получить план только для этой инструкции. (Вспомним, запись в кеше одна целиком на всю процедуру с одним plan_handle.) sys.dm_exec_text_query_plan возвращает колонку query_plan которая в противоположность тому что вы ожидали является nvarchar(MAX). Причина этого в том, что XML, для плана выполнения, может иметь такую глубокую вложенность, что не сможет быть представлен при помощи типа данных xml в SQL Server. CTE возвращает план таким же образом, но так же извлекает из документа позиции, в которых содержаться значения параметров.

В следующем CTE, next_level, я продолжаю использовать данные полученные из basedata. Выражение CASE извлекает инструкцию из текста, который вернул sys.dm_exec_sql_text. Честно говоря, способ, которым это сделано, является немного неуклюжим. И не в последнюю очередь из-за длинных названий колонок. И поскольку нет особых причин переделывать эту часть запроса, я умолкаю, и отправляю вас к Books Online. Или просто поверьте мне, что это работает =). Следующая колонка в CTE, params, производит извлечение реальных значений параметров из документа и преобразует его в тип xml.

В последнем SELECT, я разделил документ из params, таким образом, чтобы получить по одному ряду на каждый параметр. Конечно, это можно оспорить, и сказать что лучше иметь все параметры в одной единственной строке, потому что тогда каждая инструкция будет присутствовать всего один раз, вы можете это легко сделать, модифицировав последний SELECT. Так же в последнем SELECT, я конвертирую столбец плана запроса в тип XML, но как я заметил выше эта операция может дать сбой, из-за ограничений типа данных xml в sql sever. Если вы получите такую ошибку, просто закомментируйте этот столбец.

Кроме тех изменений, что я уже упомянул, вы можете сделать еще несколько, чтобы получить интересующую информацию. Например, вы можете включить дополнительные столбцы из sys.dm_exec_query_stats или больше атрибутов плана. Я указал включить только атрибут set_options, т.к. это ключ кэша, который наиболее вероятно отличается. Если вы хотите включить все инструкции в процедуре, включая те, что не относятся ни к каким входным параметрам, просто поменяйте CROSS APPLY на OUTER APPLY.

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

Еще одна альтернатива получить планы выполнения, это запустить трассировку вашего приложения или SSMS. Есть несколько событий Showplan events, которые вы можете туда включить. Наиболее многостороннее это Showplan XML Statistics Profile, которое предоставит вам такую же информацию, как если бы вы включили опцию Include Actual Execution Plan в SSMS.

Однако, по нескольким причинам, трассировка редко является хорошей альтернативой. Для начала, включение трассировки информации о планах выполнения приводит к довольно значительной нагрузке на сервере. И учтите, что это происходит даже если вы фильтруете события только для вашего spid. Механизм, по которому работает трассировка, таков, что все процесса все равно должны генерировать эти события. Я знаю. По ошибке, я однажды оставил трассировку с Showplan XML Statistics Profile, отфильтрованной только для моего spid запущенной на боевом сервере. Было очень не смешно.

Далее, если вы запускаете трассировку в Profiler, вы вероятнее всего столкнетесь с тем, что очень трудно установить хороший фильтр, который бы отлавливал все что нужно вам, отбрасывая постороннее. Одной из возможностей, является сохранение трассировки в таблицу на сервер после того как она была остановлена, что позволит вам найти интересующую информацию при помощи запросов. (Но не просите профайлер сохранять в таблицу данные в момент, когда трассировка запущена. Накладные расходы на это, просто ужасны. План содержится в столбце TextData. Преобразуйте его в xml и вы увидите его, как я описывал в предыдущем разделе.

В SQL 2008 вы так же можете использовать Extended Events, чтобы получить план выполнения, но скорее всего это будет не простая задача. Я не работал с Extended Events лично, так что не могу дать никаких примеров.

Получаем информацию о таблицах и индексах

Я предполагаю, что вы уже знакомы с тем как получить информацию о таблице, через sp_help или используя генерацию скриптов, так что я перейду непосредственно к индексам. Они так же могут быть получены через генерацию скриптов или используя sp_helpindex. Но скрипты слишком громоздкий способ, а sp_helpindex не поддерживает нововведения добавленные в SQL 2005 и выше. Этот запрос может быть полезен:

DECLARE @tbl nvarchar(265)
SELECT @tbl = 'Orders'

SELECT o.name, i.index_id, i.name, i.type_desc,
       substring(ikey.cols, 3, len(ikey.cols)) AS key_cols,
       substring(inc.cols, 3, len(inc.cols)) AS included_cols,
       stats_date(o.object_id, i.index_id) AS stats_date,
       i.filter_definition
FROM   sys.objects o
JOIN   sys.indexes i ON i.object_id = o.object_id
CROSS  APPLY (SELECT ', ' + c.name +
                     CASE ic.is_descending_key
                          WHEN 1 THEN ' DESC'
                          ELSE ''
                     END
              FROM   sys.index_columns ic
              JOIN   sys.columns c ON ic.object_id = c.object_id
                                  AND ic.column_id = c.column_id
              WHERE  ic.object_id = i.object_id
                AND  ic.index_id  = i.index_id
                AND  ic.is_included_column = 0
              ORDER  BY ic.key_ordinal
              FOR XML PATH('')) AS ikey(cols)
OUTER  APPLY (SELECT ', ' + c.name
              FROM   sys.index_columns ic
              JOIN   sys.columns c ON ic.object_id = c.object_id
                                  AND ic.column_id = c.column_id
              WHERE  ic.object_id = i.object_id
                AND  ic.index_id  = i.index_id
                AND  ic.is_included_column = 1
              ORDER  BY ic.index_column_id
              FOR XML PATH('')) AS inc(cols)
WHERE  o.name = @tbl
  AND  i.type IN (1, 2)
ORDER  BY o.name, i.index_id

В представленном виде запрос не запустится в SQL 2005, ля этого просто уберите последний столбец filter_definition из результата. Эта колонка относится к фильтрованным индексам, новшеству добавленному в SQL 2008. Что касается столбца stats_date, смотрите следующий раздел

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

Извлекаем информацию о статистике

Чтобы увидеть все статистики для таблицы, можете использовать этот запрос:

DECLARE @tbl nvarchar(265)
SELECT @tbl = 'Orders'

SELECT o.name, s.stats_id, s.name, s.auto_created, s.user_created,
       substring(scols.cols, 3, len(scols.cols)) AS stat_cols,
       stats_date(o.object_id, s.stats_id) AS stats_date,
       s.filter_definition
FROM   sys.objects o
JOIN   sys.stats s ON s.object_id = o.object_id
CROSS  APPLY (SELECT ', ' + c.name
              FROM   sys.stats_columns sc
              JOIN   sys.columns c ON sc.object_id = c.object_id
                                  AND sc.column_id = c.column_id
              WHERE  sc.object_id = s.object_id
                AND  sc.stats_id  = s.stats_id
              ORDER  BY sc.stats_column_id
              FOR XML PATH('')) AS scols(cols)
WHERE  o.name = @tbl
ORDER  BY o.name, s.stats_id

Так же как и запрос для индексов, этот запрос не запустится в таком виде в SQL 2005, для этого просто уберите filter_definition из результирующего набора. Столбец auto_created относится к статистике, которую SQL Server создает автоматически, когда это требуется, в то время как user_created относится к статистике созданной явно при помощи CREATE STATISTICS. Если оба столбца равны 0, то статистика присутствует потому, что есть индекс.

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

Если вы думаете что статистика устарела, вы можете запустить:

UPDATE STATISTICS tbl WITH FULLSCAN, INDEX

Эта инструкция обновляет всю статистику по индексам в таблице с полным сканированием. Опция FULLSCAN необязательная, но я слишком часто обжигался с неточной статистикой, которая может получаться при использовании частей данных для построения статистики (sampled statistics), и является настройкой по-умолчанию.

Вы так же можете обновить статистику по отдельному индексу. Правда, синтаксис для этого не такой как вы могли ожидать:

UPDATE STATISTICS tbl indexname WITH FULLSCAN
Примечание: заметьте, что нет никаких запятых между именем таблицы и индексом, просто пробел.

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

DBCC SHOW_STATISTICS (Orders, OrderDate)

Эта инструкция отображает три набора результатов. Я не буду их все здесь объяснять, но скажу только то, что последний набор является гистограммой статистики. Гистограмма отражает распределение данных в таблице, которое записал SQL Server. Вот как выглядят первые несколько строк:

Это говорит нам о том, что согласно статистике существует ровно одна строка со значением OrderDate = 1996-07-04. Затем есть одна строка в диапазоне от 1996-07-05 до 1996-07-07 и две строки с OrderDate = 1996-07-08 (потому что RANGE_ROWS равен 1 а EQ_ROWS равен 2). Далее гистограмма продолжается и всего для этой статистики в ней содержится 188 шагов. В гистограмме не бывает более чем 200 шагов. Для более подробного описания результатов, смотрите DBCC SHOW_STATISTICS в Books Online. Один из документов в разделе ссылки содержит более полезную информацию о статистике и гистограммах.

Примечание: В SQL 2005 есть ошибка, если есть шаг для значений NULL, то DBCC SHOW_STATISTICS не может отобразить эту стоку. Это ошибка отображения, само значение, тем не менее, присутствует в гистограмме. 

Вам не обязательно запускать DBCC SHOW_STATISTICS для всей статистики просто на всякий случай, это нужно, только если вы думаете, что это окажется вам полезным. Мы посмотрим на такой пример в следующей главе.

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

Важно понять, что само по себе прослушивание параметров, не является проблемой. Даже наоборот, это является фичей, потому что без этого SQL Server-у пришлось бы полагаться на слепые догадки, которые в большинстве своем привели бы к куда худшим планам. Но иногда прослушивание параметров работает против вас. Мы можем выделить три типичные ситуации:

1. Запрос используется таким образом, что прослушивание параметров неприемлемо совсем. В случае если план хорош только для конкретного сценария выполнения и не подходит для другого.
2. В приложении есть специфичный тип поведения при котором одна группа вызовов, очень сильно отличается от основной массы. Обычно, эти вызовы приложение делает при запуске или начале нового дня.
3. Структура индексов для одной или нескольких таблиц такова, что для запроса не существует идеального индекса, но есть более-менее подходящие, и какой индекс выберет оптимизатор предугадать нельзя.

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

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

«Не решение»

Перед тем как я перейду к реальным решениям, позвольте мне сначала отметить, что добавление в вашу процедуру SET ARITHABORT ON — не является решением. Когда вы так сделаете, может показаться, что это сработало. Но это только потому, что вы пересоздали процедуру, что вызвало ее перекомпиляцию, и при этом вызове были прослушаны текущие значения параметров. SET ARITHABORT ON — это просто плацебо, причем не самое хорошее. И проблема, скорее всего, проявится снова. Это даже не поможет избежать путаницы с разной производительностью в приложении и SSMS, потому что в целом запись в кэше все так же будет иметь в качестве атрибута плана ARITHABORT OFF.

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

Лучший индекс, зависит от входных данных

Взгляните на следующую процедуру:

CREATE PROCEDURE List_orders_12 @custid   nchar(5),
                                @fromdate datetime,
                                @todate   datetime AS
   SELECT *
   FROM   Orders
   WHERE  CustomerID = @custid
     AND  OrderDate BETWEEN @fromdate AND @todate

По столбцу CustomerID есть некластерный индекс, другой индекс по столбцу OrderDate. Предполагается, что активность заказов у клиентов может сильно отличаться. Большинство покупателей делают в год всего пару заказов. Но некоторые покупатели, более активны, и некоторые серьезные ребята делают несколько заказов в день.

В базе данных Northwind, самым активным клиентом является SAVEA с 31 заказом, в то время как у CENTC всего один заказ. Выполните приведенный ниже код:

EXEC List_orders_12 'SAVEA', '19970811', '19970811'
go
sp_recompile List_orders_12
go
EXEC List_orders_12 'CENTC', '19960101', '19961231'

То есть для SAVEA мы смотрим заказы только за один день, но для CENTC мы ищем заказы за целый год. Как вы заметили, эти два вызова лучше обслуживаются разными индексами. Вот план для первого вызова:

SQL Server здесь использует индекс по OrderDate, который более селективен.

 

В данном случае столбец CustomerID более селективен и SQL Server использует индекс по CustomerID.

Один из способов решения такой проблемы — это принудительная перекомпиляция при каждом выполнении при помощи хинта RECOMPILE:

CREATE PROCEDURE List_orders_12 @custid   nchar(5),
                                @fromdate datetime,
                                @todate   datetime AS
SELECT *
FROM   Orders
WHERE  CustomerID = @custid
  AND  OrderDate BETWEEN @fromdate AND @todate
OPTION (RECOMPILE)

При помощи этого хинта SQL Server, будет перекомпилировать запрос каждый раз. Помимо хинта в запросе, вы можете указать SQL Server перекомпилировать процедуру целиком, при каждом вызове.

CREATE PROCEDURE List_orders_12 @custid   nchar(5),
                                @fromdate datetime,
                                @todate   datetime WITH RECOMPILE AS

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

Во многих случаях, принудительная перекомпиляция каждый раз, вполне подходит, но есть несколько ситуаций, когда это не так:
1. Процедура запускается с очень высокой частотой, и частые перекомпиляции могут нанести урон системе.
2. Запрос является очень сложным, и время компиляции оказывает заметное негативное влияние на время отклика.

Что еще хотелось бы отметить то, что хотя перекомпиляция решение, которое возможно почти всегда, оно не всегда является лучшим решением. На самом деле, пример, который мы рассмотрели в этом разделе, возможно, является не очень типичным, для ситуации «быстро в SSMS, медленно в приложении». Потому что если у вас сильно различаются шаблоны использования, то вы заметите, что также сильно различается и производительность в самом приложении. Так что имеет смысл почитать дальше, чтобы увидеть, подходит ли ситуация с которой вы столкнулись, под те примеры, которые я представлю.

Динамические условия поиска

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

WHERE (CustomerID = @custid OR @custid IS NULL)
  AND (OrderDate = @orderdate OR @orderdate IS NULL)
  ...

Как вы понимаете, прослушивание параметров не очень выгодно для таких процедур. Я не буду отводить много места этой проблеме здесь по двум причинам: 1) Как я уже сказал, проблема с такими процедурами, проявляется в различной производительности и в самом приложении. 2) По этой теме у меня есть отдельная статья Dynamic Search Conditions in T-SQL.

Просматриваем индексы

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

SELECT DISTINCT c.*
FROM   Table_C c
JOIN   Table_B b ON c.Col1 = b.Col2
JOIN   Table_A a ON a.Col4 = b.Col1
WHERE  a.Col1 = @p1
  AND  a.Col2 = @p2
  AND  a.Col3 = @p3

Выполнение запроса из приложения заняло 10-15 минут. Когда они выполнили его в SSMS, они получили результат мгновенно. Поэтому они и позвонили мне.

Как только мне настроили доступ, я подключился для выяснения проблемы. Я обнаружил, что все три таблицы, были примерно одного размера, как минимум по миллиону записей в каждой. Я посмотрел на индексы в таблице Table_A, и обнаружил у нее 7-8 индексов. Интерес для этого запроса представляли следующие:
— один, некластерный, неуникальный индекс Combo_ix (Col1, Col2, Col5, Col4), возможно включающий еще какие-то колонки.
— один некластерный, неуникальный индекс Col2_ix(Col2)
— один некластерный, неуникальный индекс Col3_ix(Col3)

Таким образом, не было ни одного индекса, который бы полностью покрывал все условия поиска в предложении WHERE.

Когда я запустил процедуру в SSMS с настройками по-умолчанию, оптимизатор выбрал первый индекс для получения данных из таблицы Table_A. Когда я поменял настройку ARITHABORT на OFF, я увидел, что в плане используется индекс по Col3.

На этом этапе я запустил:

DBCC SHOW_STATISTICS (Table_A, Col3_ix)

Результат был таким:

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
APRICOT 0 17 0 1
BANANA 0 123462 0 1
BLUEBERRY 0 46 0 1
CHERRY 0 92541 0 1
FIG 0 1351 0 1
KIWI 0 421121 0 1
LEMON 0 6543 0 1
LIME 0 122131 0 1
MANGO 0 95824 0 1
ORANGE 0 10410 0 1
PEAR 0 46512 0 1
PINEAPPLE 0 21102 0 1
PLUM 0 13 0 1
RASPBERRY 0 95 0 1
RHUBARB 0 7416 0 1
STRAWBERRY 0 24611 0 1

Вот в чем дело, в этой колонке только 17 уникальных значений, и они имеют очень неровное распределение. Я подтвердил этот факт, запустив запрос:

SELECT Col3, COUNT(*) FROM Table_A GROUP BY Col3 ORDER BY Col3

Я перешел к просмотру неудачного плана выполнения, чтобы посмотреть значение параметра @p3 для которого был построен этот план. Выяснилось что это значение APPLE — значение, которое не представлено в таблице вообще! Вот по этому, при первом выполнении процедуры SQL Server оценил, что запрос венет одну единственную строку (вспомним о том, что сервер никогда не оценивает результат нулем), и индекс по Col3 был бы наиболее эффективным, для получения этой строки.

Теперь вы можете спросить себя, как так могло неудачно получиться, что процедура в первый раз выполнилась со значением APPLE? Просто не повезло? Т.к. я не знаю систему, я не могу это сказать наверняка, но очевидно, что это происходило уже не в первый раз. У меня сложилось впечатление, что процедура вызывалась много раз, как часть какой-то более сложной, большой операции. Скажем, эта операция всегда начинается со значения APPLE. Помните, что переиндексация таблиц, всегда вызывает рекомпиляцию, и очень распространено делать эти обслуживающие операции по ночам, чтобы держать в порядке ситуацию с фрагментацией. Вот почему, первый утренний вызов для процедуры, является очень важным для производительности. (Почему операция начиналась со значения, которого нет в базе данных? Кто знает, может быть APPLE это какое-то необычное условие, которое должно быть обработано раньше всех, если это значение присутствует. Или может быть это просто алфавитный порядок.)

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

1. OPTION (RECOMPILE) / WITH RECOMPILE.
2. Добавить «оптимальный» индекс по столбцам (Col1, Col2, Col3) INCLUDE (Col4).
3. Сделать индекс по Col3 фильтрованным, или удалить его вовсе.
4. Использовать хинт для явного указания другого индекса.
5. Использовать подсказку OPTIMIZE FOR
6. Скопировать @p3 в локальную переменную.
7. Изменить поведение приложения.

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

Добавление нового индекса

Моей первичной рекомендацией клиенту был пункт номер два: добавить индекс, который полностью подходит запросу. Это такой индекс, в котором ключами индекса являются все колонки, которые есть в предложении WHERE, а наименее селективная колонка Col3 является последней. На поверхности лежит, также и то, что нужно добавить Col4 как включенную колонку, так чтобы запрос мог быть выполнен только при помощи обращений к индексу, без обращения к страницам данных.

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

Изменить/удалить индекс по Col3

На сколько на самом деле полезен индекс по Col3? Так как я не знаю эту систему — сказать трудно. Но, в общем, индекс по колонке с малым числом отличающихся значений не очень селективен, и поэтому не очень полезен. Так что одним из вариантов будет удалить индекс по Col3 совсем и тем самым предотвратить оптимизатор от попадания в эту ловушку. Может быть индекс был добавлен какое-то время назад по ошибке, или был добавлен без учета того как база данных будет выглядеть через пару лет. (Поработав какое-то время с этим клиентом, я понял, что они добавляли индексы на все столбцы FK согласно заведенному порядку. Что может быть хорошей, а может быть не очень хорошей, идеей.)

Нельзя отрицать, что вы должны быть очень храбрым человеком, чтобы удалить индекс совсем. Вы можете посмотреть sys.dm_dbindex_usage_stats, чтобы проверить, как используется индекс. Только помните, что это представление очищается после перезагрузки сервера, или, когда база данных отключается.

Поскольку распределение данных в столбце Col3 такое неравномерное, нельзя исключить вероятность того, что есть запросы, которые ищут только редкие специфичные значения. Может быть значение FIG — означает «Новые необработанные строки», а значение RASPBERRY — ошибки. В таком случае, выгодно будет создать Col3_ix как фильтрованный индекс, новая функция добавленная в SQL 2008.
Например, такой индекс может быть создан так:

CREATE INDEX col3_ix ON Table_A(col3) WHERE col3 IN ('FIG', 'RASPBERRY', 'APPLE', 'APRICOT')

Это дает нам две выгоды:
1. Размер индекса уменьшается более чем на 99%
2. Этот индекс больше не подходит для проблемного запроса. Помните, что сервер, должен выбирать план, который подходит для всех входных значений, так что даже если прослушанное значение параметра будет APPLE, SQL Server не сможет использовать индекс, так как план выдаст некорректный результат для значения KIWI.

Явно указать другой индекс

Если вы точно знаете, что индекс по Col1, Col2 будет всегда наилучшим выбором, но вы не хотите добавлять или удалять какие-либо индексы, вы можете явно указать использование этого индекса:

SELECT c.*
FROM   Table_C c
JOIN   Table_B b ON c.Col1 = b.Ccol2
JOIN   Table_A a WITH (INDEX = Combo_ix) ON a.Col4 = b.Col1
WHERE  a.Col1 = @p1
  AND  a.Col2 = @p2
  AND  a.Col3 = @p3

Вы даже можете указать:

WITH (INDEX (combo_ix, col2_ix))

чтобы дать оптимизатору возможность выбора между двумя «хорошими» индексами.

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

OPTIMIZE FOR

OPTIMIZE FOR — это подсказка запроса, которая позволяет вам контролировать прослушивание параметров. Для запроса в примере, это может выглядеть так:

SELECT c.*
FROM   Table_C c
JOIN   Table_B b ON c.col1 = b.col2
JOIN   Table_A a ON a.col4 = b.col1
WHERE  a.col1 = @p1
  AND  a.col2 = @p2
  AND  a.col3 = @p3
<strong>OPTION (OPTIMIZE FOR (@p3 = 'KIWI'))</strong>

Эта подсказка, говорит SQL Server игнорировать входящие значения, и вместо этого компилировать запрос так, как если бы входное значение @p3 было KIWI, наиболее распространенное в Col3. Это несомненное разубедит SQL Server использовать индекс.

Второй вариант, доступен только в SQL 2008 и выше:

OPTION (OPTIMIZE FOR (@p3 UNKNOWN))

Вместо того чтобы жестко зашивать какое-либо значение, мы можем указать SQL Server-у делать слепые предположения, полностью исключив прослушивание параметров для @p3.

Стоит отметить, что вы так же можете использовать OPTIMIZE FOR и для локальных переменных, не только для параметров.

Скопировать @p3 в локальную переменную

Вместо того, чтобы использовать @p3 напрямую в запросе, вы можете копировать его значение в локальную переменную, и в запросе использовать ее. Это будет иметь такой же эффект, как и OPTIMIZE FOR UNKNOWN, но будет работать на любой версии SQL Server.

Поменять приложение

Также, одним из вариантов, может быть поменять поведение приложения, чтобы оно запускалось с одним из более типичных значений параметра. Это не то решение, которое я бы действительно посоветовал потому, что оно создает дополнительную зависимость между базой данных и приложением. Существует риск, что через пару лет, приложение будет переписано, чтобы удовлетворять новым требованием, таким, что строки PEACH должны быть обработаны вначале…

И тогда, этот изъян снова проявится. Запрашивает ли приложение по одному фрукту за раз, или должно получать значения для множества фруктов сразу? Я помог этому клиенту с другим запросом. У нас была сессия по настройке производительности запросов в переговорной, и мне никак не удавалось, получить действительно хорошую производительность в запросе, с которым мы работали. Но ближе к концу дня, ответственный за это разработчик сказал, что он знает, что делать дальше, и его решением было — целиком перепроектировать процесс, частью которого был проблемный запрос.

Подытожим

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

Случай с кэшем приложения

В системе, с которой я работаю большую часть своего времени, есть некоторое подобие кэша приложения, который хранит информацию в базе данных в оперативной памяти, назовем ее MemDb. Это используется для разных целей, но главная цель, предоставить кэш, из которого веб сервер потребителя, может получить информацию, вместо того чтобы делать запросы к базе данных. Обычно, по утрам, происходит полное обновление информации. Когда информация в таблице в базе данных обновляется, срабатывает некий сигнальный механизм, который заставляет MemDb запустить хранимую процедуру, чтобы получить разницу. Чтобы определить, что изменилось, MemDb пользуется столбцами timestamp (Столбец timestamp содержит 8 байтовое значение, которое уникально для всей базы данных и монотонно возрастает. Оно обновляется автоматически, когда строка добавляется или обновляется.) Обычная хранимая процедура, получающая изменения, выглядит так:

 CREATE PROCEDURE memdb_get_updated_customers @tstamp timestamp AS
   SELECT CustomerID, CustomerName, Address, ..., tstamp
   FROM   Customers
   WHERE  tstamp > @tstamp

Когда MemDb вызывает эти процедуры, она передает самое большое значение из столбца timestamp для запрашиваемой таблицы из предыдущего вызова. Когда процедура вызывается для обновления, MemDb передает 0x, в качестве параметра, для получения всех строк.

Примечание: Реальная схема более сложная, чем приведенная выше; Я упростил ее для того чтобы сфокусироваться на том что важно для этой статьи. Если вы подумываете о чем-то подобном, имейте в ввиду, что в том виде как он здесь показан, пример имеет много вопросов с последовательными обновлениями, особенно если вы используете какой-либо вид snapshot isolation. SQL 2008 предлагает Change Tracking, что является более надежным решением, специально сделанным для этой цели. Так же хочу заметить что MemDb — была не моя идея, и я не был вовлечен в ее проектирование.

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

Для того чтобы приведенный выше запрос работал быстро, по столбцу timestamp должен быть индекс, но будет ли он использоваться? Как я сказал выше, первым делом, утром, MemDb запустит:

EXEC memdb_get_updated_customers 0x

а уже позже, что-то вроде:

EXEC memdb_get_updated_customers 0x000000000003E806

Не редко происходит так, что план запроса за ночь вытесняется из кэша, из-за ночных задач, требующих много памяти. Или из-за обслуживающих джобов по перестроению индексов возникают перекомпиляции. Так что обычно, когда запускается утреннее обновление, план в кэше отсутствует, и прослушанное значение это 0x. Будет ли оптимизатор использовать индекс с этим значением? Да, если это кластерный индекс. Но так как столбец timestamp обновляется каждый раз, когда обновляется строка, это не очень хороший выбор в качестве кластерного индекса, и все наши индексы по timestamp являются некластерными. (А на часто обновляемых таблицах и некластерный индекс по столбцу timestamp является спорным вопросом). Таким образом, так как оптимизатор видит, что из таблицы будут получены все строки, он выбирает сканирование таблицы. Этот план помещается в кэш, и последующие вызовы тоже приводят к сканированию таблицы, даже если выбираются только самые последние строки. Это и была та самая плохая производительность, которую я увидел.

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

OPTION (RECOMPILE)

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

EXECUTE WITH RECOMPILE

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

EXECUTE memdb_get_updated_customers WITH RECOMPILE

Как я заметил ранее, вероятнее всего, что рано утром план в кэше отсутствует, так в чем же тогда смысл? Смысл в том, что когда вы используете WITH RECOMPILE с EXECUTE, план не помещается в кэш вовсе. По этому, обновление может пройти со сканированием, но план в кэше будет построен при первом получении разницы. (А если план для чтения разницы все еще в кэше, он так там и останется)

Для данной проблемы в нашем проекте, это было решение, на котором я настаивал. В качестве дополнительного преимущества для нас было то, что в MemDb нужно было изменить всего лишь одну строчку.

Однако с этим решением все же есть одна небольшая проблема. Обычно, когда вы вызываете хранимую процедуру с клиента, вы используете специальный тип команды, в котором только указываете название процедуры (например CommandType.StoredProcedure в ADO .NET.) Клиентский API затем делает вызов RPC (remote procedure call) в SQL Server, и не использует выражение с EXECUTE таким образом. Очень мало клиентских API предоставляют способ, чтобы указать WITH RECOMPILE при вызове через RPC. Решением этой проблемы может быть отправка команды EXECUTE целиком при помощи CommandType.Text или подобного, например:

cmd.CommandType = CommandType.Text;
cmd.Text = "EXECUTE memdb_get_updated_customers @tstamp WITH RECOMPILE";
Примечание: Вы должны передавать параметры через коллекцию параметров или через соответствующий механизм предоставляемый вашим API, не вставляйте значения параметров напрямую в строку команды EXEC, т.к. это может открыть возможность атак типа SQL injection.

Использование процедуры обертки

Если вы столкнулись с ситуацией, в которой использование EXECUTE WITH RECOMPILE — является наилучшим решением, но нет возможности менять клиент, вы можете использовать процедуру обертку. В нашем примере, исходная процедура может быть переименована в memdb_get_updated_customers_inner, а обертка выглядеть следующим образом:

CREATE PROCEDURE memdb_get_updated_customers @tstamp timestamp AS
   IF @tstamp = 0x
      EXECUTE memdb_get_updated_customers_inner @tstamp WITH RECOMPILE
   ELSE
      EXECUTE memdb_get_updated_customers_inner @tstamp

Во многих случаях, это может быть простым и понятным решением, особенно если у вас немного подобных процедур. (У нас их много.)

Разные ветки кода

Другой подход, использовать две разные ветки кода для этих двух случаев:

CREATE PROCEDURE memdb_get_updated_customers @tstamp timestamp AS
   IF @tstamp = 0x
   BEGIN
      SELECT CustomerID, CustomerName, Address, ..., tstamp
      FROM   Customers
   END
   ELSE
   BEGIN
      SELECT CustomerID, CustomerName, Address, ..., tstamp
      FROM   Customers WITH (INDEX = timestamp_ix)
      WHERE  tstamp > @tstamp
   END

Заметьте, что очень важно явно указать индекс в ветке ELSE, а иначе, эта ветка приведет к сканированию таблицы, если первый вызов процедуры сделан для @tstamp = 0x, из-за прослушивания параметров. Если ваша процедура более сложная, и включает в себя соединения с другими таблицами, то вероятнее всего такой подход не сработает, даже если вы явно укажете индекс. Оценки для соединений будут сильно неверными, и планы запросов будут оптимизированы для получения всей информации, а не только разницы.

Разные процедуры

В нашем случае, была одна особенно сложная процедура. Она получала проводки по счетам (это система для торговли на бирже). Обновление получало не все проводки в базе данных, а только за последние N дней, где N — параметр системы, прочитанный из базы данных. В базе данных N равнялось 20. Процедура читала данные не только из одной таблицы, но и из множества других. Вместо параметра timestamp, был параметр id. Это работает, так как проводки никогда не обновляются, так что MemDb должна просмотреть только более новые проводки.

Я выяснил, что в данном случае EXECUTE WITH RECOMPILE само по себе не спасет ситуацию потому, что в процедуре были и другие проблемы. Я пришел к выводу, что нет другого выхода, кроме как иметь две процедуры, одну для обновления, другую для получения разницы. Я заменил исходную процедуру следующей оберткой:

CREATE PROCEDURE memdb_get_transactions @transid int AS
   IF coalesce(@transid, 0) = 0
      EXECUTE memdb_get_transactions_refresh
   ELSE
   BEGIN
      DECLARE @maxtransid int
      SELECT @maxtransid = MAX(transid) FROM transactions
      EXECUTE memdb_get_transactions_delta @transid, @maxtransid
   END

Мне пришлось добавить @maxtransid в качестве параметра в процедуру для разницы, потому что с открытым условием WHERE transid > @transid, SQL Server имел склонность к ошибкам при оценке количества считываемых строк. Делая интервал закрытым, я избавился от этой проблемы, и передав @maxtransid в качестве параметра, SQL Server получил возможность прослушать это значение.

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

Примечание: Несколькими годами спустя, коллега переписал процедуру memdb_get_transactions_refresh, чтобы она тоже стала оберткой. Как я упомянул, она читает некоторые системные параметры, чтобы определить, какие проводки необходимо прочитать. Он обнаружил что, чтобы заставить обновление выполняться с приличной скоростью, нужно передавать значения этих системных параметров в качестве интервалов идентификаторов проводок, которые необходимо прочитать, внутренней процедуре, чтобы получить все преимущества от прослушивания параметров. ( Это было еще во времена SQL 2000. В SQL 2005 и позднее OPTION (RECOMPILE) вызвало бы такое же поведение).

Исправляем плохой sql код

Так же могут быть ситуации, в которых корень проблемы кроется просто в плохо написанном sql коде. Вот простой пример такого запроса:

SELECT ...
FROM   Orders
WHERE  (CustomerID = @custid OR @custid IS NULL)
  AND  (EmployeeID = @empid  OR @empid IS NULL)
  AND  convert(varchar, OrderDate, 101) = convert(varchar, @orderdate, 101)

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

Для этого запроса, индекс по OrderDate будет очевидным выбором, но при таком написании запроса, SQL Server не может осуществлять поиск по индексу, потому что OrderDate использовано в выражении. Это иногда называют случаем когда выражение не sargable, где SARG сокращение от Search Argument, то что иногда может использоваться в качестве предикатов поиска в запросе (seek predicate). (Лично я не считаю термин «sargable» очень уж полезным, но так как люди его периодически упоминают, я думаю, что можно его использовать как часть жаргона.)

Так как индекс по OrderDate был исключен таким написанием, оптимизатор может использовать любые другие индексы, в зависимости от значения первого параметра, показывая плохую производительность, на остальных типах поиска. В данном случае, лечение, переписать запрос, например так:

SELECT ...
FROM   Orders
WHERE  (CustomerID = @custid OR @custid IS NULL)
  AND  (EmployeeID = @empid  OR @empid IS NULL)
  AND  OrderDate >= @orderdate
  AND  OrderDate < dateadd(DAY, 1, @orderdate)

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

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

 

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Анти-спам: введите результат (цифрами) *