Автор: somewheresomehow
Уровень доклада: от 1 года работы
Как SQL Server компилирует динамический SQL
Оставим тему прослушивания параметров, и вернемся обратно к теме данной статьи: почему запрос может долго выполняться из приложения, но быстро из SQL Server Management Studio. До настоящего времени, мы посмотрели только на хранимые процедуры, и для них, наиболее вероятная причина такого поведения разные настройки SET ARITHABORT. Если у вас есть приложение, которое не использует хранимые процедуры, а генерирует запросы на клиенте, или на каком-либо промежуточном слое, есть еще несколько причин, почему вы можете получить новую запись в кэше и соответственно возможно новый план, выполняя тот же самый запрос из SSMS.
Что такое динамический 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
SQL Server компилирует динамический SQL очень похожим способом, как и хранимые процедуры. По этому, если пакет инструкций состоит более чем из одного запроса, он компилируется как единое целое, и SQL Server понятия не имеет о значениях локальных переменных внутри пакета. Так же как и для хранимых процедур SQL Server прослушивает значения параметров, и использует их при генерации плана.
Тем не менее, есть один начальный шаг в компиляции, который уникален только для динамического SQL, а именно параметризация. Она заключается в том, что SQL Server, может заменить константу в запросе — параметром. Например, если у вас есть:
SELECT * FROM Orders WHERE OrderID = 11000 |
SQL Server скомпилирует это так, как если бы вы послали:
EXEC sp_executesql N 'SELECT * FROM Orders WHERE OrderID = @p1' , N '@p1 int' , @p1 = 11000 |
Есть две модели параметризации, простая и принудительная. С простой параметризацией, SQL Server параметризует довольно узкий класс простых запросов. С принудительной параметризацией, SQL Server заменяет почти все константы параметрами, с некоторыми исключениями, о чем подробно сказано в этой теме в Books Online. По умолчанию установлен режим простой параметризации, и вы можете установить свой режим для каждой базы данных при помощи ALTER DATABASE.
Принудительная параметризация может дать большую экономию в производительности, для приложения, которое не использует выражения с параметрами, но нет особого смысла использовать ее в хорошо написанных приложениях.
Динамический SQL и кэш планов выполнения
Планы выполнения динамических запросов, помещаются в кэш, точно так же как и планы для хранимых процедур. (Если кто-то говорит вам другое, этот человек либо заблуждается, либо основывается на очень старой информации. До SQL Server 6.5, SQL Server не помещал в кэш планы для динамического SQL.) Как и в случае хранимых процедур, планы для динамического SQL могут быть вытеснены из кэша по каким-либо причинам, а отдельные инструкции могут перекомпилироваться. Более того, может быть более чем одна запись в кэше для одного и того же запроса из-за разных настроек SET.
Однако, есть две сложности с динамическим SQL, которых в хранимых процедурах.
Текст запроса как хэш ключ
Когда 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' |
Разница только в концевом пробеле.
Значение схемы по-умолчанию
Другое отличие от хранимых процедур не такое очевидное, и его лучше показать на примере. Выполните это, и посмотрите на планы выполнения:
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 |
Первые два выполнения используют Index Seek + Key Lookup, тогда как третье Clustered Index Scan, несмотря на то, что запрос и параметры были идентичны второму запросу. Что же происходит?
Сначала немного слов об установке условий эксперимента. Скрипт создает двух пользователей в базе данных и дает им разрешение на выполнение запросов. Мы запускаем запрос три раза. Первый раз от самого себя (предполагая, что мы dbo), затем от имени двух созданных пользователей. (Если вы не знакомы с имперсонализацией, посмотрите тему EXECUTE AS в Books Online; я так же рассказываю об этом в своей статье Granting Permissions through Stored Procedures.) Пользователи созданы без логинов, но это потому, что нам просто не нужны логины в этом примере. Что важно, так это то, что пользователи имеют разные схемы по-умолчанию. У User1 схема по-умолчанию dbo, а у User2 нет. Почему это имеет значение?
Помните, что когда 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 est.text LIKE '%WHERE OrderDate > @orderdate%' AND est.text NOT LIKE '%sys.dm_exec_plan_attributes%' |
В нем есть три отличия от запроса для хранимых процедур:
1. Нет условия по db_id(), так как эта колонка заполняется только для sql_handle хранимых процедур.
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.
Запуск запросов приложения в 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, то можно получить его текст в отладчике, но получить его в точности, может быть трудной задачей. Лучшим выходом в таком случае, может быть попробовать запустить приложение на том экземпляре сервера, где у вас есть необходимые права, например, на экземпляре вашей рабочей станции.
Решения проблемы прослушивания параметров в динамическом 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, вы обнаружите, что есть подсказка позволяющая контролировать параметризацию, но она разрешена только в руководствах планов, что является темой следующего раздела.
Руководства планов (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'MyGuide', @stmt = N' SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate', @type = N'SQL', @module_or_batch = NULL , @params = N'@orderdate datetime ', @hints = N' OPTION ( TABLE HINT (dbo.Orders , INDEX (OrderDate)))' go EXEC sp_executesql N' SELECT * FROM dbo.Orders WHERE OrderDate > @orderdate', N'@orderdate datetime ', @orderdate = '19980101' go EXEC sp_control_plan_guide N' DROP ', N'MyGuide' |
В этом примере я создаю план, чтобы запрос по полю 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.