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

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

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

Динамический SQL

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

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

— Инструкции SQL исполняемые при помощи EXEC() и sp_executesql.
— Инструкции SQL отправляемые непосредственно с клиента.
— Инструкции SQL отправленные из модулей, написанных при помощи SQLCLR.

Динамический SQL имеет две разновидности, параметризованный и непараметризованный. В непараметризованном SQL программист составляет строку SQL при помощи объединения элементов языка, со значениями параметров. Например:

SELECT @sql = 'SELECT mycol FROM tbl WHERE keycol = ' + convert(varchar, @value)
EXEC(@sql)

или на C#:

cmd.CommandText = "SELECT mycol FROM tbl WHERE keycol = " + value.ToString();

Непараметризованный SQL плох по нескольким причинам, пожалуйста, посмотрите мою статью The Curse and Blessings of Dynamic SQL , в которой объясняется почему.

В параметризованном SQL, вы передаете параметры, по аналогии с хранимыми процедурами.

Пример на T-SQL:

EXEC sp_executesql N'SELECT mycol FROM dbo.tbl WHERE keycol = @value',
                   N'@value int', @value = @value

или на c#:

cmd.CommandText = "SELECT mycol FROM dbo.tbl WHERE keycol = @value";
cmd.Parameters.Add("@value", SqlDbType.Int);
cmd.Parameters["@value"].Value = value;

код на c# в результате приводит к точно такому же вызову sp_executesq, что показан выше в примере T-SQL.

Для более подробного описания sp_executesql, пожалуйста, обращайтесь к моей статье The Curse and Blessings of Dynamic SQL.

Текс запроса это ключ в хэш таблице

 

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

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

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

Запустите это, включив опцию Include Actual Execution Plan:

EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', '20000101'
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', '19980101'
EXEC sp_executesql N'select * from Orders where OrderDate > @orderdate',
                   N'@orderdate datetime', '19980101'

вы обнаружите, что первые два вызова используют один и тот же план Index Seek + Key Lookup, в то время как третий запрос использует Clustered Index Scan. То есть второй вызов использует план, созданный при первом вызове. Но в третьем вызове ключевые слова SQL написаны нижнем регистре, а для этого текста в кэше нет соответствия, и поэтому создается новый план. Просто чтобы еще раз это продемонстрировать, вот второй пример, приводящий к такому же результату:

 

DBCC FREEPROCCACHE
go
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', '20000101'
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', '19980101'
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate ',
                   N'@orderdate datetime', '19980101'

Разница только в концевом пробеле в третьем вызове.

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

SELECT '<' + est.text + '>'
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
WHERE  est.text LIKE '%Orders%'

Вы увидите вывод:

<(@orderdate datetime)SELECT * FROM Orders WHERE OrderDate > @orderdate>

<(@orderdate datetime)SELECT * FROM Orders WHERE OrderDate > @orderdate >

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

EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
                   N'@orderdate  datetime', '19980101'

Это тот самый запрос еще раз, но с еще одним пробелов с списке параметров. Если вы повторно запустите запрос по DMV, вы увидите, что теперь он возвращает три строки.

Эти детали про список параметров могут показаться тривиальными, но они имеют важное значение, когда вы пишете клиентский код. Например, рассмотрим следующий код на C#:

cmd.CommandText = "SELECT * FROM dbo.Orders WHERE CustomerID = @c";
cmd.Parameters.Add("@c", SqlDbType.NVarChar).Value = TextBox.Value;

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

exec sp_executesql N'SELECT * FROM Orders WHERE CustomerID = @c',
                   N'@c nvarchar(5)',@c=N'ALFKI'

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

Простое решение этой проблемы — всегда указывайте длину явно:

cmd.Parameters.Add("@c", SqlDbType.NVarChar, 5).Value = TextBox.Value;

Здесь я использовал 5, поскольку тип CustomerID это nchar(5). Если вы не хотите зависеть от модели данных, вы можете указать большую длину, например 4000 (что является максимальной длиной для обычного nvarchar).

Значение схемы по-умолчанию

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

DBCC FREEPROCCACHE
go
CREATE SCHEMA Schema2
go
CREATE USER User1 WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo
CREATE USER User2 WITHOUT LOGIN WITH DEFAULT_SCHEMA = Schema2
GRANT SELECT ON Orders TO User1, User2
GRANT SHOWPLAN TO User1, User2
go
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', '20000101'
go
EXECUTE AS USER = 'User1'
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', '19980101'
REVERT
go
EXECUTE AS USER = 'User2'
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', '19980101'
REVERT
go
DROP USER User1
DROP USER User2
DROP SCHEMA Schema2
go

