PDF
Собираем информацию для решения проблем прослушивания параметров
Вы уже узнали, как может получиться так, что хранимая процедура, которая выполняется в приложении медленно, при таком же вызове из SQL Server Management Studio выполняется быстро: из-за разных настроек ARITHABORT вы получаете разные записи в кэше, а т.к. SQL Server использует прослушивание параметров, вы можете получать разные планы выполнения.
И хотя разгадка этой проблемы теперь получена, основная проблема еще остается: как подойти к проблеме производительности? Вы уже знаете из прочитанного, как быстро исправить ситуацию. Если вы раньше никогда не сталкивались с такой ситуацией, а дело срочное, то вы всегда можете сделать:
EXEC sp_recompile ваша_проблемная_процедура
Как мы видели, это удалит план процедуры из кэша, и при ее следующем вызове, будет построен новый план. Если проблема больше не проявится, можно считать дело закрытым.
Но если проблема продолжает периодически происходить — и, к сожалению, это наиболее вероятный случай — вам придется сделать более глубокий анализ, и в данной ситуации вы не должны использовать sp_recompile или перекомпилировать процедуру каким-либо другим способом. Вы должны сохранить медленный план, чтобы потом изучить его, и найти для каких значений параметров был построен плохой план. Это тема данной главы.
Примечание: Нет необходимости применять совет данный в параграфе выше, если у вас SQL Server 2016 или более поздней версии и вы включили Query Store для вашей БД. В таком случае вы можете найти всю информацию о планах запросов при помощи представлений Query Store, даже после того, как планы были удалены из кэша. В последней главе я приведу аналоги запросов представленных ниже для представлений Query Store.
Перед тем как я продолжу, небольшое замечание: выше я рекомендовал вам изменить настройку 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.
Так же один из вариантов, использовать процедуру sp_sqltrace, написанную Lee Tudor, которую я рад представить у себя на сайте. sp_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 не имеет значения. (Эта ошибка исправлена в SQL 2008 и позже.)
Хотя Management Studio предоставляет очень хороший интерфейс для изучения планов выполнения, я, тем не менее, хочу обратить внимание на более продвинутую альтернативу Plan Explorer, бесплатный инструмент от SentryOne, поставщика инструментов для SQL Server. Plan Explorer позволяет вам просматривать план различными способами, а так же проще переключаться между запросами, если их много.
Когда вы смотрите на план выполнения, обычно далеко не очевидно, какая его часть является действительно затратной. Но хороший указатель это толщина стрелочек. Чем толще стрелка, тем больше строк передается следующему оператору. И если вы смотрите на действительный план выполнения, то толщина стрелок основана на показателе actual number of rows. Так же, я обычно не обращаю внимания на процентную стоимость. Это только оценки, и они могут завести не туда, особенно если где-то в плане есть большая ошибка в оценке. Это частый случай, когда у вас есть проблема производительности связанная с прослушиванием параметров.
Получение плана запроса и параметров непосредственно из кэша планов
К сожалению, использовать SSMS для получения плана запроса и параметров не всегда возможно. Плохой запрос может выполняться дольше чем у вас хватит на то терпения, или процедура включает в себя так много инструкций, что выглядят как беспорядочная куча в SSMS. Не в самую последнюю очередь это так же может быть и процедура содержащая цикл, выполняющийся много раз, в таком случае даже 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 – план запроса. Вы можете кликнуть на XML документ, чтобы увидеть непосредственно графический план. (Это работает не всегда. Обычно это не работает если версия вашей SSMS меньше чем версия сервера. Эта функциональность также не работала в некоторых версиях SSMS 2008 R2 и не существовала вовсе в SSMS 2005.) Как я уже отмечал выше, это только оценочный план выполнения. Вы не можете включить никаких текущих значений из кэша.
Объяснение запроса
Запрос использует несколько 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 set_options AS [SET], n.stmt_start AS Pos, n.Statement,
(SELECT CR.c.value('@Column', 'nvarchar(128)') + ' = ' +
CR.c.value('@ParameterCompiledValue', 'nvarchar(512)') + ' '
FROM n.params.nodes('ColumnReference') AS CR(c)
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),
CAST (query_plan AS xml) AS [Query plan]
FROM next_level n
ORDER BY n.set_options, n.stmt_start
Так же в последнем SELECT, я конвертирую столбец плана запроса в тип XML, но как я заметил выше эта операция может дать сбой, из-за ограничений типа данных xml в sql sever. Если вы получите такую ошибку, просто закомментируйте этот столбец или поменяйте CAST на TRY_CAST если у вас SQL 2012 и выше. (TRY_CAST возвращает NULL если преобразование невозможно.)
Кроме тех изменений, что я уже упомянул, вы можете сделать еще несколько, чтобы получить интересующую информацию. Например, вы можете включить дополнительные столбцы из sys.dm_exec_query_stats или больше атрибутов плана. Я указал включить только атрибут set_options, т.к. это ключ кэша, который наиболее вероятно отличается. Если вы хотите включить все инструкции в процедуре, включая те, что не относятся ни к каким входным параметрам, просто поменяйте CROSS APPLY на OUTER APPLY.
Получение планов выполнения и параметров из трассировки
Еще одна альтернатива получить планы выполнения, это запустить трассировку вашего приложения или SSMS. Есть несколько событий Showplan events, которые вы можете туда включить. Наиболее многостороннее это Showplan XML Statistics Profile, которое предоставит вам такую же информацию, как если бы вы включили опцию Include Actual Execution Plan в SSMS.
Однако, по нескольким причинам, трассировка редко является хорошей альтернативой. Для начала, включение трассировки информации о планах выполнения приводит к довольно значительной дополнительной нагрузке на сервере чтобы получить эти XML данные. И учтите, что это происходит даже если вы фильтруете события только для вашего spid. Механизм, по которому работает трассировка, таков, что все процессы все равно должны генерировать эти события, что может оказать серьезное влияние на нагруженном сервере.
Далее, если вы запускаете трассировку в Profiler, вы вероятнее всего столкнетесь с тем, что очень трудно установить хороший фильтр, который бы отлавливал все что нужно вам, отбрасывая постороннее. Одной из возможностей, является сохранение трассировки в таблицу на сервер после того как она была остановлена, что позволит вам найти интересующую информацию при помощи запросов. (Но не просите профайлер сохранять в таблицу данные в момент, когда трассировка запущена. Накладные расходы на это, просто ужасны. План содержится в столбце TextData. Преобразуйте его в xml и вы увидите его, как я описывал в предыдущем разделе.
Более хорошей альтернативой будет использование процедуры Lee Tudor’s sp_sqltrace, что я упоминал ранее. У нее есть параметр, который отвечает за то, чтобы собирать планы, вы можете выбрать собирать только оценочные планы или действительный. Однако, sp_sqltrace не будет рабочим решением, если приложение может использовать несколько SPID.
Вы так же можете использовать Extended Events, чтобы получить план выполнения, если вы хороши в Extended Events (я нет), вы можете использовать этот способ. Однако, даже с учетом того, что Extended Events были разработаны с целью меньше нагружать сервер, чем старый добрый Trace, у них те же самые проблемы. Даже если вы фильтруете по spid, все процессы должны генерировать XML для планов.
Примечание: в SQL 2014 SP2 и SQL 2016 SP1 было представлено новое событие query_thread_profile. Когда оно активно, сервер использует легковесный механизм профилировки операторов. По крайней мере в 2016 версии, дополнительная нагрузка составляет всего несколько процентов, однако у меня не было времени исследовать это в деталях, поэтому я не могу дать более подробную информацию.
Получаем информацию о таблицах и индексах
Я предполагаю, что вы уже знакомы с тем как получить информацию о таблице, через 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
Это даст вам sampled статистику. Обычно такую статистку дают вам индексы, что подходит в большинстве случаев, однако, sampled статистка не всегд работает хорошо. В таких случаях возможно стоит использовать статистику, полученную путем полного сканирования данных. Лучше всего для этого подходит команда:
UPDATE STATISTICS tbl WITH FULLSCAN, INDEX
Добавляя ключевое слово INDEX в команду, FULLSCAN выполняется только для статистики созданной вместе с индексами. Это может уменьшить время выполнения обновления статистики созданной отдельно от индексов, UPDATE 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 2012 и выше или последний SP SQL 2008, но не SQL 2005, SQL 2008 SP1 или SQL 2008 R2 RTM. На этих версиях, OPTION (RECOMPILE) не обрабатывает переменные как константы.
Не имеет значения, что вы используете, если процедура состоит всего из одной инструкции. Но для длинной процедуры с множеством инструкций, в которой проблемной является только одна, очевидно что 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. Если вкратце, то OPTION (RECOMPILE) обычно работает хорошо, в подобных случаях.
Просматриваем индексы
Некоторое время назад, один из моих клиентов связался со мной, потому что их заказчики стали испытывать трудности с производительностью в одной функцией в системе. Мой клиент сказал, что в других местах, этот код работает хорошо, и в приложении давно не было никаких изменений. (Ну, вы знаете, клиенты всегда говорят так, как им кажется). Им удалось выявить проблемную процедуру, которая включала в себя запрос наподобие этого:
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)
Результат был таким:
|
Вот в чем дело, в этой колонке только 17 уникальных значений, и они имеют очень неровное распределение. Я подтвердил этот факт, запустив запрос:
SELECT Col3, COUNT(*) FROM Table_A GROUP BY Col3 ORDER BY Col3
Я перешел к просмотру неудачного плана выполнения, чтобы посмотреть значение параметра @p3 для которого был построен этот план. Выяснилось что это значение APPLE — значение, которое не представлено в таблице вообще! Вот по этому, при первом выполнении процедуры SQL Server оценил, что запрос венет одну единственную строку (вспомним о том, что сервер никогда не оценивает результат нулем), и индекс по Col3 был бы наиболее эффективным, для получения этой строки.
Теперь вы можете спросить себя, как так могло неудачно получиться, что процедура в первый раз выполнилась со значением APPLE? Просто не повезло? Т.к. я не знаю систему, я не могу это сказать наверняка, но очевидно, что это происходило уже не в первый раз. У меня сложилось впечатление, что процедура вызывалась много раз, как часть какой-то более сложной, большой операции. Скажем, эта операция всегда начинается со значения APPLE. Помните, что переиндексация таблиц, всегда вызывает рекомпиляцию, и очень распространено делать эти обслуживающие операции по ночам, чтобы держать в порядке ситуацию с фрагментацией. Вот почему, первый утренний вызов для процедуры, является очень важным для производительности. (Почему операция начиналась со значения, которого нет в базе данных? Кто знает, может быть APPLE это какое-то необычное условие, которое должно быть обработано раньше всех, если это значение присутствует. Или может быть это просто алфавитный порядок.)
Для этого отдельного запроса, существует целая куча возможных подходов к решению:
1. OPTION (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_db_index_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 байтовое значение, которое уникально для всей базы данных и монотонно возрастает. Оно обновляется автоматически, когда строка добавляется или обновляется. Этот тип данных также известен как rowversion.) Обычная хранимая процедура, получающая изменения, выглядит так:
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, и здесь не хватит места, чтобы привести их все. Оборачивание колонок в выражение — это только один из примеров, хотя и самый распространенный. Иногда выражение скрыто из-за неявных преобразований, например строковая колонка, содержащая цифры сравнивается с целым значением. Исследуйте план, и когда индекс не используется, так как вы этого ожидали, вернитесь и посмотрите на запрос снова.
