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

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

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

Давненько я хотел написать что-нибудь на эту тему. Однако, пока я собирался с мыслями и силами, наткнулся на уже написанную статью Slow in the Application, Fast in SSMS? Understanding Performance Mysteries Erland-а Sommarskog, которая исчерпывающе отвечает на поставленный вопрос. Так что мне осталось только представить перевод этой статьи, который я для удобства разделил на три части.

Введение

Когда я просматриваю различные форумы по SQL Server, я часто вижу вопросы от глубоко озадаченных пользователей. Они нашли медленный запрос или процедуру в своем приложении. Переместили этот пакет из своего приложения в SQL Server Management Studio (SSMS), чтобы проанализировать и обнаружили, что ответ от сервера приходит мгновенно. С этого момента они начинают думать что SQL Server это нечто магическое. Похожая загадка происходит и в случае если разработчик извлекает запрос из своей хранимой процедуры, чтобы выполнить его отдельно, но обнаруживает что он выполняется гораздо быстрее — или гораздо медленнее — чем внутри хранимой процедуры.
Нет, SQL Server это не магия. Но если у вас нет хорошего понимания того как SQL Server компилирует запросы и поддерживает кэш планов выполнения, так может показаться. Кроме того есть некоторые неудачные комбинации различных настроек по умолчанию в некоторых средах. В этой статье я постараюсь разъяснить, почему вы получаете такое, казалось бы, не согласующееся поведение. Я объясню, как sql server компилирует хранимую процедуру, что такое прослушивание параметров (parameter sniffing) и почему оно является важной частью уравнения в большинстве сбивающих с толку ситуаций. Я объясню, как sql server использует кэш, и почему в кэше может быть несколько записей для одной процедуры. Стоит только копнуть глубже, и вы поймете, как получается так, что запрос в SSMS выполняется гораздо быстрее.

Чтобы понять, как подойти к проблеме производительности в вашем приложении, продолжайте читать дальше. Сначала я сделаю небольшое отступление от темы прослушивания параметров, чтобы обсудить несколько ситуаций, когда существуют другие причины для разницы в производительности. Далее следуют две главы о том, как поступать с проблемами производительности, причина которых прослушивание параметров. В первой пойдет речь о сборе полезной информации, а во второй главе я обсужу некоторые сценарии — как реальные ситуации, так и общие — и возможные решения. В следующей главе я расскажу о том, как компилируется и взаимодействует с кэшем планов динамический sql, и почему существуют еще причины, по которым вы можете сталкиваться с разницей в производительности между SSMS и приложением с динамическим sql. В последней главе я рассмотрю как для поиска и устранения проблем можно использовать Query Store, представленный в SQL Server 2016. В конце приведены ссылки на документацию Microsoft и другие документы по этой теме.

Содержание

  • Введение
    • Содержание
    • Предварительные условия
  • Как SQL Server компилирует хранимую процедуру
    • Что такое хранимая процедура
    • Как SQL Server генерирует план выполнения
    • Помещение плана запроса в кэш
    • Разные планы для разных установок
    • Установки по-умолчанию
    • Влияние перекомпиляции инструкций
    • К чему мы пришли
  • Это не всегда прослушивание параметров
    • Замена переменных и параметров
    • Блокировки
    • Индексированные представления и индексированные вычисляемые столбцы
    • Связанные сервера
  • Собираем информацию для решения проблем прослушивания параметров
    • Собираем необходимые сведения
    • Какая инструкция медленная?
    • Получаем планы выполнения и параметры при помощи Management Studio
    • Получение плана запроса и параметров непосредственно из кэша планов
    • Получение планов выполнения и параметров из трассировки
    • Получаем информацию о таблицах и индексах
    • Извлекаем информацию о статистике
  • Примеры исправления проблем с прослушиванием параметров
    • «Не решение»
    • Лучший индекс, зависит от входных данных
    • Динамические условия поиска
    • Просматриваем индексы
    • Случай с кэшем приложения
    • Исправляем плохой sql код
  • Динамический SQL
    • Что такое динамический SQL
    • Текст запроса — ключ хэш таблицы
    • Важность схемы по умолчанию
    • Авто-параметризация
    • Запуск запросов приложения в SSMS
    • Руководства планов (Plan Guides) и
  • Использование Query Store в SQL Server 2016
    • Введение в Query Store
    • Поиск ключей кэша
    • Поиск прослушанных значений параметров
    • Форсирование планов при помощи Query Store
    • Заключение по Query Store
  • Заключительные замечания
    • Ссылки по теме

Предварительные условия

Суть этой статьи применима ко всем версиям SQL Server, но в фокусе SQL 2005 и более поздние версии. Статья включает несколько запросов для просмотра кэша планов выполнения, эти запросы могут быть выполнены только в SQL 2005 и более поздних версиях. SQL 2000 и более ранние версии имеют куда меньший инструментарий в этом отношении. Учтите, что для выполнения этих запросов вам понадобятся разрешения VIEW SERVER STATE.

Для примеров в этой статье я использую базу данных Northwind. Эта база данных поставляется с 2000 сервером. Для более поздних версий вы можете загрузить ее с сайта Microsoft.

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

Предостережение: В некоторых местах я привожу ссылки на документацию Books Online. Будьте внимательны т.к. ссылки могут вести на Books Online для версии SQL Server отличной от той, которую вы используете. Вверху странице есть ссылка «Другие версии», так что вы довольно просто можете перейти на страницу той версии SQL Server, которую используете вы (по крайней мере так была организована документация Books Online, когда я писал эту статью).

Как SQL Server компилирует хранимую процедуру

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

Что такое хранимая процедура

Вопрос может показаться глупым, но тема, к которой я подбираюсь это: «Какие объекты сервера имеют собственные планы выполнения». SQL Server строит планы для этих типов объектов:

  • хранимые процедуры
  • скалярные функции
  • Multi-step табличные функции
  • триггеры

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

Для других типов объектов, не перечисленных в списке, SQL Server не строит планов выполнения. В частности, SQL Server не создает планов выполнения для представлений и inline табличных функций. Запросы вида:

SELECT abc, def FROM myview
SELECT a, b, c FROM mytablefunc(9)

