Category: SQL Server

by Miguel Ángel Motos 2026-03-27

What’s New in Intelligent Query Processing in SQL Server 2025

As we already know, SQL Server 2025 has been part of our catalog since late 2025.

This new version introduces significant enhancements to Intelligent Query Processing (IQP). These innovations focus on automating tasks, reducing the need for manual tuning, and optimizing performance through the use of artificial intelligence.

Key IQP Improvements in SQL Server 2025

More Automatic Optimization

The engine adjusts execution plans dynamically and with less manual intervention.

The database engine is able to adjust itself automatically by detecting inefficient plans and replacing or modifying them, relying on continuous feedback and artificial intelligence techniques to learn from query behavior.

In previous versions, we needed more manual control to carry out these actions, such as manually analyzing performance, rewriting queries, or adding indexes or hints to execution plans. Now all this work is done transparently.

In summary, in SQL Server 2025, this task becomes less reactive and more proactive.

Better Cardinality Estimation

It learns from previous executions (feedback) and improves how it estimates the number of rows, generating more accurate plans.

As we already know, the cardinality estimator has been a headache since it was changed in SQL Server 2014.

In SQL Server 2022 there was already a significant change that allowed the engine to estimate the resulting row count more accurately, but it wasn’t fully refined.

It is in SQL Server 2025 where the cardinality estimator takes on a primary role and, once again thanks to AI, is able to adjust estimates with more accurate results.

This also leads to better handling of changing data: when data grows or its distribution shifts, the optimizer adapts without the need to manually update statistics.

Adaptive Parameter Optimization (APPO)

New techniques (such as APPO) allow choosing different plans depending on parameter values in real time.

In previous versions of SQL Server, the execution plan was generated based on the first parameter value received, which could result in very poor plans for other values.

In SQL Server 2025 this changes: the system can generate and choose between multiple execution plans depending on the parameter value.

This way, we can have one plan for parameter values that return few rows and another for values that return many rows, effectively avoiding parameter sniffing.

Reduction of compilation issues

It improves the behavior of sp_executesql to avoid compilation storms and reduce overhead.

Thanks to the fact that the new SQL Server 2025 engine no longer needs to recompile so many similar or nearly identical queries, excessive CPU usage is avoided.

It also reduces pressure on the plan cache and improves its stability.

Environments with heavy use of dynamic queries, such as Navision or AX, will benefit from this change, as they perform many compilations.

More use of AI and continuous feedback

The system learns from real usage and automatically adjusts memory, parallelism, and optimizer decisions.

Thanks to AI, IQP takes another step forward with advanced mechanisms, machine learning, and continuous feedback, allowing the engine to learn and improve on its own depending on the environment.

With SQL Server 2025 we add an extra layer to the feedback introduced in SQL Server 2022, along with an additional layer of Artificial Intelligence that helps apply improvements proactively.

Summary

SQL Server 2025 embraces AI, and together they converge in a way that helps us manage and administer our environments more accurately.

Practical example: OPPO in SQL Server 2025

To illustrate one of these improvements, we are going to focus on OPPO.

To demonstrate OPPO in SQL Server 2025, I have created a procedure with a real optional parameter (@CustomerID).

The following script creates the prerequisites (table, data, and stored procedure)

USE [DemoDB];
GO

/* 1) OPPO prerequisites */
ALTER DATABASE [DemoDB] SET COMPATIBILITY_LEVEL = 170;
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;
GO

/* Cleanup in case previous objects existed */
IF OBJECT_ID('dbo.SalesDemo_OPPO', 'U') IS NOT NULL
DROP TABLE dbo.SalesDemo_OPPO;
GO

IF OBJECT_ID('dbo.usp_SalesDemo_OPPO', 'P') IS NOT NULL
DROP PROC dbo.usp_SalesDemo_OPPO;
GO

/* 2) Demo table */
CREATE TABLE dbo.SalesDemo_OPPO
(
SaleID bigint IDENTITY(1,1) NOT NULL,
CustomerID int NOT NULL,
OrderDate date NOT NULL,
Amount decimal(12,2) NOT NULL,
Filler char(300) NOT NULL DEFAULT REPLICATE('X',300),
CONSTRAINT PK_SalesDemo_OPPO
PRIMARY KEY CLUSTERED (SaleID)
);
GO

/* 3) Data:
- A very large number of rows for CustomerID = 1
- Very few rows for the rest
This creates a clear contrast between @CustomerID = NULL and @CustomerID = 2.”
*/
;WITH N AS
(
SELECT TOP (5000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.SalesDemo_OPPO (CustomerID, OrderDate, Amount)
SELECT
CASE WHEN n <= 450000 THEN 1 ELSE n - 449999 END AS CustomerID,
DATEADD(day, n % 365, '2025-01-01'),
CAST((n % 1000) + 0.99 AS decimal(12,2))
FROM N;
GO

/* 4) Useful index for the SELECT case */
CREATE INDEX IX_SalesDemo_OPPO_CustomerID
ON dbo.SalesDemo_OPPO (CustomerID);
GO

/* 5) Up‑to‑date statistics */
UPDATE STATISTICS dbo.SalesDemo_OPPO WITH FULLSCAN;
GO

/* 6) Procedure with a REAL PARAMETER (not a local variable)
This is the eligible pattern for OPPO.
*/
CREATE OR ALTER PROC dbo.usp_SalesDemo_OPPO
@CustomerID int = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT COUNT_BIG(*) AS TotalRows
FROM dbo.SalesDemo_OPPO
WHERE CustomerID = @CustomerID
   OR @CustomerID IS NULL;
END
GO

/* 7) We clear the cache so the test is clean. */
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

The first scenario consists of passing the value NULL to the variable.

/* 8) Scenario 1 */
SET STATISTICS IO, TIME ON;
GO
EXEC dbo.usp_SalesDemo_OPPO @CustomerID = NULL;
GO

When executing the query with @CustomerID = NULL, the engine chooses a plan designed to read a large volume of data.

Scenario two, on the other hand, is executed by passing the value 2 to the variable.

/* 8) Scenario 2 */
EXEC dbo.usp_SalesDemo_OPPO @CustomerID = 2;
GO

When executing the same query with @CustomerID = 2 , it selects a more selective variant based on an index seek.

In the execution plan, you can see PLAN PER VALUE along with optional_predicate(@CustomerID IS NULL), which confirms that SQL Server 2025 has generated plan variants adapted to the parameter value.

As we can see, the plan used changes depending on the value provided.

In short, SQL Server 2025 consolidates a more intelligent and autonomous engine, capable of continuously improving its performance thanks to AI and real‑world feedback from the environment. A clear step forward toward more precise optimization with less dependence on manual intervention.

Articles in the SQL Server 2025 Series

Miguel Ángel Motos

Azure Data Engineer con más de 7 años de experiencia. Conocimiento de múltiples herramientas y enfocado en el mundo del dato. Experto en tuning de queries y mejora de rendimiento de Base de Datos. Profesional apasionado por la tecnología y los deportes al aire libre. En mi tiempo libre, me encontrarás jugando vóley playa o disfrutando con nuevos videojuegos.