Parameter Sensitive Plan Optimization

SQLServerCentral Article

Exploring Parameter Sensitive Plan Optimization in SQL Server 2022

  • Article

PSPO (Parameter Sensitive Plan Optimization) is a SQL Server feature that improves query performance by accepting varied data sizes based on the runtime parameter value(s) specified by the customer. It deals with the situation in which a single cached plan for a parameterized query isn't the best option for all potential incoming parameter values. Non-uniform data distributions exhibit this phenomenon. When using PSPO, SQL Server keeps several execution plans for a single query, each one customized for a particular parameter value. With the help of this feature, numerous execution plans for a parameterized query are generated, each of which is tailored for a certain range of parameter values.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2023-07-21

4,163 reads

Blogs

Retro Data 2025 – Slidedeck

By

You can find the slides of my session on the €100 DWH in Azure...

The Book of Redgate: We Value Teams

By

This value is something that I still hear today: our best work is done...

Troubleshooting TempDB Log Full Errors When SSMS Won’t Connect

By

Have you ever received the dreaded error from SQL Server that the TempDB log...

Read the latest Blogs

Forums

Is there a way for SP to know who called it?

By water490

Hi everyone I am writing an SP where there is logic inside the SP...

Planning for tomorrow, today - database migrations

By John Martin

Comments posted to this topic are about the item Planning for tomorrow, today -...

Bottlenecks on SQL Server performance

By runarlan

We have a BI-application that connects to input tables on a SQL Server 2022...

Visit the forum

Question of the Day

The Tightly Linked View

I try to run this code on SQL Server 2022. All the objects exist in the database. 

CREATE OR ALTER VIEW OrderShipping
AS
SELECT cl.CityNameID,
       cl.CityName,
       o.OrderID,
       o.Customer,
       o.OrderDate,
       o.CustomerID,
       o.cityId
 FROM dbo.CityList AS cl
 INNER JOIN dbo.[Order] AS o ON o.cityId = cl.CityNameID
GO
CREATE OR ALTER FUNCTION GetShipCityForOrder
(
    @OrderID INT
)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @city VARCHAR(50);
    SELECT @city = os.CityName
    FROM dbo.OrderShipping AS os
    WHERE os.OrderID = @OrderID;
    RETURN @city;
END;
go
What is the result?

See possible answers