Скрипт сначала очищает кэш планов, затем создает схему и двух пользователей в базе данных и дает им разрешение на выполнение запросов. Затем мы запускаем один и тот же запрос три раза, но с разными значениями параметров. В первый раз мы передаем дату, которая находится за границей диапазона дат в Northwind, поэтому оптимизатор строит план с Index Seek + Key Lookup. Для второго и третьего раза, мы запускаем запрос с другой датой, для которой Clustered Index Scan более хороший выбор. Но т.к. уже есть кешированный план, мы ожидаем, что будет использован он, и это происходит во втором выполнении. Однако, в третьем выполнении, мы видим что используется план со сканированием кластерного индекса. Что произошло, почему мы не получили план из кэша?

Ключевой момент здесь в том, что мы запустили запрос от имени трех разных пользователей. Первый раз от самого себя (предполагая, что мы dbo), но в двух других случаях от имени двух других созданных пользователей. (Если вы не знакомы с имперсонализацией, посмотрите тему EXECUTE AS в Books Online; я так же рассказываю об этом в своей статье Granting Permissions through Stored Procedures.) Пользователи созданы без логинов, но это потому, что нам просто не нужны логины в этом примере. Что важно, так это то, что пользователи имеют разные схемы по-умолчанию. У User1 схема по-умолчанию dbo, а у User2 схема Schema2. Почему это имеет значение?

Помните, что когда SQL Server ищет объект, он сначала ищет в схеме по-умолчанию для пользователя, а потом, если объект не найден, в схеме dbo . Для dbo и пользователя User1, запрос не является неопределенным, так как dbo их схема по-умолчанию, и такая же схема у таблицы Orders. Но для User2 — это не так. Сейчас есть только таблица dbo.Orders, но что если Schema2.Orders будет добавлена позже? По правилам, User2 должен будет начать получать информацию из добавленной таблицы, а не из dbo.Orders. Но если User2 будет использовать ту же запись в кэше что и dbo и User1, этого не произойдет. Поэтому, User2 нужна своя запись в кэше. И если добавится Schema2.Orders, то эту запись можно будет сделать недействительной, не влияя на остальных пользователей.

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

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 a.attrtlist LIKE '%dbid=' + ltrim(str(db_id())) + ' %'
--AND est.text LIKE '%WHERE OrderDate > @orderdate%'
  AND  est.text NOT LIKE '%sys.dm_exec_plan_attributes%'

В нем есть три отличия от запроса для хранимых процедур:

1. Фильтрация по БД отличается, т.к. столбец dbid в sys.dm_exec_sql_text не заполняется для динамического SQL, вместо этого мы должны взять значение из sys.dm_exec_plan_attributes. Чтобы не пришлось вызывать эту вункцию в запросе дважды, фильтрация сделана именно таким образом.
2. так как нет имени хранимой процедуры для сопоставления, мы вынуждены использовать часть текста запроса.
3. Нам нужно дополнительно условие, чтобы исключить из результатов запроса сам запрос при помощи сравнения с sys.dm_exec_plan_attributes.

Когда я запустил этот запрос, я увидел (частично) такой список атрибутов:

date_first=7   date_format=1   dbid=6   objectid=158662399   set_options=251   user_id=5
date_first=7   date_format=1   dbid=6   objectid=158662399   set_options=251   user_id=1

Сначала взгляните на objectid. Как вы видите значение одинаково для обоих записей. Это значение objectid — является значением хэша, которое я описывал выше. Потом взгляните на отличающийся атрибут user_id. Название столбца употреблено неверно, так как значение представляет собой схему по-умолчанию, для пользователей, использующих этот план. Схема dbo всегда имеет schema_id = 1. В моей базе данных Northwind, Schema2 получила schema_id = 5, когда я запустил запрос, но вы можете увидеть другое значение.

Теперь, запустите этот запрос:

EXEC sp_executesql N'SELECT * FROM >>>dbo<<<.Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', '20000101'

И затем запустите запрос по sys.dm_exec_plan_attributes снова.
В результатах появится третья строка:

date_first=7   date_format=1   dbid=6   objectid=549443125   set_options=251   user_id=-2

objectid отличается от тех, что выше, поскольку текст запроса другой. А user_id теперь равно -2. Что это значит? Если вы посмотрите на запрос повнимательнее, вы увидите, что теперь мы указали схему явно когда осуществляем доступ к таблице Orders. Это значит, что теперь запрос не несет неопределенности и этой записью в кэше могут пользовать все пользователи. Это и значит значение -2, оно говорит что: в запросе нет неоднозначных ссылок. Отсюда следует вывод, что очень хорошая практика при написании кода использовать двусоставные имена в динамическом SQL, не важно пишите ли вы динамический SQL в клиентском приложении или в хранимой процедуре.