ни чем не отличаются от ad-hoc запросов получающих доступ к таблице напрямую. При компиляции запроса, SQL Server разворачивает представление/inline-функцию в запрос и оптимизатор работает с развернутым текстом запроса.

Есть еще одна вещь, которую необходимо знать о хранимых процедурах. Скажем, у вас есть две процедуры, в которых одна вызывает другую:

CREATE PROCECURE Outer_sp AS
...
EXEC Inner_sp

Догадываюсь, что большинство думает, что Inner_sp независима от Outer_sp, и так оно и есть на самом деле. План выполнения Outer_sp, не включает в себя план для Inner_sp, а включает только ее вызов. Однако, есть очень похожая ситуация, о которой, как я заметил, участники SQL форумов имеют другое мысленное представление, а именно ситуация с динамическим SQL:

CREATE PROCEDURE Some_sp AS
DECLARE @sql    nvarchar(MAX),
        @params nvarchar(MAX)
SELECT @sql = 'SELECT ...'
...
EXEC sp_executesql @sql, @params, @par1, ...

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

Как SQL Server генерирует план выполнения

Обзор

Когда вы создаете хранимую процедуру при помощи CREATE PROCEDURE (или CREATE FUNCTION для функции или CREATE TRIGGER для триггера), SQL Server проверяет что код синтаксически верен, а так же проверяет что вы не ссылаетесь на несуществующие колонки. (Но если вы ссылаетесь на несуществующие таблицы, он позволяет вам продолжить, из-за одной особенности, известной как отложенное разрешение имен). Однако, на этом этапе, SQL Server не строит план выполнения, а только сохраняет текст запроса в базе данных.

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

Параметры и переменные

Рассмотрим таблицу Orders в базе данных Northwind, и эти три процедуры:

CREATE PROCEDURE List_orders_1 AS
   SELECT * FROM Orders WHERE OrderDate > '20000101'
go
CREATE PROCEDURE List_orders_2 @fromdate datetime AS
   SELECT * FROM Orders WHERE OrderDate > @fromdate
go
CREATE PROCEDURE List_orders_3 @fromdate datetime AS
   DECLARE @fromdate_copy datetime
   SELECT @fromdate_copy = @fromdate
   SELECT * FROM Orders WHERE OrderDate > @fromdate_copy
go

[qoute] Примечание:
Использование SELECT * в рабочем коде — плохая практика. Я использую это в статье просто для краткости.
[/qoute]

Потом выполним эти процедуры следующим образом:

EXEC List_orders_1
EXEC List_orders_2 '20000101'
EXEC List_orders_3 '20000101'

Перед запуском процедур, включите режим Include Actual Execution Plan в меню Query. (Для этого так же есть кнопка на панели инструментов или стандартная комбинация горячих клавиш Ctrl-M). Если вы посмотрите на планы выполнения, то увидите, что две первые процедуры имеют одинаковый план:

согласно которому SQL Server осуществляет поиск по индексу OrderDate, и далее использует key lookup, чтобы получить остальные данные. Третий план выполнения отличается:

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


Примечание автора: внешний вид подсказок будет зависеть от того, какую версию SSMS вы используете. Снимки экрана выше были сделаны в SSMS 2008 на сервере SQL 2008. Если вы используете более позднюю версию SSMS, вы можете видеть несколько другой порядок элементов и большее число свойств. Ни один из этих элементов не имеет отношения к данному примеру, поэтому оставлены снимки экрана для 2008.

Интересующий нас элемент — Estimated Number of Rows(Предполагаемое количество строк). Для двух первых процедур SQL Server предполагает то, что будет возвращена одна строка, но для процедуры List_orders_3 предполагается 249 строк. Эта разница в оценках и объясняет разницу в планах выполнения. Index Seek + Key Lookup — хорошая стратегия для небольшого количества строк. Но чем больше строк начинают удовлетворять условиям поиска, тем больше увеличивается стоимость и вероятность того что SQL Server-у придется осуществить доступ к той же страницее данных более чем один раз. В крайнем случае, когда выбираются все строки, сканирование таблицы гораздо эффективнее, чем поиск и просмотр закладок. При сканировании SQL Server-у нужно прочитать каждую страницу данных ровно один раз, в то время как при поиске с просмотром, каждая страница будет прочитана один раз для каждой отдельной строки. Таблица Orders в Northwind содержит 830 строк, и когда SQL Server оценивает что будет возвращено целых 249 строк, он (совершенно справедливо) заключает, что сканирование — лучший выбор.

Откуда берутся эти оценки?

Теперь мы знаем, почему оптимизатор приходит к разным планам выполнения, из-за разных оценок. Но это только приводит нас к следующему вопросу: почему эти оценки различны? Это ключевая тема этой статьи.

В первой процедуре, дата является константой, которая означает, что SQL Server нужно принимать во внимание только это значение. Он опрашивает статистику для таблицы Orders, которая указывает, что строк со значением OrderDate в третьем тысячелетии — не существует. (Все заказы в базе данных Northwind от 1996 до 1998 года). Т.к. статистика это статистика, SQL Server не может быть уверен, что запрос совсем не вернет строк, по этому он предполагает, по крайней мере, одну строку.

В случае с List_orders_2 запрос делается с переменной, точнее с параметром. Выполняя оптимизацию, SQL Server знает, что процедура была вызвана со значением 2000-01-01. Т.е. он не производит никакого анализа потока выполнения, он не может с уверенностью сказать, будет ли параметр иметь такое же значение при выполнении самого запроса. Тем не менее, он использует входное значение для проведения оценки, которая получается такая же, как и в случае List_orders_1: единственная строка. Эта стратегия просмотра значений входных параметров во время оптимизации хранимой процедуры известна как «прослушивание параметров» (parameter sniffing).

В последней процедуре, все по-другому. Входное значение копируется в локальную переменную, но когда SQL Server строит план выполнения, он не имеет об этом никакого понятия и говорит себе «Я не знаю, какое будет значение у этой переменной». По этому, он применяет стандартное предположение, которое для оператора неравенства, такого как «>» — заключается в 30%-ном коэффициенте эффективности поиска. 30% от 830, в самом деле будет 249.

Вот еще одна вариация на эту тему:

CREATE PROCEDURE List_orders_4 @fromdate datetime = NULL AS
   IF @fromdate IS NULL
      SELECT @fromdate = '19900101'
   SELECT * FROM Orders WHERE OrderDate > @fromdate

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

