В SQL Server 2022 появилась замечательная возможность\функция, которая называется: Parameter Sensitive Plan Optimization (оптимизация плана с учетом параметров, для краткости - PSP). Как следует из названия, её задача обеспечить производительность параметризованных запросов. Появилась возможность создания несколько планов исполнения для параметризованного запроса, каждый из которых оптимизирован для разных значений параметров. Это позволяет оптимизатору SQL Server выбирать наилучший план выполнения для определенных значений параметров, используемых при исполнении конкретного запроса.

Как работает PSP

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

Когда оптимизация PSP обнаруживает по статистикам неоднородность данных, выбираются три лучших предиката и потом в кэш помещаются несколько планов исполнения для запроса с низким, средним и высоким значениями кардинальности. Во время исполнения запроса оптимизатор выберет план на основании тех значений параметров, которые передал клиент. Это позволяет SQL Server определять оптимальный план выполнения на основе значений параметров, повышая производительность запросов, и при этом не сильно увеличивать размер кэша.  Кроме того, PSP может уменьшить сниффинг параметров — ситуацию, когда SQL Server создает план выполнения на основе первого полученного значения параметра, а затем использует этот же план для всех последующих значений параметров - в некоторых случаях это может привести к неоптимальной производительности. PSP также может улучшить масштабируемость, адаптируя различные размеры неоднородных данных в зависимости от значений параметров на момент исполнения.

Включение и выключение PSP

PSP в SQL Server 2022 включен по умолчанию. Однако его можно отключить на уровне базы данных или запроса. Чтобы отключить PSP на уровне базы данных, вы можете использовать показанную ниже инструкцию T-SQL:

ALTER DATABASE [database_name] 
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Если вы случайно отключили PSP, его можно включить обратно, выполнив следующую команду:

ALTER DATABASE [database_name] 
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;