Category: SQL Server

by Miguel Ángel Motos 2024-01-25

SQL Server 2022 Parameter Sensitive Plan

Welcome to a new post on the best Data & AI blog.

In today’s article, we will talk about one of the most important new advantages of SQL Server 2022.

En concreto, estamos hablando de “Parameter Sensitive Plan” o PSP, que forma parte de una nueva serie de características llamada “Intelligent Query Processing”.

What is Parameter Sensitive Plan?

Parameter Sensitive Plan is the new functionality of SQL Server 2022, it has become one of the most used in the world of data since it is very easy to use and understand. In today’s article, we show you step by step how it works.

Step 1: Activating time statistics

To begin, let’s enable time statistics in our SQL Server Management Studio session.

Next, we go to the database that Microsoft provides us as an example “World Wide Importers” that can be downloaded for free and we are going to set the compatibility level of the database to 150, that is, SQL Server 2019.

USE WideWorldImporters;
GO
ALTER DATABASE current SET COMPATIBILITY_LEVEL = 150;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
ALTER DATABASE current SET QUERY_STORE CLEAR;
GO

Step 2: Execution Plan Activation

The first script for our demo will be to execute the “GetStockItemsbySupplier” procedure passing parameter 2 to it.

EXEC Warehouse.GetStockItemsbySupplier 2;
GO

We look at the execution plan and verify that it uses the most suitable for the “GetStockItemsbySupplier” procedure with parameter 2, “Index Seek”.

Now we choose the time statistics to compare later.

Step 3: Procedure execution with parameter 4

Next, we execute the same procedure, but now we use parameter 4.

EXEC Warehouse.GetStockItemsbySupplier 4
GO

Como podemos observar, ahora utiliza lo que considera más adecuado, “Index Scan”.

Paso 5: Ejecución del procedimiento GetStockItemsbySupplier

Finally, we run the “GetStockItemsbySupplier” procedure with parameter 2 again.

EXEC Warehouse.GetStockItemsbySupplier 2
GO

We see that the change in the execution plan has been cached and now uses “Index Scan” instead of “Index Seek” and, we check in the time statistics:

We check what happens in SQL Server 2022.

We are changing the database compatibility level to SQL Server 2022.

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 160
GO

We will run the “GetStockItemsbySupplier” process again, passing parameter 2 to it.

EXEC Warehouse.GetStockItemsbySupplier 2
GO

Verificando el plan de ejecución, vemos que actúa de la misma forma que en SQL Server 2019, usando “Index Seek”.

As the next step, we are going to pass parameter 4 to it.

EXEC Warehouse.GetStockItemsbySupplier 4
GO

We still get the same results, use “Index Scan” for parameter 4.

But…

What will happen if we use parameter 2 again?

Let’s see it!

EXEC Warehouse.GetStockItemsbySupplier 2
GO

Now, if we execute the “GetStockItemsby Supplier” procedure again with parameter 2, we see that it performs an “Index Seek” again, thus improving its brother SQL Server 2019.

This is because SQL Server 2022 has the ability to use one execution plan or another depending on the parameters we pass to it. SQL Server 2022 saves a range of execution plans and selects the best one for each case.

Finally, let’s look at the time statistics, we have returned to correct times.

As a conclusion, SQL Server 2022 has the ability to make many improvements in the performance of our environment on its own and this is thanks to its multiple “Intelligent Query Processing” features.

And that’s it for today’s post, I hope I have helped you improve your knowledge of SQL Server.

And if you liked it, we recommend you check out our latest articles:

The importance of SQL Server Updates

Ledger for SQL Server 2022

How to Configure pgbackrest in PostgreSQL to back up to Azure

If you want us to advise you or help you migrate from version to SQL Server 2022, contact us.