15.9.25

Оптимизация чувствительных к параметрам планов исполнения в SQL Server 2022

Автор: Deepam Ghosh, Parameter Sensitive Plan Optimization in SQL Server 2022

SQL Server 2022 включает множество усовершенствований и новых возможностей по сравнению с предыдущими версиями. Среди них новые роли сервера, улучшенный Query Store, повышение производительности TempDB, интеллектуальная обработка запросов, автономные группы доступности, Database Ledger и многое другое.

В сегодняшней статье мы рассмотрим практическую демонстрацию одной из таких возможностей — оптимизации планов, чувствительных к параметрам (Parameter Sensitive Plan Optimization, PSPO). Мы увидим, какие трудности создают параметризованные хранимые процедуры в старых версиях и как оптимизация PSPO решает эти проблемы и улучшает планы выполнения запросов в новой версии.

Эта статья посвящена только практической части, для изучения подробной теории об оптимизации планов, чувствительных к параметрам, обратитесь к официальной документации Microsoft.

Проблемы параметрического зондирования в старых версиях

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

Демонстрация с искусственно смещёнными данными

Рассмотрим пример, в котором поэкспериментируем с параметрами и понаблюдаем за поведением SQL Server.

Примечание: В этой статье для демонстрации используется SQL Server 2022 Enterprise Edition.

Скрипт ниже создаёт таблицу Users, содержащую сведения о регистрации постояльцев в отелях по четырём разным городам Индии. В неё вставляется 1 миллион записей, которые специально добавляются с неравномерным распределение по количеству.

CREATE TABLE dbo.Users(
    ID INT,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    City NVARCHAR(100),
    HotelName NVARCHAR(100),
    CheckInDate DATE
);
GO

