SQL-Ex blog

CREATE OR ALTER PROC dbo.usp_SearchUsers @Reputation INT AS

	BEGIN

	/* Запрос 1, всегда один и тот же: */

	SELECT COUNT(*) FROM dbo.Users;

	/* Запрос 2, перекомпилируется и получает разные планы: */

	SELECT TOP 10000 * 

	  FROM dbo.Users 

	  WHERE Reputation = @Reputation 

	  ORDER BY DisplayName OPTION (RECOMPILE);

	END

GO

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

Я собираюсь сделать некоторые настройки, очистить кэш планов и затем выполнить процедуру:

USE StackOverflow

GO

DropIndexes;

GO

CREATE INDEX IX_Reputation ON dbo.Users(Reputation);

GO

DBCC FREEPROCCACHE;

GO

EXEC usp_SearchUsers @Reputation = 2

Когда я выполняю её с @Reputation = 2, второй запрос возвращает 9149 строк.

Как перекомпилированный запрос отображается в кэше планов

Если я прямо сейчас проверю sys.dm_exec_query_stats, то несколько столбцов будут содержать интересные данные - я собираюсь передвинуть их немного, чтобы вам было видно:



Строка 1 - это первый запрос в хранимой процедуре. Тут COUNT(*), и поэтому возвращается только 1 строка.
Строка 2 - второй запрос, и он возвращает 9149 строк для Reputation=2.

Так что сейчас, если я займусь математикой, то смогу сложить общее число строк двух операторов и увидеть общее число строк, возвращенных запросом. Может показаться странным использовать сейчас строки как меру, но столбцы sys.dm_exec_query_stats - CPU, reads (чтения), writes (записи), duration (продолжительность) и т.д. - все ведут себя так же, как я собираюсь показать вам здесь, а число строк более повторяемо, чем некоторые другие, поэтому давайте использовать строки.

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

DBCC FREEPROCCACHE;

GO

EXEC usp_SearchUsers @Reputation = 2

GO

EXEC usp_SearchUsers @Reputation = 1

GO

SELECT * FROM sys.dm_exec_query_stats;

Результат:



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

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

Это я знал, но забыл, что когда я смотрю на итоговые показатели хранимой процедуры в sp_BlitzCache, то значения total, min и max - бесполезны:



В sp_BlitzCache мы складываем итоги для каждого оператора в процедуре, и представляем их как общие числа процедуры. Проблема в том, что они просто не соответствуют действительности, когда есть хинт перекомпиляции: итоги занижены, а avg/min/max просто отражают последнее выполнение каждого запроса с хинтом перекомпиляции.

Кэш планов не очень подходит для отслеживания запросов с хинтами RECOMPILE, для этого годится Query Store.

Как перекомпилируемый запрос отображается в Query Store

Я собираюсь включить Query Store в базе данных Stack Overflow, а затем снова выполнить два запроса:

ALTER DATABASE CURRENT SET QUERY_STORE = ON;

GO

ALTER DATABASE CURRENT SET QUERY_STORE 

  (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);

GO

EXEC usp_SearchUsers @Reputation = 2

GO

EXEC usp_SearchUsers @Reputation = 1

GO

Теперь запросим Query Store:



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

Когда вы решаете использовать хинты RECOMPILE, то вероятно хотите включить Query Store.

Query Store первоначально появился в SQL Server 2016. Сейчас, в 2020, когда ваши усилия по настройке запросов требуют добавления хинтов RECOMPILE, вам следует притормозить на минуточку, чтобы рассмотреть также Query Store. Это упростит устранение проблем, связанных с производительностью.

Хотя Query Store потребует немного большего планирования, чем простое добавление хинта RECOMPILE. Вот о чем нужно будет подумать:

• Посмотреть курс Ерин Стеллато по Query Store - чтобы научиться применять его (команды на пару строк в моем посте недостаточно) и писать к нему запросы.


• Прочитать её пост Query Store Best Practices.


• Устанавливать последние патчи SQL Server - для устранения постоянно возникающих багов Query Store, например, проблем с файлом журнала и блокировками.

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