EXEC List_orders_4

План выполнения идентичен плану выполнения List_orders_1 и List_orders_2. То есть Index Seek + Key Lookup, несмотря на то,что возвращаются все заказы. Если вы посмотрите на всплывающее окно в операторе Index Seek, вы увидите то же самое всплывающее окно, как и в List_orders_2, но за одним исключением — действительным числом строк.

При компиляции процедуры SQL Server не знает о том, что значение @fromdate изменяется, и компилирует процедуру исходя из предположения, что @fromdate имеет значение NULL. Так как все сравнения с NULL приводят к результату UNKNOWN, запрос не должен вернуть строк вообще, если @fromdate так и имеет это значение во время выполнения.

Если SQL Server примет это входное значение как окончательный вариант, он может составить план только из одного оператора Constant Scan, который вообще не пытается получить доступ к таблице (запустите запрос SELECT * FROM Orders WHERE OrderDate > NULL чтобы увидеть пример такого плана). Но SQL Server должен генерировать план, который возвращает верный результат вне зависимости от того какое будет значение у @fromdate во время выполнения. С другой стороны, не обязательно строить план, который был бы лучшим для всех возможных значений. По этому, так как предполагается, что не будет возвращено ни одной строки, SQL Server довольствуется планом с Index Seek. (Оценка все та же, что будет возвращена одна строка. Это потому, что SQL Server никогда не дает оценку в 0 строк).

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

CREATE PROCEDURE List_orders_5 @fromdate datetime = NULL AS
   DECLARE @fromdate_copy datetime
   SELECT @fromdate_copy  = coalesce(@fromdate, '19900101')
   SELECT * FROM Orders WHERE OrderDate > @fromdate_copy

В процедуре List_orders_5 вы всегда будете получать Clustered Index Scan.

Ключевые моменты 

В этой части мы изучили три важные вещи:

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

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

Помещение плана запроса в кэш

Если SQL Server будет компилировать хранимую процедуру — что значит оптимизировать и строить план выполнения — каждый раз, когда она вызывается, существует большой риск того, что SQL Server упадет из-за того, что это займет все ресурсы процессора. Я должен немедленно сделать оговорку, потому что это справедливо не для всех систем. В больших хранилищах данных, где несколько бизнес аналитиков запускают сложные запросы, которые в среднем для выполнения занимают минуты, не будет никакого ущерба от того что компиляция будет происходить каждый раз — скорее это даже будет выгодно. Но в OLTP базе данных, где множество пользователей запускают хранимые процедуры, содержащие короткие и простые запросы это беспокойство очень вероятно может стать оправданным.

По этой причине, SQL Server кэширует план выполнения запроса для хранимой процедуры, так что когда пользователь в следующий раз запускает процедуру, фаза компиляции пропускается, и выполнение может начаться сразу. План останется в кэше, до наступления некоторых событий, которые могут удалить план из кэша. Например, такими событиями являются:
Буфер кэша SQL Server полностью использован, и SQL Server должен удалить из кэша некоторые устаревшие данные, которые не использовались в течении некоторого времени. Буфер кэша включает в себя как табличные данные, так и планы выполнения
Кто-либо выполняет для процедуры операцию ALTER PROCEDURE
Кто-либо выполняет для процедуры операцию sp_recompile
Кто-либо выполняет команду DBCC FREEPROCCACHE, которая целиком очищает кэш планов выполнения
перезапуск SQL Server. Т.к. кэш хранится только в оперативной памяти, кэш не восстанавливается после перезапуска
Изменение определенных параметров конфигурации (при помощи sp_configure или Server Properties в среде SSMS) так же полностью очищает кэш

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

Существуют и другие события, которые не вызывают вытеснение из кэша плана целой хранимой процедуры, но вызывают перекомпиляцию одной или более инструкций в процедуре. Перекомпиляция произойдет, когда инструкция выполнится в следующий раз. Это происходит даже если событие произошло после того как процедура стала выполняться. Вот примеры таких событий:
Изменение структуры таблицы, участвующей в инструкции
Удаление или добавление индекса для таблицы, участвующей в инструкции. Это включает в себя и перестроение индекса при помощи ALTER INDEX или DBCC DBREINDEX
Новая или обновленная статистика для таблицы, участвующей в инструкции. Статистика может быть создана или обновлена SQL Server автоматически. DBA так же может создавать и обновлять статистику при помощи команд CREATE STATISTICS и UPDATE STATISTICS
Кто-либо запускает sp_recompile для таблицы, участвующей в инструкции

Примечание: В Server 2000 нет отдельной перекомпиляции инструкций, вместо этого процедура перекомпилируется целиком.

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

EXEC List_orders_2 '19900101'

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

Примечание: для получения полного списка того что может вызвать вытеснение планов или перекомпиляцию инструкций, обратитесь к разделу Plan Caching официальной документации.

Разные планы для разных установок

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

CREATE PROCEDURE List_orders_6 AS
   SELECT *
   FROM   Orders
   WHERE  OrderDate > '12/01/1998'
go
SET DATEFORMAT dmy
go
EXEC List_orders_6
go
SET DATEFORMAT mdy
go
EXEC List_orders_6
go

Если вы запустите это, то заметите, что первое выполнение возвращает много заказов, тогда как второе не возвращает ни одного. И если вы посмотрите на планы выполнения, вы увидите, что они так же различаются. Для первого выполнения план — Clustered Index Scan (который является лучшим выбором, если будет возвращено много строк), тогда как для второго в плане используется Index Seek и Key Lookup (который лучше подходит, если не возвращается ни одной строки).

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

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

SELECT qs.plan_handle, a.attrlist
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
              FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
              WHERE  epa.is_cache_key = 1
              ORDER  BY epa.attribute
              FOR    XML PATH('')) AS a(attrlist)
WHERE  est.objectid = object_id ('dbo.List_orders_6')
  AND  est.dbid     = db_id('Northwind')

[qoute] Примечание: Вам понадобятся разрешения уровня сервера VIEW SERVER STATE, чтобы выполнить этот запрос.[/qoute]

[qoute]Примечание: Запросы к кешу планов выполнения в этой статье не будут выполнятся в SQL 2000.[/qoute]

