SQL-Ex blog

Просмотр кэша

Одним из способов посмотреть, что находится к кеше планов запросов SQL Server - это обратиться к DMV sys.db_exec_query_stats. Соединив функции sys.dm_exec_sql_text и sys.dm_exec_query_plan, мы можем узнать, что находится в кеше, и как часто его содержимое повторно используется:

SELECT

	s.sql_handle,

	s.plan_generation_num,

	s.plan_handle,

	s.execution_count,

	s.query_hash,

	s.query_plan_hash,

	t.text,

	p.query_plan

FROM 

	sys.dm_exec_query_stats s

	CROSS APPLY sys.dm_exec_sql_text(sql_handle) t

	CROSS APPLY sys.dm_exec_query_plan(plan_handle) p

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

Примеры того, что препятствует повторному использованию плана

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

Пробел

Если мы выполним эти два запроса:

SELECT TOP 100 DisplayName FROM dbo.Users;

GO

SELECT TOP 100 DisplayName FROM     dbo.Users;

GO

и затем проверим наш кеш планов запросов:


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

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

Регистр букв

Различие в регистре букв также мешает повторному использованию кешированного плана:

SELECT TOP 100 DisplayName FROM dbo.Users;

GO

select TOP 100 DisplayName from dbo.Users;

GO

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

Комментарии

Хотите улучшить читаемость вашей бизнес-логики с помощью комментариев? Отлично! Но имейте в виду, что при выполнении SQL Server будет считать запросы с комментариями и без различными:

SELECT TOP 100 DisplayName FROM dbo.Users;

GO

/* Этот запрос возвращает имена всех пользователей */

SELECT TOP 100 DisplayName FROM dbo.Users;

GO

Имена схем

Выполняем следующие два запроса:

SELECT TOP 100 DisplayName FROM dbo.Users;

GO

SELECT TOP 100 DisplayName FROM Users;

GO

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

Опции SET

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

SET ANSI_PADDING ON;

GO

SELECT TOP 100 DisplayName FROM dbo.Users;

GO

SET ANSI_PADDING OFF;

GO

SELECT TOP 100 DisplayName FROM dbo.Users;

GO

Типы данных простой параметризации

Если запрос достаточно прост для того, чтобы SQL Server сам его параметризовал, мы увидим увеличение числа использования параметризованного запроса, т.е. SQL Server может повторно использовать план:

SELECT DisplayName FROM dbo.Users WHERE Id = 3;

GO

SELECT DisplayName FROM dbo.Users WHERE Id = 4;

GO

Однако обратите внимание, что SQL Server определил тип параметризованного значения как tinyint. Если наш запрос вдруг передаст значение, превышающее 255:

SELECT DisplayName FROM dbo.Users WHERE Id = 256;

GO

то мы увидим новый план, сгенерированный и сохраненный в кеше.

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

SELECT DisplayName FROM dbo.Users WHERE Id = 3;

GO

SELECT DisplayName FROM dbo.Users WHERE Id =         3;

GO

SELECT DisplayName FROM dbo.Users WHERE             Id = 3;

GO

SELECT DisplayName /* comment */ FROM dbo.Users WHERE             Id = 3;

GO

Будьте последовательны

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