SQL-Ex blog

Предложение TOP часто используется в запросах SQL для ограничения числа возвращаемых строк. Я объясню, как мы можем использовать технику индексирования в сочетании с предложением ORDER BY для решения проблемы прослушивания параметра.

Что такое Parameter Sniffing?

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

Подготовка теста

Я буду использовать базу данных StackOverflow и создам индекс на столбце Location в таблице Users. База данных StackOverflow свободно распространяется на StackOverflow.com.

CREATE INDEX IX_Location ON dbo.Users (Location)

WITH (DATA_COMPRESSION = PAGE)

GO

Для изменения уровня совместимости на SQL Server 2019 (150) используйте следующую команду:

Use master

GO

Alter Database StackOverflow Set Compatibility_Level = 150

GO

Для получения статистики ввода/вывода, используйте команду ниже:

SET STATISTICS IO ON

GO

Я запрошу top 100 пользователей, проживающих в Индии.

Use StackOverflow

GO

Select Top 100 * From dbo.Users Where [Location] = N'India'

GO

Посмотрите на фактический план запроса:



SQL Server использует сканирование кластеризованного индекса для получения результата. Время выполнения запроса несколько миллисекунд. Замечание: в запросе нет предложения ORDER BY.

Число логических чтений 1113, что видно на рисунке ниже:



Следующий запрос запрашивает top 100 пользователей, проживающих в Bergkamen, Германия:

Select Top 100 * From dbo.Users Where [Location] = N'Bergkamen, Germany'

GO

SQL Server получает результаты, используя индекс в некластеризованном индексе с последующим поиском ключа, что видно на рисунке ниже:



SQL Server прочитал только 15 страниц для получения результата, т.к. всего четыре записи имеют местоположение 'Bergkamen, Germany' в таблице Users:



Для демонстрации проблемы прослушивания параметра я скопирую запрос выше и помещу его внутри хранимой процедуры:

Create Or Alter Procedure dbo.USP_FindPeopleByLocation

(@Location Nvarchar(100))

AS

Select Top 100 * From dbo.Users Where Location = @Location

GO

После создания хранимой процедуры при первом ее исполнении я передам значение параметра 'India'.

Exec USP_FindPeopleByLocation N'India'

GO

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



Выполнено 1113 логических чтений, что показано в выводе:



Что произойдет, если мы передадим в качестве параметра в хранимую процедуру значение 'Bergkamen.Germany'? Посмотрите на следующий рисунок :



SQL Server применил операцию сканирования кластеризованного индекса для получения лишь 4 записей. Оператор TOP указывает, что время выполнения запроса выросло свыше 3 секунд (3,342с).

Посмотрите на число логических чтений:



SQL Server прочитал свыше 141 тысячи страниц для вывода четырех записей. Это и есть проблема прослушивания параметра.

Какие имеются варианты решения проблемы?

Во-первых, мы можем добавить предложение 'OPTION(RECOMPILE)' в хранимую процедуру. Однако частое выполнение хранимой процедуры в приложении может привести к давлению на процессор. Поэтому я бы предпочел включить предложение ORDER BY в хранимую процедуру. Например, если пользователь хочет увидеть результаты отсортированные по столбцу 'Reputation' в порядке убывания, нам потребуется изменить хранимую процедуру таким образом:

Create Or Alter Procedure dbo.USP_FindPeopleByLocation

(@Location Nvarchar(100))

AS

Select Top 100 * from dbo.Users Where Location = @Location

Order by Reputation Desc

GO

После добавления предложения ORDER BY в хранимую процедуру я собираюсь изменить некластеризованный индекс на таблице Users:

Drop Index IX_Location On dbo.Users

GO

Create Index IX_Location_Reputation On dbo.Users (Location, Reputation)

With (Data_Compression = Page)

GO

Выполню наш тест еще раз. Сначала передам в хранимую процедуру параметр 'India':

Exec USP_FindPeopleByLocation N'India'

GO

Как можно увидеть, SQL Server использовал поиск в некластеризованном индексе с последующим поиском ключа. Теперь я передам в процедуру 'Bergkamen, Germany':

Exec USP_FindPeopleByLocation N'Bergkamen, Germany'

GO

Как ожидалось, SQL Server применил операцию поиска в некластеризованном индексе с последующим поиском ключа, что в результате дало время выполнения ноль секунд.

Посмотрите число логических чтений - оно снизилось до всего 20 страниц. Это изумительно!



Индексирование - один из лучших способов решения проблемы Parameter Sniffing. Однако есть некоторые недостатки. Например, мы не можем использовать технику покрывающего индекса, когда запрос возвращает большое число столбцов.

Заключение

Проблема прослушивания параметра (Parameter Sniffing) возникает, когда имеется значительная неравномерность распределения данных в таблицах, или когда пользователи приводят к очень неравномерным значениям параметров. Имеется несколько подходов к решению проблемы прослушивания параметра. Индексирование является одним из лучших решений, особенно когда оператор SELECT включает предложение TOP или когда запрос возвращает небольшое число столбцов, которые можно покрыть индексом. Мы не можем всегда использовать предложение 'OPTION(RECOMPILE)', т.к. при частом выполнении процедуры может возникнуть высокая нагрузка на процессор.

Ссылки по теме

1. 5 причин, которые следует рассмотреть при падении производительности запроса
2. Почему Parameter Sniffing не всегда плохо (хотя обычно так и есть)
3. PSPO: Как SQL Server 2022 пытается решить проблему прослушивания параметра
4. Значение уровня совместимости базы данных в SQL Server
5. Как отследить производительность запросов, которые используют хинты RECOMPILE
6. Перекомпиляция запроса SQL и её влияние на производительность
7. Покрывающие индексы SQL Server с ключевыми и неключевыми столбцами для повышения производительности