Вы можете подумать «Мы не используем схемы в наших приложениях, так что это нас не касается», но не торопитесь! Когда вы используете CREATE USER, схема по-умолчанию действительно всегда будет dbo, до тех пор пока вы не укажете что-то другое. Однако, если ваш DBA старой закалки, он может создавать пользователей используя старые процедуры sp_adduser или sp_grantdbaccess, а они работают по-другому. Они создают не только пользователя, но так же создают и схему, с тем же именем и устанавливают ее как схему по-умолчанию для вновь созданного пользователя. Это звучит банально? Да, но до SQL 2000, схема и пользователь были объединены в SQL Server. По этому, пока у вас нет контроля над тем, как создается пользователь, вы не должны полагаться на то, что dbo будет всегда схемой по-умолчанию.

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

Решения проблемы прослушивания параметров в динамическом SQL

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

Эффекты автопараметризации

Можно узнать о том, что предикат был параметризован посмотрев на свойства оператора.
Например в этом запросе:

SELECT * FROM Orders WHERE OrderID = 11000

Предикат поиска:

Seek Keys[1]: Prefix: [Northwind].[dbo].[Orders].OrderID =
Scalar Operator(CONVERT_IMPLICIT(int,[@1],0))

[@1] сообщает нам, о том, что запрос был автоматически параметризован.

Иногда, факт запроса автопараметризации SQL Server-ом может сработать против вас. Например, у вас есть такой запрос:

SELECT ... FROM dbo.Orders WHERE Status = 'Delayed'

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

На самом деле не существует абсолютно гарантированного способа отключить все виды параметризации, но есть некоторые хаки. Если в базе данных установлена простая параметризация, то параметризуются только очень простые запросы, например запросы, только к одной таблице. Полный список условий не включен в Books Online, но один из документов, представленных в секции Ссылки включает его. Согласно этому списку будет достаточно просто добавить условие:

AND 1 = 1

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

Если в базе данных включена принудительная параметризация, есть два варианта. В Books Online, в разделе forced parameterisation приведен список, который описывает, когда принудительная параметризация не будет работать. Согласно списку, одним из решений является использование OPTION (RECOMPILE), что хорошо, до тех пор пока можно мириться с временем компиляции каждый раз. Другим способом является добавить в запрос переменную:

DECLARE @x int
SELECT ... FROM dbo.Orders WHERE Status = 'Delayed' AND @x IS NULL

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

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

Запуск запросов приложения в SSMS

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

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

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

EXECUTE AS USER = 'appuser'
go
-- Run SQL here
go
REVERT

appuser — пользователь базы данных, которого использует приложение — это может быть как закрытый пользователь для самого приложения, так и пользователь созданный для определенного человека. Однако это не будет работать, если запрос осуществляет доступ к ресурсам вне текущей базы данных. В таком случае, нужно использовать EXECUTE AS LOGIN. Заметьте, что для этого нужны разрешения уровня сервера.

Получение точного текста запроса SQL может быть более сложной задачей. Лучше всего для получения запроса использовать трассировку; вы можете запустить ее в профайлере или на стороне сервера. Если SQL запрос не параметризован, будьте внимательны и копируйте в точности весь текст. Не удаляйте никаких начальных или конечных пробелов. Не добавляйте лишних строк для лучшей читаемости, не удаляйте комментарии. Оставьте запрос в точности таким, каким его отправляет на сервер приложение. Вы можете использовать запрос к sys.dm_exec_plan_attributes из этой статьи, чтобы удостовериться, что в кэше не появилась вторая запись для этого запроса.

Другой способ, получить текст при помощи запроса к sys.dm_exec_query_stats и sys.dm_exec_sql_text. Запрос который вы можете использовать:

SELECT '<' + est.text + '>'
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
WHERE  est.text LIKE '%some significant SQL text here%'

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

С параметризованным SQL проще, потому что запрос упакован в строку. Вот что вы увидите в Profiler:

EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', '20000101'

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

EXEC sp_executesql
N'SELECT * FROM Orders WHERE OrderDate > @orderdate',
N'@orderdate datetime', '20000101'

Важно, не менять то, что находится в кавычках, т.к. именно по этому строится хэш.

