Posted by

Posted on

June 2, 2026

Posted under

Core

Comments

SQL Server 2025 Series : Degree Of Parallelism (DOP) Feedback Explained with Real-Time Demo!

Parallelism tuning has always been one of the most challenging aspects of SQL Server performance optimization. DBAs often spend hours fine-tuning MAXDOP settings, trying to strike the perfect balance between performance and resource consumption.

With SQL Server 2025, this challenge is significantly reduced thanks to Degree of Parallelism (DOP) Feedback—a powerful Intelligent Query Processing feature that automatically optimizes parallel query execution.

In this blog, we will walk through:

What DOP Feedback is
How to track it using Extended Events
A real-time demo with multiple scenarios
How to validate whether DOP Feedback is working on your server

What is DOP Feedback?

DOP Feedback enables SQL Server to self-adjust the degree of parallelism for repeated queries. Instead of relying on static MAXDOP settings, SQL Server analyzes runtime performance (CPU usage, execution time, waits) and dynamically tunes DOP for subsequent executions.

This helps:

Reduce excessive CPU usage
Improve query performance
Eliminate manual tuning effort

Tracking DOP Feedback using Extended Events

To understand how SQL Server applies DOP Feedback, we can capture internal engine decisions using Extended Events.

Setup DOP Feedback Tracking

			
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'DOPFeedbackWatch')
    DROP EVENT SESSION DOPFeedbackWatch ON SERVER;
GO
CREATE EVENT SESSION DOPFeedbackWatch ON SERVER
ADD EVENT sqlserver.dop_feedback_eligible_query(
    ACTION(sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash)),
ADD EVENT sqlserver.dop_feedback_provided(
    ACTION(sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash)),
ADD EVENT sqlserver.dop_feedback_validation(
    ACTION(sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash)),
ADD EVENT sqlserver.dop_feedback_stabilized(
    ACTION(sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash)),
ADD EVENT sqlserver.dop_feedback_reverted(
    ACTION(sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash)),
ADD EVENT sqlserver.dop_feedback_analysis_stopped(
    ACTION(sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash)),
ADD EVENT sqlserver.dop_feedback_reassessment_failed(
    ACTION(sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash))
ADD TARGET package0.ring_buffer;
GO
ALTER EVENT SESSION DOPFeedbackWatch ON SERVER STATE = START;
GO

		

What this captures

This session helps us track:

When a query becomes eligible for DOP Feedback
When SQL Server adjusts DOP
Whether feedback is validated or reverted
When the system stabilizes on an optimal DOP

This gives real-time visibility into the self-tuning engine behavior.

DOP Feedback Demo (Step-by-Step)

Let’s simulate workloads to observe DOP Feedback in action.