DMV (Dynamic Management View) sys.dm_exec_query_stats имеет по одной записи на каждый запрос, находящийся в текущее время в кэше планов выполнения. Если процедура состоит из нескольких инструкций, то оно содержит по одной строке для каждой инструкции. То, что представляет здесь интерес это sql_handle и plan_handle. Я использую sql_handle, чтобы определить к какой процедуре относится запись в кэше (позже мы посмотрим пример, в котором так же получаем и текст запроса), чтобы мы могли отфильтровать все остальные записи. Чаще всего plan_handle используется, чтобы получить сам план выполнения, и позже мы посмотрим на такой пример, но в этом запросе я обращаюсь к DMV, которое возвращает атрибуты плана выполнения. Точнее, возвращаются атрибуты, которые являются «ключами кэша». Когда есть более чем одна запись в кэше для одной и той же процедуры, эти записи имеют хотя бы одно отличие в ключах кэша. Ключ кэша это настройка времени выполнения, которая по той или иной причине вызывает разные планы запроса. Большинство этих настроек контролируются командой SET, но не все.

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

plan_handle                                          attrlist
---------------------------------------------------- --------------------------------------------------------
0x0500070064EFCA5DB8A0A905000000000000000000000000   compat_level=100   date_first=7   date_format=1
                                                     set_options=4347   user_id=1
0x0500070064EFCA5DB8A0A805000000000000000000000000   compat_level=100   date_first=7   date_format=2
                                                     set_options=4347   user_id=1

Для экономии места, я сократил значения в plan_handle, удалил несколько значений в колонке attrlist и сжал колонку в две линии. Если вы сами запустите запрос, вы увидите полный список ключей кэша, которых не так уж и много. Если вы посмотрите раздел sys.dm_exec_plan_attributes в Books Online, вы увидите описание многих атрибутов плана, но также заметите, что далеко не все ключи кэша документированы. В этой статье я не буду углубляться в ключи кэша, даже в документированные, а сфокусируюсь на наиболее важных для нас.

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

EXEC sp_recompile List_orders_2
go
SET DATEFORMAT dmy
go
EXEC List_orders_2 '12/01/1998'
go
SET DATEFORMAT mdy
go
EXEC List_orders_2 '12/01/1998'
go

(Вызов sp_recompile вначале позволяет удостовериться, что предыдущий план выполнения будет удален). Этот пример приводит к таким же результатам и таким же планам, как и пример с List_orders_6 выше. Это потому что два плана запроса используют текущее значение параметра, когда строится соответствующий план. Первый запрос использует 12 января 1998 года, второй 1 декабря 1998 года.

Очень важным ключом кэша являются set_options. Это битовая маска, которая устанавливает несколько параметров SET в значения ON или OFF. Если вы посмотрите дальше в разделе sys.dm_exec_plan_attributes, вы найдете подробный список SET опций, который описывает каждый бит. (Вы так же увидите, что есть еще несколько элементов, которые не управляются командами SET). Таким образом, если два подключения различаются в значении хотя бы одной опции, подключения будут использовать разные записи в кэше планов для одной и той же процедуры – и, следовательно, сами планы тоже могут отличаться, с возможно большой разницей в производительностью.

Одним из способов преобразовать атрибут set_options является выполнение запроса:

SELECT convert(binary(4), 4347)

Это скажет нам о том, что 16-ричное значение 4347 это 0x10FB. Затем мы можем посмотреть в Books Online и используя таблицу выяснить что включены следующие опции: ANSI_PADDING, Parallel Plan, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_NULL_DFLT_ON и ARITHABORT.Вы так же можете использовать табличную функцию написанную мной и выполнить:

SELECT Set_option FROM setoptions (4347) ORDER BY Set_option
Примечание: Вы можете удивиться, что тут делает опция Parallel Plan, ведь план в примере не является параллельным. Когда SQL Server строит параллельный план для запроса, он может позже так же построить и непараллельный план, если загрузка процессора такова, что делает выполнение параллельного плана неоправданно. По-видимому, для плана, который всегда последователен, бит параллельного плана, тем не менее, остается установленным в set_options.
Чтобы упростить рассуждения, скажем, что каждая из SET опций — ANSI_PADDING, ANSI_NULLS и т.д. — сама по себе является ключом кэша. Тот факт, что они добавляются вместе единым числовым значением — просто детали реализации.

Установки по-умолчанию

Большинство из существующих опций SET ON/OFF являющихся ключами кэша существуют по историческим причинам. Изначально в далеком дремучем прошлом, SQL Server включал в себя некоторые действия, которые нарушали стандарт ANSI для SQL. С выходом SQL Server 6.5 Microsoft представила все эти SET опции (кроме ARITHABORT, которая была уже в версии 4.x), чтобы дать возможность пользователям использовать SQL Server в ANSI совместимом режиме. В SQL 6.5, нужно было устанавливать эти опции явным образом, чтобы получить поведение соответствующее ANSI, но с версией SQL 7, Microsoft поменяла значения по-умолчанию для клиентов, которые использовали новые версии ODBC и OLE DB API. SET опции все еще остаются, для обеспечения обратной совместимости для старых клиентов.

Примечание: В случае если вам любопытно, какое влияние оказывают эти SET опции, я отсылаю вас к Books Online. Некоторые из них объясняются ясно и четко, тогда как остальные сбивают с толку. Чтобы понять эту статью, вам нужно знать только что они существуют, и что они оказывают влияние на кэш планов выполнения.

Увы, Microsoft делала изменения настроек по-умолчанию не совсем последовательно, и даже сегодня эти настройки зависят от того, как вы подключаетесь, о чем говорит таблица ниже:

Applications using
ADO .Net,
ODBC,
OLE DB
SSMS,
Query Analyzer
SQLCMD,
OSQL,
BCP,
SQL Server Agent
ISQL,
DB-Library
ANSI_NULL_DFLT_ON ON ON ON OFF
ANSI_NULLS ON ON ON OFF
ANSI_PADDING ON ON ON OFF
ANSI_WARNINGS ON ON ON OFF
CONACT_NULLS_YIELD_NULL ON ON ON OFF
QUOTED_IDENTIFIER ON ON OFF OFF
ARITHABORT OFF ON OFF OFF