Если у вас нет разрешений ALTER TRACE, чтобы запустить трассировку, или VIEW SERVER STATE, чтобы сделать запрос к sys.dm_exec_query_stats и sys.dm_exec_sql_text, это уже трудновато. Если динамический SQL находится в процедуре, которую вы можете редактировать, то можно добавить команду PRINT, чтобы вывести текст. (Вообще, хранимые процедуры, которые работают с динамическим SQL, должны иметь параметр @debug и включать в себя строку IF @debug = 1 PRINT @sql.) Вам так же нужно быть внимательным, чтобы извлечь текст в точности и не пропустить или добавить новых пробелов. Если есть список параметров, вам нужно убедиться, что вы его скопировали в точности как он есть. Если SQL создается приложением или хранимой процедурой .Net, то можно получить его текст в отладчике, но получить его в точности, может быть трудной задачей. Лучшим выходом в таком случае, может быть попробовать запустить приложение на том экземпляре сервера, где у вас есть необходимые права, например, на экземпляре вашей рабочей станции.

Руководства планов (plan guides) и фиксация планов (plan freezing)

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

Однако, SQL Server предоставляет решения для таких ситуаций, это руководства планов.
Существует два способа задания руководства планов, общий способ и упрощенный вариант, известный также как фиксация плана*.
(*Прим. переводчика: Я не нашел в документации официального перевода термина «plan freezing», наиболее удачным русским аналогом мне показалось словосочетание «фиксация планов»).

Руководства планов были представлены в SQL 2005, тогда как фиксация плана была добавлена в SQL 2008.

Примечание:
Этот функционал не доступен в базовых версиях SQL Server – Express, Web и Workgroup Edition.

Вот пример создания и установки руководства планов. Этот конкретный пример для SQL 2008 и более поздних версий.

DBCC FREEPROCCACHE
go
EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', @orderdate = '19960101'
go
EXEC sp_create_plan_guide
     @name = N&#039;MyGuide&#039;,
     @stmt = N&#039;SELECT * FROM dbo.Orders WHERE OrderDate &gt; @orderdate&#039;,
     @type = N&#039;SQL&#039;,
     @module_or_batch = NULL,
     @params = N&#039;@orderdate datetime&#039;,
     @hints =  N&#039;OPTION (TABLE HINT (dbo.Orders , INDEX (OrderDate)))&#039;
go
EXEC sp_executesql N&#039;SELECT * FROM dbo.Orders WHERE OrderDate &gt; @orderdate&#039;,
                   N&#039;@orderdate datetime&#039;, @orderdate = &#039;19980101&#039;
go
EXEC sp_control_plan_guide N&#039;DROP&#039;, N&#039;MyGuide&#039;

В этом примере я создаю план, чтобы запрос по полю OrderDate всегда использовал Index Seek (например, я ожидаю, что все запросы будут осуществляться для заказов за последние несколько дней). Я указываю название руководства. Далее указываю точный текст инструкции, к которой будет применен план. Точно так же как и при извлечении запроса для исследования в SSMS, вам нужно убедиться, что вы не потеряли никаких начальных или конечных пробелов или не сделали каких-то других изменений. Параметр @type указывает, что руководство должно быть применено для динамического SQL, а не для хранимой процедуры. Если бы этот SELECT был бы частью большого пакета инструкций, мне нужно было бы указать текст этого пакета в параметре @module_or_batch, снова в точности такой же каким его отправляет серверу приложение. Когда я в @module_or_batch указываю NULL, подразумевается что @stmt — это и есть целиком текст пакета. @params — список параметров инструкции, и снова, текст должен совпадать с текстом что отправляет приложение буква в букву.

И наконец, @hints, место, где и начинается все интересное. В этом примере я указал, что запрос должен всегда использовать индекс по OrderDate, вне зависимости от прослушанного значения @orderdate. Конкретно эта подсказка OPTION (TABLE HINT), не доступна в SQL 2005, вот почему запрос не выполнится для этой версии.

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

В SQL 2008 вы можете указывать параметры в sp_create_plan_guide в любом порядке если вы делаете это указывая имя параметра, а так же не указывать N перед строковыми литералами. Однако SQL 2005 не такой всепрощающий. Параметры должны быть заданы строгом порядке, даже если вы указываете их названия, а строковые литералы, должны начинаться с N.

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

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

DBCC FREEPROCCACHE
SET ARITHABORT ON
go
EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', @orderdate = '19990101'
go
DECLARE @plan_handle varbinary(64),
        @rowc int

SELECT @plan_handle = plan_handle
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
WHERE  est.text LIKE '%Orders WHERE OrderDate%'
  AND  est.text NOT LIKE '%dm_exec_query_stats%'
SELECT @rowc = @@rowcount

IF @rowc = 1
   EXEC sp_create_plan_guide_from_handle 'MyFrozenPlan', @plan_handle
