So, is parameter sniffing a think of the past with SQL Server 2022? Well, it looks promising, but I wanted to understand how Foglight SQL PI would show me what’s going on.
I took some sample code from https://www.brentozar.com/sql/parameter-sniffing/ , deployed this to SQL Server 2022 and started monitoring with Foglight for SQL.
First of all, let’s look at the problem by setting compatibility level to 150 (SQL Server 2019) and run the scripts. We free the proc cache, run the stored proc with parameter set as 2 and then with parameter set as 1.
Then we can review in SQL PI:
With this initial run we get a key lookup. That’s good for the parameter set as 2 but not when set as 1.
We free the proc cache again, run the stored proc with parameter set as 1 and then with parameter set as 2. Then we can review in SQL PI and compare the two runs:
More importantly we see the different plan, clustered index scan:
This plan is good for parameter set as 1 but not so good for parameter set as 2. But that’s another story or blog.
The big question is what’s going to happen with compatibility level set as 160 (SQL 2022).
We repeat the process as above.
We can see above that the query text is displayed differently, for the same stored proc there are two SQL Handles, and each handle has its own execution plan. And here are the two execution plans:
The first one from above is the best plan for parameter 1 is a Clustered Index Scan:
And the second one is the best plan for parameter 2 using a key lookup:
So once you start using SQL Server 2022, keep an eye out for this with Foglight, if you start seeing multiple SQL Handles for what you think is the same procedure or query, this might be related to Parameter Sensitive Plan Optimization in SQL Server 2022 (https://docs.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitivity-plan-optimization?view=sql-server-ver16)
