SQL Server 2022 introduces Parameter Sensitive Plan Optimization (PSPO), a feature that revolutionizes the performance of parameterized queries. With PSPO, the SQL Server stores multiple execution plans for a single query, each tailored to different parameter values. This allows the database engine to dynamically select the most optimized execution plan based on the specific parameter values provided at runtime. By accommodating different data sizes and distributions, PSPO significantly improves query performance, making it particularly valuable for applications dealing with non-uniform data.
If you want to use PSPO, you have to upgrade your compatibility level to 160
As of today;
| Product | Database Engine version | Default compatibility level designation | Supported compatibility level values |
| SQL Server 2022 (16.x) | 16 | 160 | 160, 150, 140, 130, 120, 110, 100 |
| SQL Server 2019 (15.x) | 15 | 150 | 150, 140, 130, 120, 110, 100 |
| SQL Server 2017 (14.x) | 14 | 140 | 140, 130, 120, 110, 100 |
| Azure SQL Database | 12 | 150 | 160, 150, 140, 130, 120, 110, 100 |
| Azure SQL Managed Instance | 12 | 150 | 160, 150, 140, 130, 120, 110, 100 |
| SQL Server 2016 (13.x) | 13 | 130 | 130, 120, 110, 100 |
| SQL Server 2014 (12.x) | 12 | 120 | 120, 110, 100 |
| SQL Server 2012 (11.x) | 11 | 110 | 110, 100, 90 |
| SQL Server 2008 R2 (10.50.x) | 10.5 | 100 | 100, 90, 80 |
| SQL Server 2008 (10.0.x) | 10 | 100 | 100, 90, 80 |
| SQL Server 2005 (9.x) | 9 | 90 | 90, 80 |
| SQL Server 2000 (8.x) | 8 | 80 | 80 |
How to switch;
-- to view
SELECT name,compatibility_level FROM sys.databases WHERE name='DBname'
-- to set
ALTER DATABASE [DBname] SET COMPATIBILITY_LEVEL = 160