Scenario 1: High-Volume Parallel Query (Feedback Adjusted)

			
CREATE DATABASE jbdb
GO
USE JBDB
GO
--Create table and load table
CREATE TABLE [dbo].[Table1]([Col1] [int] IDENTITY(1,1) ON [PRIMARY]
GO
set nocount on
insert into Table1 (Col2, Col3, Col4, Col5) values (1, REPLICATE ('a',4000), 10, 100)
go 999
insert into Table1 (Col2, Col3, Col4, Col5) values (2, REPLICATE ('z',4000), 11, 100)
go 99999
insert into Table1 (Col2, Col3, Col4, Col5) values (3, REPLICATE ('f',4000), 12, 100)
go 8965
insert into Table1 (Col2, Col3, Col4, Col5) values (4, REPLICATE ('g',4000), 13, 100)
go 7844
insert into Table1 (Col2, Col3, Col4, Col5) values (5, REPLICATE ('u',4000), 14, 100)
go 4567
insert into Table1 (Col2, Col3, Col4, Col5) values (2, REPLICATE ('z',4000), 11, 100)
go 751049
-- Stored Procedure to query the table
CREATE OR ALTER PROCEDURE [dbo].[usp_GetDetails] @Col2 int
AS
BEGIN
SELECT top (50000)
    [Col1], 
    [Col2], 
    [Col3], 
    [Col4], 
    [Col5]
FROM dbo.Table1
WHERE [Col2] = @Col2
ORDER BY [Col3];
END
--EXECUTE the stored procedure
EXEC [dbo].[usp_GetDetails] @Col2 = 2
--OSTRESS
C:\Program Files\Microsoft Corporation\RMLUtils>ostress -S"VIJANAK\IN2025" -E -Q"EXEC usp_GetDetails @Col2 = 2;" -n1 -r100 -q -dJBDB

		

In this scenario:

Query executes repeatedly under load
SQL Server identifies inefficiencies in parallelism
DOP is adjusted for better performance

Scenario 2: Skewed Parallelism (Feedback Evaluation)

			
use JBBlog
[dbo].[usp_SkewedParallelReport]
ostress -S"VIJANAK\IN2025" -E -Q"EXEC usp_SkewedParallelReport;" -n1 -r100 -q -dJBBlog

Here:

SQL Server evaluates skewed workloads
Determines whether reducing DOP improves efficiency
May validate or reject feedback

Monitoring Live Workload

To see what is actively running:

			
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  -- What SQL Statements Are Currently Running?
  SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
      er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
                er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
  FROM sys.dm_exec_requests er
  INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
  where session_Id NOT IN (@@SPID)   -- Ignore this current statement.
  ORDER BY 1, 2
  go

		

This helps you:

Identify active queries
Validate parallel workloads
Correlate with Extended Event output

Key Observations from the Demo

From the above scenarios, you will typically notice:

DOP Feedback Applied Successfully
- SQL Server reduces or adjusts DOP
- Performance improves over repeated executions
Optimal DOP Identified
- No change needed
- System confirms current DOP is efficient
Query Not Eligible
- Some queries are excluded
- Depends on execution pattern and workload

Why This Matters

DOP Feedback fundamentally changes how DBAs approach tuning:

Traditional Approach	SQL Server 2025 Approach
Manual MAXDOP tuning	Automatic per-query tuning
Trial and error	Data-driven decisions
Static configuration	Adaptive optimization

Watch the Full Demo

I’ve recorded a complete walkthrough of this setup on my YouTube channel JBSWiki. If you’re a visual learner, go check it out!

👉 Watch here: https://www.youtube.com/watch?v=nVMeQeiUKTA

Final Thoughts

With SQL Server 2025, the database engine becomes smarter and more autonomous.

DOP Feedback:

Eliminates guesswork
Improves performance stability
Reduces CPU contention
Adapts dynamically to workload changes

For DBAs and performance engineers, this means less time tuning and more time delivering value.

If you are testing SQL Server 2025 in your environment, I highly recommend running this demo and observing the Extended Events output — it gives incredible insight into how the engine learns and adapts in real time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted by

Vivek Janakiraman

Posted on

August 10, 2024

Posted under

SQL Server 2022

Comments

SQL Server 2022 Performance Tuning Tips: Optimizing for Peak Efficiency

SQL Server 2022 introduces numerous enhancements aimed at improving performance and efficiency. Whether you’re dealing with query optimization, index management, or memory allocation, these new features and best practices can help you achieve significant performance gains. In this blog, we’ll explore specific tuning tips and tricks for SQL Server 2022, highlighting changes that enhance query performance without requiring any code changes. We’ll also address how these improvements solve longstanding issues from previous versions. Practical T-SQL examples will be provided to help you implement these tips. Let’s dive in! 🎉

Key SQL Server 2022 Enhancements for Performance Tuning ⚙️

Intelligent Query Processing (IQP) Enhancements: SQL Server 2022 continues to enhance IQP features, including Adaptive Joins, Batch Mode on Rowstore, and more.
Automatic Plan Correction: This feature helps to identify and fix suboptimal execution plans automatically.
Increased Parallelism: SQL Server 2022 offers more granular control over parallelism, improving the performance of complex queries.
Optimized TempDB Usage: Improvements in TempDB management reduce contention and improve query performance.

Specific Tuning Tips and Tricks 🔧

1. Leverage Intelligent Query Processing (IQP) 🧠

SQL Server 2022 builds on the IQP feature set, which adapts to your workload to optimize performance. Here are some specific IQP features to take advantage of:

Batch Mode on Rowstore: This feature allows batch mode processing on traditional rowstore tables, providing significant performance improvements for analytical workloads.

Example Query:

-- Without Batch Mode on Rowstore
SELECT SUM(SalesAmount) 
FROM Sales.SalesOrderDetail
WHERE ProductID = 707;

-- With Batch Mode on Rowstore (SQL Server 2022)
SELECT SUM(SalesAmount) 
FROM Sales.SalesOrderDetail WITH (USE HINT ('ENABLE_BATCH_MODE'))
WHERE ProductID = 707;

Adaptive Joins: SQL Server dynamically chooses the best join strategy (nested loop, hash join, etc.) during query execution, optimizing performance based on actual data distribution.

Example Query:

-- Without Adaptive Joins
SELECT p.ProductID, p.Name, SUM(s.Quantity) AS TotalSold
FROM Production.Product p
JOIN Sales.SalesOrderDetail s ON p.ProductID = s.ProductID
GROUP BY p.ProductID, p.Name;

-- With Adaptive Joins (SQL Server 2022)
SELECT p.ProductID, p.Name, SUM(s.Quantity) AS TotalSold
FROM Production.Product p
JOIN Sales.SalesOrderDetail s ON p.ProductID = s.ProductID
GROUP BY p.ProductID, p.Name;

2. Utilize Automatic Plan Correction 🛠️

Automatic Plan Correction helps to identify and fix inefficient execution plans. This feature automatically captures query performance baselines and identifies regressions, correcting them as needed.

Enabling Automatic Plan Correction:

ALTER DATABASE SCOPED CONFIGURATION 
SET AUTOMATIC_TUNING = AUTO_PLAN_CORRECTION = ON;

3. Optimize TempDB Usage 🗄️

TempDB can often become a bottleneck in SQL Server. SQL Server 2022 introduces several enhancements to manage TempDB more efficiently:

Memory-Optimized TempDB Metadata: Reduces contention on system tables in TempDB, particularly beneficial for workloads with heavy use of temporary tables.

Enabling Memory-Optimized TempDB Metadata:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED_TEMPDB_METADATA = ON;

4. Fine-Tune Parallelism Settings 🏃‍♂️

SQL Server 2022 offers more granular control over parallelism, which can improve the performance of complex queries by better utilizing CPU resources.

Setting MAXDOP (Maximum Degree of Parallelism):

-- Setting MAXDOP for the server
EXEC sys.sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

-- Setting MAXDOP for a specific query
SELECT * 
FROM LargeTable 
OPTION (MAXDOP 4);

Solving Previous Issues with SQL Server 2022 🔄

1. Resolving Parameter Sniffing Issues 🎯

Parameter sniffing can lead to suboptimal plans being reused, causing performance issues. SQL Server 2022’s Parameter Sensitive Plan Optimization addresses this by creating multiple plans for different parameter values.

Example T-SQL Query:

-- Enabling Parameter Sensitive Plan Optimization
ALTER DATABASE SCOPED CONFIGURATION 
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

2. Handling Query Store Performance Overhead 📈

The Query Store feature in SQL Server 2022 has been enhanced to minimize performance overhead while still capturing valuable query performance data.

Best Practices:

Limit Data Capture: Configure Query Store to capture only significant queries to reduce overhead.
Use Read-Only Secondary Replicas: Leverage Always On Availability Groups to offload Query Store data collection to read-only replicas.

Business Use Case: E-Commerce Platform 🛒

Consider an e-commerce platform experiencing slow query performance during peak shopping seasons. By implementing SQL Server 2022’s performance tuning features, the platform can:

Improve Checkout Process Speed: Use IQP features like Batch Mode on Rowstore to optimize complex analytical queries that calculate discounts and shipping costs.
Enhance Product Search Efficiency: Utilize Adaptive Joins to dynamically optimize search queries based on the data distribution of products.
Reduce Database Contention: Apply TempDB optimization techniques to handle the high volume of temporary data generated during transactions.

Conclusion 🎉

SQL Server 2022 offers a wealth of new features and enhancements designed to optimize performance and solve long-standing issues. By leveraging Intelligent Query Processing, Automatic Plan Correction, and other tuning tips, you can achieve significant performance gains without extensive code changes. Whether you’re running a high-traffic e-commerce platform or a complex analytical workload, these tuning tips can help you get the most out of your SQL Server 2022 environment.

For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.