ELSE
  RAISERROR('%d plans found in plan cache. Canno create plan guide', 16, 1, @rowc)
go
-- Test it out!
SET ARITHABORT OFF
go
EXEC sp_executesql N'SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate',
                   N'@orderdate datetime', @orderdate = '19960101'
go
SET ARITHABORT ON
EXEC sp_control_plan_guide 'DROP', 'MyFrozenPlan'

В целях демонстрации, я сначала очистил кэш планов и установил опцию ARITHABORT в известное состояние. Затем, я запустил свой запрос с параметром, который, я знаю, даст хороший план. Следующий пакет инструкций демострирует как использовать sp_create_plan_guide_from_handle. Сначала, я запустил запрос к sys.dm_exec_query_stats и sys.dm_exec_sql_text чтобы найти запись о моем пакете инструкций. Потом я сохранил @@rowcount в локальную переменную (поскольку @@rowcount меняет значений после каждой инструкции, я предпочитаю копировать ее в локальную переменную в SELECT-е, который идет сразу за запросом, чтобы избежать неприятностей). Это предосторожность для безопасности, в случае, если я получу несколько или ни одной записи в кэше. Если я получаю в точности одно совпадение, я вызываю процедуру sp_create_plan_guide_from_handle которая принимает два параметра: название руководства планов и хэндл плана. Вот и все!

Следующая часть тестирует руководство. Чтобы быть уверенным, что я не использую ту же запись из кэша, я использую другое значение ARITHABORT. Если вы запустите демонстрацию со включенным планом, вы увидите, что во втором случае, используется тот же план Index Seek + Key Lookup, что и в первом. Хотя в обычной ситуации, для данного значения параметра был бы использован план с Clustered Index Scan. Это значит, что руководства планов не зависят от настроек SET.

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

Классной штукой является то, что вам не нужно заниматься настройками на рабочем сервере, вы можете проводить эксперименты на тестовом. Руководства хранятся в sys.plan_guides, и как только вы получили нужное руководство, вы можете использовать содержимое DMV, чтобы сконструировать правильный вызов sp_create_plan_guide, который потом сделать на рабочем сервере. Вы также можете сделать скрипт этого объекта в Object Explorer в среде SSMS.

Если ваш пакет или процедура состоит из нескольких инструкций, вы можете не настраивать руководство для всего пакета, а сделать это только для одной инструкции. Для этой цели, sp_create_plan_guide_from_handle принимает третий параметр @statement_start_offset, значение для которого вы также можете получить при помощи запроса к sys.dm_exec_query_stats.

Это был только вводный ознакомительный пример руководств и фиксации планов. Руководства планов, безусловно, очень обширная тема, и чтобы не делать статью еще длиннее, я просто отсылаю вас к разделу plan guides в Books Online, или к документам в разделе Ссылки.

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

Заключительные замечания

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

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

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

Если вы думаете, что наткнулись на что-то чего нет в этой статье, но что должно бы быть в нее включено, пожалуйста, напишите мне на esquel@sommarskog.se. То же самое, если вы заметили какие-то ошибки. Однако, если у вас есть вопросы касательно того как решать вашу конкретную проблему, я рекомендую вам обратиться на форум SQL Server, потому, что вопрос увидят гораздо больше людей. Если вы очень хотите чтобы я посмотрел ваш вопрос, опубликуйте его Microsoft’s Transact-SQL forum, и пришлите мне ссылку (потому что это слишком большой поток, чтобы успеть прочитать все).

Ссылки по теме

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

Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 — Документ, написанный Eric Hanson и Yavor Angelov из команды SQL Server team. Доступна так же версия для SQL 2005.

Plan Caching in SQL Server 2008 – Документ написанный SQL Server MVP Greg Low. Appendix A подробно описывает правила простой параметризации. Есть более старая версия документа для SQL 2005, написанная Arum Marathe.

Troubleshooting Performance Problems in SQL Server 2008 – Обширный документ, рассматривающий производительность с разных точек зрения, а не только с точки зрения настройки запросов. Написан командой разработчиков и CSS из Microsoft. Так же доступна версия для SQL 2005.

Forcing Query Plans – документ по руководствам планов. Версия для SQL 2005; Я еще не видел версии для SQL 2008.

Last Translation Revision Date: 2013-04-14;

 

Один ответ

  1. Golick

    Огромное спасибо за статью/перевод.
    И вообще за блог, особо за то, что на русском и простым понятным языком.
    Пару раз уже начинал читать оригинальную статью, но так и не продвинулся дальше 10 абзацев…

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

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

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