Вы можете видеть, где это проявляется. Ваше приложение подключается с ARITHABORT OFF, но когда вы запускаете запрос в SSMS, ARITHABORT установлен в ON и по этому вы не будете пере использовать тот же план из кэша, что использует приложение, но SQL Server скомпилирует процедуру заново, прослушивая ваши текущие значения параметров и вы можете получить план отличающийся от того что использует приложение. Итак, вот вы и получили самый вероятный ответ на изначальный вопрос этой статьи. Есть еще несколько возможных вариантов, на которые мы посмотрим в следующей главе, но наиболее распространенная причина «медленно в приложении, быстро в SSMS» для SQL 2005 и более поздних версий это — прослушивание параметров и разные установки для ARITHABORT. (Если это все что вам требовалось узнать, вы можете не читать дальше. Если вы хотите исправить проблемы в производительности — держитесь! И нет, установка ARITHABORT в ON в процедуре не решение.)

Помимо команд SET и настроек по-умолчанию перечисленных выше, ALTER DATABASE разрешает вам указать, что определенная опция SET должна быть всегда включена в базе данных по умолчанию и таким образом переопределить настройки по-умолчанию для API. Однако, хотя синтаксис и может указывать на это, но вы не можете таким же способом указать чтобы опция была всегда OFF. Так же, будьте внимательны, если проверяете эти опции из Management Studio, может казаться что это не работает, т.к. SSMS отправляет на сервер команды SET явно. Для той же цели есть и настройка уровня сервера, настройка user options, которая является битовой маской. Вы можете устанавливать индивидуальные биты в маске из страниц Connection — Server Properties в Management Studio. В общем и целом я против того чтобы контролировать настройки по-умолчанию таким образом, по моему мнению, это может привести к еще большей путанице.

Не всегда влияние оказывают только настройки во время выполнения. Когда вы создаете хранимую процедуру, представление, таблицу и т.д. — настройки ANSI_NULLS и QUOTED_IDENTIFIER так же сохраняются вместе с объектом. Таким образом, если вы запустите это:

SET ANSI_NULLS, QUOTED_IDENTIFIER OFF
go
CREATE PROCEDURE stupid @x int AS
IF @x = NULL PRINT "@x is NULL"
go
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
go
EXEC stupid NULL

Будет напечатано: @x is NULL

(Когда QUOTED_IDENTIFIER OFF, двойная кавычка («) является обозначением строки и используется так же как одинарная (‘). Когда установка включена, двойные кавычки используются как разделители идентификаторов в запросах, так же как и квадратные скобки ([]) и инструкция PRINT вызовет ошибку компиляции.)

Так же, установка ANSI_PADDING сохраняется для колонок каждой таблицы, к которой это применимо. (Имеются ввиду типы данных varchar, nvarchar и varbinary).

Все эти настройки и разные значения по умолчанию без сомнения сбивают с толку, но я советую следующее. Во-первых, первые шесть из этих семи настроек существуют только для обратной совместимости, поэтому нет особых причин устанавливать их в значение OFF. Да, бывают ситуации, когда кажется, что установка некоторых из них в OFF даст больше удобства, но не поддавайтесь этому соблазну. Единственная сложность здесь в том, что инструменты SQL Server генерируют команды SET для некоторых настроек, когда вы генерируете скрипты для объектов. К счастью, в основном они генерируют SET ON команды, которые безвредны. (Но когда вы генерируете скрипт для таблицы, вы все еще можете получить команду SET ANSI_PADDING OFF. Вы можете контролировать это при помощи меню Tools->Options->Scripting, где вы можете установить Script ANSI_PADDING команды в значение false, что я и рекомендую.)

Далее, когда дело доходит до ARITHABORT, вы должны знать, что в SQL 2005 и более поздних версиях эта установка имеет нулевое влияние, до тех пор, пока ANSI_WARNINGS установлен в ON. (Чтобы быть точным, эта настройка не влияет до тех пор, пока уровень совместимости базы 90 или выше.) Таким образом, нет необходимости ее включать просто так. И когда речь заходит об SQL Server Management Studio, вы можете оказать себе услугу, открыть нижеследующий диалог и отключить SET ARITHABORT.

Это изменит настройку по-умолчанию для ARITHABORT при подключении через SSMS. Это не заставит ваше приложение выполняться быстрее, но вы хотя бы, не будете сбиты с толку, получая другую производительность в SQL Server Management Studio.

Для справки, внизу приведен рисунок как должна выглядеть вкладка ANSI. Я очень сильно рекомендую никогда ничего не менять на этой странице!

Когда дело касается SQLCMD и OSQL, выработайте в себе привычку, всегда использовать опцию командной строки -I, которая заставляет эти инструменты запускаться с установкой QUOTED_IDENTIFIER ON. Соответствующая опция для BCP это -q. Когда дело касается SQL Server Agent все немного сложнее, т.к. способа поменять значения по умолчанию нет — или, по крайней мере, я такой не нашел. И снова, если в шагах джоба вы запускаете хранимые процедуры, проблем не будет, т.к. приоритет имеют настройки сохраненные для процедуры. Но если вы выполняете отдельные батчи SQL кода, то вы можете столкнуться с проблемой разных планов запросов в SSMS и SQL Server Agent из-за разницы в настройке QUOTED_IDENTIFER, заданной по умолчанию. Для таких джобов необходимо всегда явно указывать значение этой настройки SET QUOTED_IDENTIFIER ON в качестве первой команды.

Мы уже посмотрели на SET DATEFORMAT, и есть еще две опции в этой группе: LANGUAGE и DATEFIRST. Язык по-умолчанию конфигурируется для каждого пользователя, а так же есть настройка на весь сервер, которая отвечает за язык по-умолчанию для вновь создаваемых пользователей. Язык по-умолчанию, определяет значения по-умолчанию у двух других опций. Т.к. они являются ключами кэша, это значит, что два пользователя с разными настройками языка будут иметь разные записи в кэше, что может привести к разным планам.

Моя рекомендация, это стараться избегать зависимостей от языка и формата даты в SQL Server. Для присвоения значений, если вы вообще используете литералы для задания дат, используйте формат, который всегда интерпретируется одинаково, такой как YYYYMMDD. (Для более подробного описания о форматах даты, смотрите статью SQL Server MVP Tibor-а Karaszi The ultimate guide to the datetime datatypes.) Если вы хотите осуществлять локализованный вывод из хранимой процедуры в зависимости от языка пользователя, возможно лучшим решением будет использовать свой механизм, чем полагаться на установку языка в SQL Server.