;WITH Numbers AS (
    SELECT TOP (1000000) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowID
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.Users(ID, FirstName, LastName, City, HotelName, CheckInDate)
SELECT 
    RowID,
    'Rahul',
    CASE WHEN RowID % 2 = 1 THEN 'Sharma' ELSE 'Pandey' END,
    CASE 
        WHEN RowID % 10 = 1 THEN 'Udaipur'
        WHEN RowID % 1000 = 5 THEN 'Jaipur'
        WHEN RowID % 1000000 = 3 THEN 'New Delhi'
        ELSE 'Agra'
    END,
    CASE WHEN RowID % 2 = 1 THEN 'Oberoi' ELSE 'Taj' END,
    DATEADD(DAY, -1 * (RowID % 100), CAST(GETDATE() AS DATE))
FROM Numbers;
GO

Теперь создадим некластерный индекс по столбцу City:

CREATE INDEX IX_City
ON dbo.Users(City)
GO

После добавления индекса выполним запрос для анализа распределения данных по городам:

SELECT City AS TravelDestination, COUNT(*) AS NoOfVisitors
FROM dbo.Users 
GROUP BY City
ORDER BY NoOfVisitors
GO

Мы видим существенную разницу: для «New Delhi» только 1 запись, а для «Agra» — 898999 записей.

Создание параметризованной хранимой процедуры

Теперь создадим параметризованную хранимую процедуру, которая принимает город в качестве входного параметра и возвращает 1000 первых записей таблицы Users для данного города.

CREATE OR ALTER PROCEDURE dbo.UserDestination
  @City NVARCHAR(100)
AS
BEGIN
  SELECT TOP 1000 * FROM dbo.Users WHERE City = @City
  ORDER BY ID
END
GO

Очистим кэш планов, включим сбор статистики и установим уровень совместимости базы данных равным 130 (SQL 2016), чтобы увидеть поведение в старой версии:

DBCC FREEPROCCACHE
GO

SET STATISTICS IO, TIME ON
GO

ALTER DATABASE DBTest SET COMPATIBILITY_LEVEL = 130  -- SQL 2016
GO

Выполним хранимую процедуру с разными параметрами:

USE DBTest

EXEC dbo.UserDestination 'New Delhi';  -- 1 Row(s)
EXEC dbo.UserDestination 'Jaipur';     -- 1000 Row(s)
EXEC dbo.UserDestination 'Udaipur';    -- 100000 Row(s)
EXEC dbo.UserDestination 'Agra';       -- 898999 Row(s)
GO



Изображение 1. На плане выполнения видно, что для всех параметров используется один и тот же план: Index Seek с поиском по Heap, затем соединение и сортировка.

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

DBCC FREEPROCCACHE
GO

USE DBTest

EXEC dbo.UserDestination 'Agra';       -- 898999 Row(s)
EXEC dbo.UserDestination 'New Delhi';  -- 1 Row(s)
EXEC dbo.UserDestination 'Jaipur';     -- 1000 Row(s)
EXEC dbo.UserDestination 'Udaipur';    -- 100000 Row(s)
GO



Изображение 2. В этот раз все параметры используют другой общий план: полное сканирование таблицы, сортировка, параллелизм и выборка первых 1000 строк.

Как Parameter Sniffing влияет на планы

В первом случае, когда процедура запускалась с параметром «New Delhi», SQL использовал его для генерации плана. Так как там всего 1 запись, план оказался оптимальным (Index Seek и поиск строк). Но этот же план применился и для других параметров, включая «Agra» с почти 900 тыс. строк, что крайне неэффективно.

Во втором случае, при первом запуске с «Agra», SQL создал оптимальный план для большого количества строк — сканирование таблицы. Но затем тот же план применился и к параметру «New Delhi», где достаточно было бы быстрого поиска, и опять получилась неэффективность.

Оптимизация PSPO в SQL Server 2022

Теперь посмотрим, как PSPO решает эту проблему в SQL Server 2022.

DBCC FREEPROCCACHE
GO

ALTER DATABASE DBTest SET COMPATIBILITY_LEVEL = 160  -- SQL 2022
GO

USE DBTest

EXEC dbo.UserDestination 'New Delhi';  -- 1 Row(s)
EXEC dbo.UserDestination 'Jaipur';     -- 1000 Row(s)
EXEC dbo.UserDestination 'Udaipur';    -- 100000 Row(s)
EXEC dbo.UserDestination 'Agra';       -- 898999 Row(s)
GO

Теперь SQL Server не использует первый параметр для создания единственного плана. Вместо этого он создаёт несколько планов, ориентированных на разные диапазоны количества строк. Для «New Delhi» (1 строка) оптимальным стал Index Seek с поиском по Heap, соединением и сортировкой. Для «Agra» (898999 строк) оптимальным оказался план сканирования таблицы, сортировки, параллелизма и выборки 1000 строк. В обоих случаях использован наилучший план.

SQL Server теперь кэширует три варианта планов (низкий, средний и высокий) в зависимости от диапазонов кардинальности. При этом в запрос добавляется внутренний хинт OPTION(PLAN PER VALUE(...)), содержащий идентификатор варианта QueryVariantID и диапазон предиката (predicate_range), указывающий диапазон количества строк, для которого эффективен данный план.

Включение и отключение PSPO

По умолчанию PSPO включена в SQL Server 2022. При необходимости её можно отключить для конкретной базы данных:

ALTER DATABASE [DBTest]
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Чтобы включить её снова:

ALTER DATABASE [DBTest]
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

Ограничения PSPO

  • Кэширование и поддержка нескольких планов выполнения для одного параметризованного запроса увеличивает нагрузку на память, процессор и размер кэша планов. Это может быть критично для систем с ограниченными ресурсами.
  • Если данные не имеют выраженной неоднородности по диапазонам, PSPO не работает. Она уменьшает проблему Parameter Sniffing, но не является универсальным решением. При трёх вариантах планов возможны новые случаи неоптимального выбора. Однако в большинстве ситуаций PSPO значительно повышает производительность.

Заключение

В итоге, PSPO — ценная функция SQL Server 2022, которая существенно улучшает производительность запросов. Она в значительной мере уменьшает проблему Parameter Sniffing. Но важно понимать, что данная возможность не всегда подходит для всех запросов. Внимательный анализ и отключение функции там, где она не нужна, помогут обеспечить оптимальную производительность SQL Server.

14.9.25

Новое в SQL Server 2025: Функция REGEXP_REPLACE

Автор: Louis Davidson, REGEXP_ Functions in SQL Server 2025 – REGEXP_REPLACE

Мы уже рассмотрели столько способов фильтрации с помощью регулярных выражений, сколько, на мой взгляд, реализовано в SQL Server 2025. Теперь пришло время сосредоточиться на функциях, которые не являются REGEXP_LIKE. Мы уже говорили о REGEXP_MATCHES, которая пригодится в дальнейшем.

Я начну с REGEXP_REPLACE, которая похожа на обычную функцию SQL REPLACE. Но вместо замены на основе статического разделителя, она может заменять несколько (или конкретное) значение, совпадающее с регулярным выражением. Все мои примеры в этой статье будут использовать просто переменную со значением, над которым мы работаем, так что создавать или загружать объекты не нужно.

13.9.25

Новое в SQL Server 2025: Функция REGEXP_SPLIT_TO_TABLE

Автор: Louis Davidson, REGEXP_ Functions in SQL Server 2025 – REGEXP_SPLIT_TO_TABLE

Продолжим рассмотрение функций семейства REGEXP_, следующая, на которой я хочу остановиться, — это табличная функция REGEXP_SPLIT_TO_TABLE. Это определённо одна из тех функций, которые стоит знать, особенно если когда-либо потребуется извлекать данные из сложной структуры.

Эта функция очень похожа на STRING_SPLIT. И, в отличие от таких функций, как REGEXP_LIKE, в простых случаях здесь можно использовать те же основные параметры, что и у STRING_SPLIT. Но далее возможности становятся практически безграничными, потому что можно определить почти любые разделители. Конечно, у функции есть и недостатки, но об этом поговорим позже.

12.9.25

Новое в SQL Server 2025: Поиск совпадений с помощью функций регулярных выражений

Автор: Louis Davidson, Viewing Multiple Matches in SQL Server Regular Expression Functions

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

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

  • REGEXP_REPLACE – возвращает измененную исходную строку, замененную строкой замены, в которой найдено вхождение шаблона регулярного выражения. Если совпадения не найдены, функция возвращает исходную строку.
  • REGEXP_SUBSTR – возвращает одно вхождение подстроки в анализируемой строке, которое соответствует шаблону регулярного выражения. Если совпадение не найдено, возвращается NULL.
  • REGEXP_INSTR – возвращает начальную или конечную позицию соответствующей подстроки в зависимости от значения аргумента return_option.
  • REGEXP_COUNT – подсчитывает количество совпадений шаблона регулярного выражения в строке.
  • REGEXP_SPLIT_TO_TABLE – используется аналогично функции SPLIT_TO_TABLE, но возвращает таблицу строк, разделенную шаблоном регулярных выражений. Если шаблон не соответствует, функция возвращает строку.
  • REGEXP_MATCHES – возвращает таблицу захваченных подстрок, которые соответствуют шаблону регулярного выражения строке. Если совпадение не найдено, функция не возвращает строку.

Накопительный пакет обновления 21 для SQL Server 2022 - KB5065865

Описание: KB5065865

Скачать: SQLServer2022-KB5065865-x64.exe

SQL Server 2022 — Версия: 16.0.4215.2

Analysis Services — Версия: 16.0.43.247

Дата выпуска: 11 сентября 2025 г.

11.9.25

Новое в SQL Server 2025: Функция REGEXP_SUBSTR

Автор: Louis Davidson, REGEXP_ Functions in SQL Server 2025 – REGEXP_SUBSTR

Функция REGEXP_SUBSTR извлекает части строки на основе шаблона регулярного выражения. Она имеет сходство с функцией SUBSTRING, но есть и важные (и интересные) различия. Эта функция возвращает N-ое вхождение подстроки, которая соответствует регулярному выражению.

Когда я начал писать десятую статью в серии о регулярных выражениях в SQL Server, я должен признаться: я не знал заранее, что именно эта функция делает. Классическая функция SUBSTRING принимает строго позиционные параметры. Задана строка, указываешь начальную позицию и количество символов — и получаешь результат. Никакого сопоставления с шаблоном. К счастью для вас, изучение этого материала у меня заняло совсем немного времени.

В одной из предыдущих статей, где речь шла о REGEXP_MATCHES, я показывал, как можно увидеть все совпадения, которые регулярное выражение находит в строке. REGEXP_SUBSTR в своей простой форме возвращает скалярный результат — то есть одно совпадение.

Страсти по SQL Server 2025: ускоренное восстановление базы данных не исправляет проблему NOLOCK!!!

Автор: Brent Ozar, No, Accelerated Database Recovery Doesn’t Fix NOLOCK

Я никогда не видел в T-SQL такой фразы, которую так же любят использовать, как NOLOCK. Мне постоянно кажется, что я написал уже достаточно публикаций об этом, но вот недавно клиент высказал новую идею:

Мы используем Accelerated Database Recovery в SQL Server 2022, который хранит версии строк внутри таблицы. К тому же мы не используем транзакции — наши операции вставки, обновления и удаления выполняются над одной таблицей за раз, а ваши демонстрации всегда используют транзакции, поэтому нас это не затрагивает.

Исправление безопасности для SQL Server 2022 GDR - KB5065221

Описание: KB5065221

Скачать: SQLServer2022-KB5065221-x64.exe

SQL Server 2022 — версия: 16.0.1150.1

Дата выпуска: 9.09.2025

Исправление безопасности для SQL Server 2022 CU20 - KB5065220

Описание: KB5065220

Скачать: SQLServer2022-KB5065220-x64.exe

SQL Server 2022 — версия: 16.0.4212.1

Дата выпуска: 9.09.2025

Исправление безопасности для SQL Server 2019 CU32 - KB5065222

Описание: KB5065222

Скачать: SQLServer2019-KB5065222-x64.exe

SQL Server 2019 — версия: 15.0.4445.1

Дата выпуска: 9.09.2025