Tag Archives: SQL Server Extended Events

Standard

Posted by

Posted on

June 2, 2026

Posted under

Core

Comments

Leave a comment

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:

  1. DOP Feedback Applied Successfully
    • SQL Server reduces or adjusts DOP
    • Performance improves over repeated executions
  2. Optimal DOP Identified
    • No change needed
    • System confirms current DOP is efficient
  3. 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 ApproachSQL Server 2025 Approach
Manual MAXDOP tuningAutomatic per-query tuning
Trial and errorData-driven decisions
Static configurationAdaptive 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.

Standard

Posted by

Posted on

July 24, 2024

Posted under

Core

Comments

Leave a comment

Understanding Max Server Memory and Minimum Server Memory in SQL Server

SQL Server’s memory management is a crucial aspect of its performance and stability. Two important settings in this context are Max Server Memory and Minimum Server Memory. These settings help SQL Server efficiently manage its memory usage, ensuring optimal performance and avoiding system instability.

What is Max Server Memory?

Max Server Memory limits the amount of memory that SQL Server can use for its operations. This setting helps prevent SQL Server from consuming too much memory, which could negatively impact the operating system and other applications running on the same server.

Importance of Max Server Memory
  1. System Stability: By capping the memory usage, you ensure that enough memory is available for the OS and other applications, preventing system-wide slowdowns or crashes.
  2. Performance Optimization: Properly configuring Max Server Memory allows SQL Server to use memory efficiently, reducing the need for frequent data disk reads and writes, which can significantly slow down performance.
  3. Resource Allocation: In environments where SQL Server shares resources with other applications, setting an appropriate Max Server Memory ensures fair resource distribution.
Calculating and Setting Max Server Memory

To start, you should leave enough memory for the operating system and any other applications. A common approach is to allocate at least 4 GB or 10% of total system memory (whichever is larger) to the OS. The rest can be allocated to SQL Server as Max Server Memory.

Example Calculation: Suppose you have a server with 32 GB of RAM:

  1. Allocate memory for the OS and other applications:
    • 4 GB (minimum recommended) or 10% of 32 GB = 3.2 GB
    • Choosing the larger value: 4 GB
  2. Subtract this from the total RAM:
    • 32 GB – 4 GB = 28 GB
  3. Set Max Server Memory to 28 GB.

Setting Max Server Memory in SQL Server: You can set Max Server Memory using SQL Server Management Studio (SSMS) or T-SQL commands:

  • Using SSMS:
    1. Open SSMS and connect to your SQL Server instance.
    2. Right-click on the server name and select “Properties.”
    3. Navigate to the “Memory” tab.
    4. Set the “Maximum server memory (in MB)” to the calculated value.
  • Using T-SQL:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 28672; -- Set to 28 GB (28 * 1024 MB)
RECONFIGURE;

What is Minimum Server Memory?

Minimum Server Memory specifies the minimum amount of memory SQL Server should attempt to reserve after it has started. However, it’s worth noting that SQL Server doesn’t start with this memory allocation; instead, it gradually grows its memory usage up to this amount as needed.

Importance of Minimum Server Memory
  1. Ensuring Performance: Setting a minimum ensures that SQL Server has enough memory for its operations, which is crucial for maintaining performance under varying workloads.
  2. Avoiding Memory Pressure: It helps avoid situations where SQL Server might have to give up memory under pressure, which could degrade performance.

Potential Issues with Incorrect Settings

  1. Setting Max Server Memory Too High: This can lead to insufficient memory for the OS and other applications, causing system instability, swapping, and even crashes.
  2. Setting Max Server Memory Too Low: SQL Server might not have enough memory for optimal performance, leading to excessive disk I/O, slower queries, and reduced throughput.
  3. Incorrect Minimum Server Memory: If set too high, it can reserve more memory than necessary, potentially starving other processes. If set too low, SQL Server might not have enough resources to function efficiently under load.

Best Practices

  1. Monitor and Adjust: Regularly monitor memory usage and adjust settings based on the workload and system performance.
  2. Consider the Entire System: Take into account the memory requirements of the OS and other applications on the server.
  3. Start Conservative: Begin with a conservative estimate and gradually increase Max Server Memory as needed, observing the system’s behavior.

In conclusion, correctly configuring Max Server Memory and Minimum Server Memory is vital for SQL Server’s performance and the overall system’s stability. By carefully calculating and setting these values, you can ensure a balanced and efficient use of resources, providing a stable and high-performing environment for your SQL Server workloads.

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

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.