SQL-Ex blog

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

Мы будем исходить из этого вводного поста в блоге, но теперь переведем базу данных в режим совместимости с SQL Server 2022, который делает возможным PSPO.

USE StackOverflow;

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160; /* 2022 */

GO

/* Включаем фактические планы выполнения и: */

SET STATISTICS IO, TIME ON;

EXEC DropIndexes;

GO

CREATE INDEX Reputation ON dbo.Users(Reputation)

GO

CREATE OR ALTER PROCEDURE dbo.usp_UsersByReputation

  @Reputation int

AS

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation=@Reputation

ORDER BY DisplayName;

GO

Когда я выполняю код для @Reputation = 2, чему вряд ли кто-то из пользователей соответствует:

EXEC dbo.usp_UsersByReputation @Reputation =2;

Фактический план выполнения имеет несколько новых элементов в SQL Server 2022:



Сверху скриншота, если посмотреть внимательно, можно заметить, что сам запрос был модифицирован. SQL Server добавил хинт “option”. Вот полный текст модифицированного запроса:

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation=@Reputation

ORDER BY DisplayName 

option (PLAN PER VALUE(ObjectID = 1557580587, 

QueryVariantID = 2, 

predicate_range([StackOverflow].[dbo].[Users].[Reputation] = @Reputation, 

100.0, 1000000.0)))

Давайте вникнем, как это работает.

Что означает опция plan per value (план по значению)

Когда SQL Server скомпилировал план запроса впервые, он заметил, что мы делали поиск по равенству в столбце Users.Reputation, и что различные параметры этого значения могут породить существенно разное число строк. Это объясняет часть “predicate_range”.

SQL Server понял:

• Некоторые параметры могут дать менее 100 строк


• Некоторые могут дать от 100 до 1000000 строк


• Некоторые могут дать свыше 1000000 строк

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

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

DBCC FREEPROCCACHE

GO

EXEC dbo.usp_UsersByReputation @Reputation =2;

GO

sp_BlitzCache

sp_BlitzCache показывает, что внешняя хранимая процедура была выполнена один раз, и внутренний оператор выполнился единожды:



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

Давайте выполним версию с большими данными, Reputation = 1. Будет произведена тонна строк, поскольку каждый получает 1 балл, когда впервые открывает свой аккаунт:

EXEC dbo.usp_UsersByReputation @Reputation =1;

Фактический план является параллельным сканированием таблицы:



И SQL Server переписывает текст запроса, чтобы включить другой QueryVariantID:

SELECT TOP 10000 *

FROM dbo.Users

WHERE Reputation=@Reputation

ORDER BY DisplayName 

option (PLAN PER VALUE(ObjectID = 1557580587, 

QueryVariantID = 3, 

predicate_range([StackOverflow].[dbo].[Users].[Reputation] = @Reputation, 

100.0, 1000000.0)))

И кэш планов показывает, что внешняя процедура была выполнена дважды с выполнением каждого, среднего и большого запроса:



Если вы только читаете это и выполняете только краткие подобные этому демонстрационные примеры, вам может показаться, что Microsoft выполнил огромный прыжок в решении проблемы прослушивания параметра. PSPO позволяет нам кэшировать до 3-х планов выполнения на запрос - небольшой, средний и большой план - и выбирать между ними во время выполнения. Так это будет выглядеть со сцены конференции, когда Microsoft хвастается эффективностью этой функции.

Но давайте копнем немного глубже, и тут обнаружатся небольшая, средняя и большая проблемы.

Небольшая проблема: вы все еще имеем прослушивание.

Попробуйте выполнить это, скажем, для Reputation = 3 и посмотрите фактический план выполнения запроса:



Reputation = 3 повторно использует план запроса, который мы построили для Reputation = 2 - вернитесь к предыдущим скриншотам, если вы хотите перепроверить мою работу. Обратите внимание, что SQL Server оценил, что нашлось бы только 9149 строк - это потому, что средний план прослушал первое значение, с которым он вызывался, Reputation = 2.

Reputation = 3 возвращает в 21 раз больше строк, чем Reputation = 2, поэтому он читает больше логических страниц, чем имеется в таблице, только в однопоточном режиме, и сортировку переносит в TempDB.

Если очистить кэш планов, а затем выполнить запрос сначала для Reputation = 3:



Тогда "средний" план строится с параллельным сканированием при большой заявке на память, что прекрасно работает для Reputation = 3. Однако не работает так же хорошо для Reputation = 2:



Который оставляет это гигантское выделение памяти и генерирует тонну ожиданий CX%, поскольку оценка теперь в 21 раз меньше в другую сторону.

Реализация PSPO в SQL Server 2022 не решает проблему прослушивания параметра - она усугубляет проблему, поскольку теперь мы имеем больше возможных планов выполнения в памяти, каждый из которых дает прослушивание. Я думаю, что в большинстве сценариев это по-прежнему будет чистым выигрышем, потому что малые и большие планы, вероятно, будут менее подвержены чрезвычайным ситуациям с производительностью. Средние планы по-прежнему будут такими же уязвимыми.