Влияние перекомпиляции инструкций

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

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

CREATE PROCEDURE List_orders_7 @fromdate datetime,
                               @ix       bit AS
   SELECT @fromdate = dateadd(YEAR, 2, @fromdate)
   SELECT * FROM  Orders WHERE OrderDate > @fromdate
   IF @ix = 1 CREATE INDEX test ON Orders(ShipVia)
   SELECT * FROM  Orders WHERE OrderDate > @fromdate
go
EXEC List_orders_7 '19980101', 1

Когда вы запустите это и взглянете на план выполнения, вы увидите, что план для первой инструкции SELECT это Clustered Index Scan, который прекрасно согласуется с тем, что мы уже знаем. SQL Server прослушивает значение 1998-01-01 и оценивает, что запрос вернет 267 строк, что слишком много для чтения при помощи Index Seek + Key Lookup. Что SQL Server не знает, так это то, что значение @fromdate меняется, перед тем как запрос будет выполнен. Тем не менее, план для второго, точно такого же запроса это Index Seek + Key Lookup и количество возвращаемых строк оценивается как один. Это происходит потому, что инструкция CREATE INDEX устанавливает пометку о том, что изменилась схема таблицы Orders, что и вызывает перекомпиляцию второй инструкции SELECT. При перекомпиляции SQL Server заново прослушивает значение параметра, которое в данный момент является текущим, и таким образом находит более хороший план.

Запустите процедуру снова, но с другими параметрами (обратите внимание, что теперь дата установлена на два года раньше):

EXEC List_orders_7 '19960101', 0

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

Перед тем как продолжить, удалите:

DROP INDEX test ON Orders
DROP PROCEDURE List_orders_7

Как я и говорил, пример является надуманным. Я сделал его таким образом, чтобы получить простой компактный пример, который легко запустить. В реальной ситуации, у вас может быть хранимая процедура, которая использует один и тот же параметр в двух запросах к разным таблицам. DBA создает новый индекс на одной из этих таблиц, который вынуждает запрос к этой таблице перекомпилироваться, в то время как второй нет. Ключевым знанием, которое необходимо вынести отсюда является то, что планы для двух инструкций в процедуре могут быть скомпилированы для разных значений «прослушанных» параметров.

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

CREATE PROCEDURE List_orders_8 AS
   DECLARE @fromdate datetime
   SELECT @fromdate = '20000101'
   SELECT * FROM  Orders WHERE OrderDate > @fromdate
   CREATE INDEX test ON Orders(ShipVia)
   SELECT * FROM  Orders WHERE OrderDate > @fromdate
   DROP INDEX test ON Orders
go
EXEC List_orders_8
go
DROP PROCEDURE List_orders_8

В этом примере мы получаем Clustered Index Scan для обеих инструкций SELECT, несмотря на то, что вторая инструкция SELECT перекомпилируется во время выполнения, и значение @fromdate в этот момент так же известно.

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

CREATE PROCEDURE List_orders_9 AS
   DECLARE @ids TABLE (a int NOT NULL PRIMARY KEY)
   INSERT @ids (a)
      SELECT OrderID FROM   Orders
   SELECT COUNT(*)
   FROM   Orders O
   WHERE  EXISTS (SELECT *
                  FROM   @ids i
                  WHERE  O.OrderID = i.a)
   CREATE INDEX test ON Orders(ShipVia)
   SELECT COUNT(*)
   FROM   Orders O
   WHERE  EXISTS (SELECT *
                  FROM   @ids i
                  WHERE  O.OrderID = i.a)
DROP INDEX test ON Orders
go
EXEC List_orders_9
go
DROP PROCEDURE List_orders_9

Когда вы это запустите, вы получите в общем четыре плана выполнения. Два интересующих нас, это второй и четвертый планы, которые получаются для двух одинаковых SELECT COUNT(*) запросов. Я привожу здесь интересующий нас части планов, вместе с всплывающим окном для операции Clustered Index Scan над табличной переменной.

В первом плане мы видим оператор Nested Loops Join вместе с Clustered Index Seek по таблице Orders, который соответствует оценке количества строк в таблице: одна единственная строка, стандартное предположение. Во втором запросе соединение выполняется при помощи оператора Merge Join совместно со сканированием таблицы Orders. Как вы видите, оценка для табличной переменной теперь 830 строк, потому что при перекомпиляции запроса, SQL Server «прослушивает» количество строк табличной переменной, даже если она не является параметром.

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

Говоря о табличных переменных, вам, наверное, будет любопытно, а что насчет табличных параметров, появившихся в SQL 2008. Они обрабатываются очень похоже на табличные переменные, но так как параметры заполняются, перед тем как процедура будет вызвана, то это нормальная ситуация, что SQL Server использует оценку отличную от одной строки. Вот пример:

CREATE TYPE temptype AS TABLE (a int NOT NULL PRIMARY KEY)
go
CREATE PROCEDURE List_orders_10 @ids temptype READONLY AS
   SELECT COUNT(*)
   FROM   Orders O
   WHERE  EXISTS (SELECT *
                  FROM   @ids i
                  WHERE  O.OrderID = i.a)
go
DECLARE @ids temptype
INSERT @ids (a)
   SELECT OrderID FROM   Orders
EXEC List_orders_10 @ids
go
DROP PROCEDURE List_orders_10
DROP TYPE temptype

План выполнения для этой процедуры такой же как и для второго запроса SELECT в процедуре List_orders_9, т.е. Merge Join + Clustered Index Scan по таблице Orders, т.к. SQL Server видит 830 рядов в переменной @ids, при компиляции запроса.

К чему мы пришли

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

На практике наиболее важной опцией SET является ARITHABORT, потому что значение по-умолчанию для этой опции отличается для приложений и для SQL Server Management Studio. Это объясняет, почему вы можете обнаружить медленный запрос в вашем приложении, и затем получить хорошую скорость, выполняя его в SSMS. Приложение использует план, который был построен для набора значений отличающегося от актуальных, правильных значений. Тогда как если вы запускаете запрос в SSMS, то вероятнее всего в кэше пока еще не имеется плана выполнения для ARITHABORT ON, и поэтому SQL Server построит план для ваших текущих значений.

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

