Как отследить производительность запросов, которые используют хинты RECOMPILE
Пересказ статьи Brent Ozar. How to Track Performance of Queries That Use RECOMPILE Hints
Пусть у нас есть хранимая процедура, которая содержит два запроса - второй запрос использует хинт RECOMPILE (тут есть статья по теме):
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 будет влиять на производительность вашего сервера. Вам не следует трогать эти переключатели, если вы не уверены, что это лучший способ решить проблему с прослушиванием параметров.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой