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,601 reads

Blogs

SQL Server Journey Part 2: Modern Era (2017 – 2026) – AI/Cloud First

By

Following up on my Part 1 baseline, the journey from 2017 onward changed how...

Google Moves Up Post-Quantum Cryptography Timeline

By

In cryptography, the RSA and ECC algorithms which we use primarily for asymmetric cryptography...

The Book of Redgate: No Politics

By

In today’s world, this might mean something different, but in 2010, we had this...

Read the latest Blogs

Forums

Auto Update Statistics not triggering on filtered indexes

By Leo.Miller

I've got a table with 186,703,969 rows, about 300GB of data. There are several...

What is the difference between SQL Server and SQL Database?

By kamiiteore

I created a SQL Database in Azure Portal but I've just noticed it also...

An Unusual Identity

By Steve Jones - SSC Editor

Comments posted to this topic are about the item An Unusual Identity

Visit the forum

Question of the Day

An Unusual Identity

What values are returned when I run this code? 

CREATE TABLE dbo.IdentityTest2
(
     id NUMERIC(10,0) IDENTITY(10,10) PRIMARY KEY,
     somevalue VARCHAR(20)
)
GO
INSERT dbo.IdentityTest2
(
    somevalue
)
VALUES
( 'Steve')
, ('Bill')
GO
SELECT top 10
 id
 FROM dbo.IdentityTest2

See possible answers