Средняя проблема: поиски только по прямому равенству

Реализация PSPO похоже больше на предварительный взнос, чем на полную оплату. Она работает только для поиска на равенство, но не вхождение в диапазон. Одной из наиболее частых проблем прослушивания параметра является проблема с диапазоном дат: запросы с параметрами начальной и конечной даты, например:

CREATE INDEX LastAccessDate ON dbo.Users(LastAccessDate);

GO

CREATE OR ALTER PROC dbo.usp_UsersByLastAccessDate

	@StartDate DATETIME, @EndDate DATETIME AS

SELECT TOP 10000 *

FROM dbo.Users

WHERE LastAccessDate BETWEEN @StartDate AND @EndDate

ORDER BY DisplayName;

GO

EXEC usp_UsersByLastAccessDate '2018-01-01', '2018-01-02'

PSPO не добавляет здесь к запросу хинт option:



Поскольку код PSPO работает только с поиском на точное равенство.

Под точным я подразумеваю непосредственное сравнение со столбцом с известными огромным разбросом в количестве элементов. Для примера поиска на неравенство давайте возьмем таблицу, которая имеет справочную таблицу: таблицы Posts и PostTypes. Stack Overflow хранит все в таблице Posts, что идентифицируется по их типу (PostTypeId):

CREATE INDEX PostTypeId ON dbo.Posts(PostTypeId);

GO

SELECT pt.Type, pt.Id, SUM(1) AS Posts

FROM dbo.PostTypes pt

INNER JOIN dbo.Posts p ON pt.Id = p.PostTypeId

GROUP BY pt.Type, pt.Id

ORDER BY 3 DESC;

Вопросы (Questions) и ответы (Answers) являются значительно более популярными типами постов:



При том, что я получил индекс на PostTypeId, который означает, что мы имеем также статистику на PostTypeId, этот запрос мог бы получить различные планы для различных параметров:

CREATE OR ALTER PROC dbo.usp_PostsByPostType

	@PostTypeName NVARCHAR(50) AS

SELECT TOP 10000 p.*

FROM dbo.PostTypes pt

INNER JOIN dbo.Posts p ON pt.Id = p.PostTypeId

WHERE pt.Type = @PostTypeName

ORDER BY p.Score DESC;

GO

EXEC usp_PostsByPostType N'PrivilegeWiki' /* Only 2 matching posts */

Печально, но нет, поскольку PSPO так и не сработал здесь - обратите внимание, что PSPO не добавил хинт option в запрос:



Заметим жуууутко некорректные оценки числа постов, которые отвечают запросу. SQL Server использует здесь вектор плотности, оптимизируя среднее значение PostTypeId, а не какое-либо конкретное. PSPO не зайдет так далеко, чтобы:

1. Взять PostTypeId для ‘PrivilegeWiki’, затем


2. Найти PostTypeId в статистике для таблицы Posts, затем


3. Заметить, что имеется большой перекос по PostTypeId, и построить различные планы

Нет - это слишком много работы для реализации PSPO, по крайней мере, в версии 2022.

Большая проблема: ПО для мониторинга обречено.

Если посмотреть внимательно на скриншоты sp_BlitzCache, то имеется огромная, невероятно большая новая проблема:



В столбце “Query Type” для двух сгенерированных PSPO запросов говорится просто “Statement” (оператор). Когда вы посмотрите на запросы и планы - в кэше планов или sp_WhoIsActive, или в ваших инструментах мониторинга, или как в демонстрации Эрика Дарлинга хранилища запросов - SQL Server не может больше сказать, откуда пришел запрос. Это похоже на то, что каждый выполняющийся оператор внезапно становится динамическим SQL без родителя.

Например, если вы посмотрите план запроса для хранимой процедуры, которая была "оптимизирована" с помощью PSPO, то получите:



Если вы думаете, что это плохо, поскольку нет способа соединиться с соответствующими операторами, то это не просто плохо, это очень плохо. Реально, очень плохо.

Переключение в уровень совместимости SQL Server 2022, просто разрушает мониторинг производительности запросов.

Когда вышли первые предварительные версии 2022 года, я поиграл с этой функцией и подумал: «Они точно не могут выпустить что-то настолько сломанное». Я просто пожал плечами и пошел дальше. Но сейчас, сегодня, мне страшно подумать, что именно так эта функция и будет выпущена.

Это PSPO.

Произносится pss-poh, как в piss-poor.

Мне нравится идея оптимизация чувствительного к параметру плана. Это великолепная идея. Выполненная корректно, она бы сделала более быстрыми приложения для работы с базами данных и сократила время на устранение неполадок, связанных с проблемами производительности.

Но это, это просто реализация PSPO.