SET ARITHABORT OFF

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

Примечание
Всегда есть забавные варианты. Приложение, с которым я в основном работаю, действительно отправляет команду SET ARITHABORT ON, когда соединяется с сервером, по этому мы не должны видеть такое сбивающее с толку поведение в SSMS. Вот только, оно есть. Некоторые части приложения, также посылают серверу команду SET NO_BROWSETABLE ON при соединении. Никогда не понимал влияния этой недокументированной команды SET, но я кажется, припоминаю, что она относилась к ранним версиям «классического» ADO. И да, она является ключом в кэше планов.

Это не всегда прослушивание параметров

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

Замена переменных и параметров

Я уже затрагивал эту тему, но хочу остановиться на этом более подробно.

Время от времени, я вижу на форумах людей, которые говорят мне, что их процедура выполняется медленно, но когда они запускают тот же запрос вне процедуры — он работает быстро. После нескольких постов в теме, вскрывается правда: запрос с которым идет борьба использует переменные, будь то локальные переменные или параметры. Чтобы выявить проблему в самом запросе, они заменяют переменные константами. Но как мы видели, итоговый одиночный запрос довольно отличается, и SQL Server может сделать более точную оценку, имея в распоряжении константы, а не переменные и, следовательно, построить лучший план. Более того SQL Server не должен принимать во внимание то что константа будет иметь другое значение при следующем запуске запроса. (Вообще-то это не совсем так, как мы увидим в главе про динамический SQL).

Похожая ошибка — заменить параметры переменными. Допустим, у вас есть:

CREATE PROCEDURE some_sp @par1 int AS
   ...
   -- Запрос использующий @par1

вы хотите отладить сам запрос, поэтому вы делаете вот это:

DECLARE @par1 int
SELECT @par1 = 4711
-- запрос

Из того что вы изучили, вы уже знаете, что это очень отличается от того если бы @par1 действительно был параметром. SQL Server не имеет представления о значении для @par1, когда вы объявляете локальную переменную и будет руководствоваться стандартными предположениями.

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

Один из способов справиться с этим, это включить запрос в процедуру sp_executesql.
sp_executesql

EXEC sp_executesql N'-- Некоторый запрос с @par1', N'@par1 int', 4711

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

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

CREATE PROCEDURE #test @par1 int AS
   -- запрос.

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

Блокировки

Не следует забывать, что одна из возможных причин, по которым процедура долго выполнялась в приложении, может быть элементарное блокирование. Когда вы проверяли запрос три часа назад в SSMS, тот, кто заблокировал, закончил свою работу. Если вы видите, что не имеет значения, как вы запускаете процедуру в SSMS, со включенной ARITHABORT или нет, она все равно выполняется быстро, блокировка становится наиболее вероятным объяснением. В следующий раз, когда вы заметите, что процедура выполняется медленно, вы должны провести некоторое расследование и анализ блокировок. Эта тема полностью выходит за рамки данной статьи, но в качестве полезного инструмента для исследования блокировок можете посмотреть мою процедуру beta_lockinfo

Индексированные представления и индексированные вычисляемые столбцы

Эта проблема наиболее характерна для SQL 2000 чем для более поздних версий. Или, чтобы быть точным, это применимо только к БД уровня совместимости 80, поэтому может случиться и в SQL 2005, 2008. (Более поздние версии не поддерживают этот уровень совместимости.)

Если уровень совместимости БД 90 и выше, то говоря об индексированных представлениях и вычисляемых столбцах (так же как и для фильтрованных индексов в SQL 2008) при компиляции запроса, следующие опции должны быть включены: QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL. Более того, NUMERIC_ROUNDABORT должен быть OFF. Но на уровне совместимости 80, есть еще одна опция, которая должна быть включена, и, да, вы угадали, это — ARITHABORT. (Причина этого в том, что в SQL 2000 существует тип ошибок — domain errors, например, sqrt(-1) — которые покрываются опцией ARITHABORT, а не ANSI_WARNINGS).

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

Есть еще один феномен, с которым вы можете столкнуться при использовании индексированных представлений и вычисляемых столбцов. Он не уникален только для SQL 2000, но проявляется в основном там. У вас есть хранимая процедура, которая выполняется медленно. Вы копируете медленную инструкцию и запускаете отдельно, и теперь она выполняется молниеносно, даже если вы перед вызовом правильно упаковали ее во временную хранимую процедуру, как показано выше. Почему?
Запустите этот запрос:

SELECT objectproperty(object_id('ваша_хп'), 'IsQuotedIdentOn'),
       objectproperty(object_id('ваша_хп'), 'IsAnsiNullsOn')

Очень вероятно, что он вернет ноль, по крайней мере, в одной из колонок. Как я заметил ранее, эти две опции QUOTED_IDENTIFIER и ANSI_NULLS, сохраняются вместе с хранимой процедурой, и по этому во время выполнения процедуры применяются эти настройки , а не настройки соединения.

Но почему эти опции отключены? Я могу предположить, что это происходит в основном с БД унаследованными из SQL 2000 и более ранних версий. А именно, в SQL 2000 вы можете создавать хранимые процедуры при помощи Enterprise Manager, и Enterprise Manager всегда устанавливает SET QUOTED_IDENTIFIER OFF и SET ANSI_NULLS OFF перед созданием объекта и это нельзя изменить настройками. БД могла быть перемещена на более новую версию, но когда вы генерируете скрипты при помощи SSMS, SSMS генерирует команды SET OFF для сохранения старых настроек. Есть все причины поменять эти SET команды.

Если только QUOTED_IDENTIFIER в OFF, но ANSI_NULLS в ON для хранимой процедуры, можно подозревать, что она была создана при помощи SQLCMD, который, как мы видели, по умолчанию запускается с установкой QUOTED_IDENFIFIER OFF. (Всегда запускайте эти утилиты с параметром -I для переопределения).

Чтобы найти все подверженные такой проблеме модули, выполните следующий запрос:

SELECT o.name
FROM   sys.sql_modules m
JOIN   sys.objects o ON m.object_id = o.object_id
WHERE  (m.uses_quoted_identifier = 0 or
        m.uses_ansi_nulls = 0)
  AND  o.type NOT IN ('R', 'D')

Связанные сервера

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

SELECT C.*
FROM   SQL_2008.Northwind.dbo.Orders O
JOIN   Customers C ON O.CustomerID = C.CustomerID
WHERE  O.OrderID > 20000

Я запустил этот запрос дважды, залогинившись из-под двух разных пользователей. Первый пользователь sysadmin на обоих серверах, тогда как второй обычный пользователь только с разрешениями SELECT. Чтобы удостовериться в том, что будут получены две разные записи в кэше, я использовал разные настройки для ARITHABORT.

Когда я запустил запрос под sysadmin-ом, я получил такой план:

Когда я запустил запрос под простым пользователем, план был другой:

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

Вы видите, что оценки отличаются. Когда я запустил запрос под sysadmin, оценка — 1 строка, что является верной оценкой, поскольку в базе данных Northwind не существует заказов с ID большим 20000. (Вспомните, что оптимизатор никогда не предполагает 0 строк из статистики). Но когда я запустил запрос как простой пользователь, оценка 249 строк. Мы помним это значение как 30% от 830 заказов, т.е. стандартное предположение для оценки неравенства, в случае если у оптимизатора отсутствует информация. Ранее это происходило потому, что было неизвестно значение переменной, но в данном случае нет переменной, которая была бы неизвестна. Нет, отсутствует сама по себе информация о статистике.

Когда запрос осуществляет доступ к таблицам находящимся только на локальном сервере, оптимизатор всегда может получить доступ к статистике всех таблиц в запросе; Это происходит внутри SQL Server, и нет никаких дополнительных проверок, имеет ли пользователь разрешения на просмотр статистики. Но это не так в случае таблиц расположенных на связанных серверах. Когда SQL Server осуществляет доступ к таблице на связанном сервере, оптимизатор должен получить статистику используя то соединение, что используется, чтобы получать данные, при помощи команд T-SQL, вот где разрешения вступают в игру.И, если сопоставление логинов не было выполнено, это разрешения пользователя выполняющего запрос. Используя Profiler или Расширенные события, вы можете видеть, что оптимизатор получает статистику в два шага.
Сначала он вызывает процедуру sp_table_statistics2_rowset, которая возвращает информацию как о том какая статистика по столбцам существует, так и о количестве строк (cardinality) и плотности (density) столбцов. Вторым шагом, он запускает DBCC SHOW_STATISTICS — чтобы получить полное распределение статистики. (Подробнее мы посмотрим на эту команду чуть позже в данной статье.)

Примечание: чтобы быть точным, оптимизатор не общается со связанным сервером совсем, но он общается с OLE DB провайдером для удаленного источника и запрашивает у провайдера те данные, что нужны оптимизатору.

Чтобы процедура sp_table_statistics2_rowset завершилась успешно, пользователь должен иметь разрешения VIEW DEFINITION на таблице. Это разрешение подразумевается если у пользователя есть разрешения SELECT (без которых пользователь не смог бы запустить запрос вовсе). Таким образом, до тех пор, пока пользователю не выдали явно разрешения VIEW DEFINITION, эта процедура не очень полезна.
С DBCC SHOW_STATISTICS другая история. Долгое время, запуск этой команды требовал членство в серверной роли sysadmin или одной из ролей базы данных db_owner или db_ddladmin. Это изменилось в SQL 2012 SP1, начиная с этой версии требуются только разрешения SELECT.

И вот почему я получил разные результаты. Как вы можете заключить из названия сервера в примере, мой связанный сервер был версии 2008. Поэтому, когда я подключался пользователем sysadmin и запускал запрос я получал доступ к полному распределению статистики, которое показало, что нет строк с ID > 20000 и привело к оценке «одна строка». Но при запуске из-под простого пользователя, запрос к DBCC SHOW_STATISTICS не прошел с ошибкой доступа. Эта ошибка не была передана пользователю, вместо этого оптимизатор действовал так, как будто статистики не существует и дал оценку на основе стандартных предположений. И поскольку он, тем не менее, получил информацию о том что количество строк в удаленной таблице 830, то оценка получилась равной 249.

Из сказанного выше, это не должно быть проблемой если связанный сервер версии SQL 2012 SP1 или выше, но тем не менее возможные некоторые трудности. Если пользователь имеет разрешения SELECT не на всех столбцах таблицы, может быть статистика, которую оптимизатор не сможет получить. Более того, согласно Books Online существует флаг трассировки 9485, который разрешает DBA предотвратить разрешения SELECT для DBSS SHOW_STATISTICS. Еще более важно, если для таблицы включена безопасность на уровне строк (функционал добавленный в SQL 2016), то только разрешений SELECT не достаточно, поскольку они могут дать возможность пользователю увидеть те данные, к которым у него не должно быть доступа. Поэтому, чтобы запустить DBSS SHOW_STATISTICS на таблице с фильтрацией безопасности на уровне строк, вы должны быть членом группы sysadmin, db_owner или ddladmin. (Довольно интересно, что можно ожидать, что тоже самое требуется если на таблице применяется динамическое маскирование данных, но это не тот случай.)

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

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

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

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

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

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

Оргининал: Slow in the Application, Fast in SSMS? Understanding Performance Mysteries 

 

4 комментария

  1. Дима

    Огромное спасибо значительно расширили мой кругозор.
    да и в целом БЛОГ очень понятный и грамотный.

  2. Игорь

    Здравствуйте. Очень хороший блог — очень много нового узнал. Большое спасибо за труд! Последующие две части доступны для скачивания в PDF. По этой части скачиваться файл с одной единственной пустой страницей. Можно это поправить?

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

  4. Алексей

    Здравствуйте. Спасибо вам, хорошая статья, как и последующие. Возможно я просто проглядел, но вы не упоминаете, что прослушивание параметров это не только проблема ХП, но и также динамического SQL к примеру (при использовании sp_executesql), здесь об этом пишет Пол — https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options.
    Т.е. как я понимаю, ХП и дин. SQL (через sp_..), а также скяларные функции повторно используют план который был построен при первом выполнении (с учетом схемы и того что запрос один в один для sp_..) и испытывают одни и те же проблемы с этим связанные? Получается способы решения подобной пробемы у них таже одинаковые?

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

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

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