Posted by

Posted on

June 4, 2026

Posted under

Core

Comments

SQL Server 2025 Series : SQL Backups Just Got Smaller and Faster – ZSTD Compression Live Demo!

Database backups are one of the most critical parts of any data platform strategy. Whether you are protecting transactional systems, reporting environments, or large enterprise workloads, backups directly influence storage consumption, recovery objectives, operational overhead, and even infrastructure cost.

With SQL Server 2025, backup compression gets a major upgrade through support for ZSTD (Zstandard) compression. This is a significant enhancement for database administrators and architects looking to reduce backup size, improve efficiency, and gain more flexibility in how backup workloads are tuned.

In this post, I will walk through what ZSTD compression is, why it matters, and how to test it using a simple end-to-end backup and restore demo.

What is ZSTD Compression?

ZSTD, or Zstandard, is a modern lossless compression algorithm designed to deliver an excellent balance between:

High compression ratio
Fast compression speed
Very fast decompression
Flexible tuning through compression levels

For years, backup compression has helped reduce storage usage and improve I/O efficiency. But as database sizes continue to grow, traditional compression methods may not always provide the best balance between speed and storage savings.

That is where ZSTD becomes exciting.

SQL Server 2025 now allows backups to use the ZSTD algorithm, giving DBAs a newer and more efficient option for compressing database backups.

Why This Matters

As backup volumes increase, organizations typically face a common set of challenges:

Backup files consume too much space
Backup windows become longer
Restore operations need to stay fast and reliable
Storage and archival costs continue growing
Sending backups across environments or regions becomes more expensive

ZSTD helps address these challenges by improving backup compression efficiency while still maintaining strong decompression performance.

In practical terms, this means you may be able to:

Store more backups using less space
Improve backup storage utilization
Reduce backup repository growth
Optimize retention strategies
Improve overall operational efficiency

Key Benefits of ZSTD Backup Compression

1. Better Compression Efficiency

One of the biggest advantages of ZSTD is its ability to compress data more efficiently than older approaches in many scenarios. This can result in noticeably smaller backup files, especially for large databases with compressible data patterns.

2. Faster Decompression

Backup is only one half of the story. Restore performance is equally important. ZSTD is known for fast decompression, which is valuable during restore operations when time matters most.

3. Compression Levels for Flexibility

SQL Server 2025 introduces the ability to choose different compression levels when using ZSTD. This is useful because not every environment has the same priorities.

For example:

If your priority is faster backup completion, a lower level may be enough
If your priority is maximum storage reduction, a higher level may be better
If you want a balance, medium can be a good starting point

4. Familiar Backup Workflow

Another great advantage is that ZSTD integrates directly into the backup syntax DBAs are already familiar with. There is no need to redesign the backup process from scratch. You simply use the appropriate compression options while taking the backup.

Demo Objective

In this walkthrough, the goal is to compare:

A normal compressed backup
A ZSTD backup with the default compression level
A ZSTD backup with MEDIUM compression level
A ZSTD backup with HIGH compression level

After each backup, we also validate the backup metadata and restore the database to separate target names and file paths. This gives us a complete end-to-end validation of both backup creation and restore success.

For this demo, we will use the JBFinance database and the exact script provided below.

What We Will Validate

This demo helps validate several things:

Backup command executes successfully
Backup header can be read
Backup file can be restored successfully
Different ZSTD compression levels can be tested easily
Separate restored copies can be created for comparison and verification

Step 1: Review the Source Database

Before taking backups, it is always useful to review the source database size and file layout.

			
USE [master]
GO
sp_helpdb JBFinance
GO

This gives you a quick overview of the database structure and helps confirm the logical file names that will later be used during restore.

Step 2: Take a Regular Compressed Backup

First, take a standard compressed backup using the familiar compression option.

			
BACKUP DATABASE JBFinance to DISK ='C:\temp\ZSTD\JBFinance_normal.bak' with COMPRESSION,STATS=1;
GO

What this does

This command creates a compressed backup of the JBFinance database and writes it to the specified backup location.

Why this matters

This serves as your baseline. You can compare this backup later with the ZSTD-based backups to understand whether ZSTD offers better storage efficiency or operational benefits in your environment.

Step 3: Inspect the Backup Metadata

After the backup completes, inspect the backup header.

			
RESTORE HEADERONLY FROM DISK ='C:\temp\ZSTD\JBFinance_normal.bak';
GO

Why this step is useful

This confirms that:

The backup file is valid
SQL Server can read the backup metadata
The backup can be used in restore operations

It is also a good verification step before running a restore.

Step 4: Restore the Regular Compressed Backup

Now restore that baseline backup to a separate database name.

			
RESTORE DATABASE [JBFinance_Normal] FROM  DISK = N'c:\temp\zstd\JBFinance_normal.bak' WITH  FILE = 1,  MOVE N'JBFinance_Data1' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Data1.mdf',  MOVE N'JBFinance_Data2' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Data2.mdf',  MOVE N'JBFinance_Data3' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Data3.mdf',  MOVE N'JBFinance_Data4' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Data4.mdf',  MOVE N'JBFinance_Log' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Log.ldf',  NOUNLOAD,  STATS = 1
GO

Why restore it?

A backup is only useful if it can be restored successfully. This step validates the full backup-and-restore chain.

Step 5: Take a ZSTD Backup Using the Default Compression Level

Now let’s move to the new feature.

			
BACKUP DATABASE JBFinance to DISK ='C:\temp\ZSTD\JBFinance_ZSTD.bak' with COMPRESSION(ALGORITHM = ZSTD),STATS=1; --Default compression Level is LOW
GO

Important note

When only ALGORITHM = ZSTD is specified, the default compression level is LOW.

Why this is interesting

This gives you a first look at how ZSTD behaves with minimal additional tuning. It is a good starting point for most first-time tests.

Step 6: Validate the ZSTD Backup Header

			
RESTORE HEADERONLY FROM DISK ='C:\temp\ZSTD\JBFinance_ZSTD.bak';
GO

Again, this confirms the backup is readable and valid.

Step 7: Restore the ZSTD LOW Backup

			
RESTORE DATABASE [JBFinance_Low] FROM  DISK = N'c:\temp\zstd\JBFinance_ZSTD.bak' WITH  FILE = 1,  MOVE N'JBFinance_Data1' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Data1.mdf',  MOVE N'JBFinance_Data2' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Data2.mdf',  MOVE N'JBFinance_Data3' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Data3.mdf',  MOVE N'JBFinance_Data4' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Data4.mdf',  MOVE N'JBFinance_Log' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Log.ldf',  NOUNLOAD,  STATS = 1
GO

This confirms that a backup created using ZSTD can be restored just as expected.

Step 8: Take a ZSTD Backup with MEDIUM Compression Level

Now let’s test the MEDIUM compression level.

			
BACKUP DATABASE JBFinance to DISK ='C:\temp\ZSTD\JBFinance_ZSTD_MEDIUM.bak' with COMPRESSION(ALGORITHM = ZSTD, LEVEL = MEDIUM),STATS=1;
GO

Why MEDIUM matters

This is often the level many teams will be interested in because it may provide a stronger balance between:

Backup size reduction
CPU cost
Backup duration

Step 9: Validate the MEDIUM Backup Header

			
RESTORE HEADERONLY FROM DISK ='C:\temp\ZSTD\JBFinance_ZSTD_MEDIUM.bak';
GO

Step 10: Restore the MEDIUM Backup

			
RESTORE DATABASE [JBFinance_Medium] FROM  DISK = N'c:\temp\zstd\JBFinance_ZSTD_MEDIUM.bak' WITH  FILE = 1,  MOVE N'JBFinance_Data1' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Data1.mdf',  MOVE N'JBFinance_Data2' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Data2.mdf',  MOVE N'JBFinance_Data3' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Data3.mdf',  MOVE N'JBFinance_Data4' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Data4.mdf',  MOVE N'JBFinance_Log' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Log.ldf',  NOUNLOAD,  STATS = 1
GO

This gives you a restored copy from the ZSTD MEDIUM backup for validation and comparison.

Step 11: Take a ZSTD Backup with HIGH Compression Level

Now let’s test the HIGH compression level.

			
BACKUP DATABASE JBFinance to DISK ='C:\temp\ZSTD\JBFinance_ZSTD_HIGH.bak' with COMPRESSION(ALGORITHM = ZSTD, LEVEL = HIGH),STATS=1;
GO

Why HIGH matters

If your main goal is maximum backup size reduction, this option is worth testing. In some environments, HIGH can offer the most aggressive storage savings, though it may also require more CPU resources during backup creation.

Step 12: Validate the HIGH Backup Header

			
RESTORE HEADERONLY FROM DISK ='C:\temp\ZSTD\JBFinance_ZSTD_HIGH.bak';
GO

Step 13: Restore the HIGH Backup

			
RESTORE DATABASE [JBFinance_High] FROM  DISK = N'c:\temp\zstd\JBFinance_ZSTD_HIGH.bak' WITH  FILE = 1,  MOVE N'JBFinance_Data1' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Data1.mdf',  MOVE N'JBFinance_Data2' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Data2.mdf',  MOVE N'JBFinance_Data3' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Data3.mdf',  MOVE N'JBFinance_Data4' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Data4.mdf',  MOVE N'JBFinance_Log' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Log.ldf',  NOUNLOAD,  STATS = 1
GO

This completes the end-to-end validation of all backup variants in the test.

Full Demo Script

For convenience, here is the complete script exactly as provided for the demo.

			
--- ZSTD Compression
USE [master]
GO
sp_helpdb JBFinance
GO
BACKUP DATABASE JBFinance to DISK ='C:\temp\ZSTD\JBFinance_normal.bak' with COMPRESSION,STATS=1; 
GO  
RESTORE HEADERONLY FROM DISK ='C:\temp\ZSTD\JBFinance_normal.bak';  
GO 
RESTORE DATABASE [JBFinance_Normal] FROM  DISK = N'c:\temp\zstd\JBFinance_normal.bak' WITH  FILE = 1,  MOVE N'JBFinance_Data1' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Data1.mdf',  MOVE N'JBFinance_Data2' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Data2.mdf',  MOVE N'JBFinance_Data3' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Data3.mdf',  MOVE N'JBFinance_Data4' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Data4.mdf',  MOVE N'JBFinance_Log' TO N'C:\temp\ZSTD\Non-STD\JBFinance_Log.ldf',  NOUNLOAD,  STATS = 1
GO  
-------
BACKUP DATABASE JBFinance to DISK ='C:\temp\ZSTD\JBFinance_ZSTD.bak' with COMPRESSION(ALGORITHM = ZSTD),STATS=1; --Default compression Level is LOW
GO  
RESTORE HEADERONLY FROM DISK ='C:\temp\ZSTD\JBFinance_ZSTD.bak';
GO  
RESTORE DATABASE [JBFinance_Low] FROM  DISK = N'c:\temp\zstd\JBFinance_ZSTD.bak' WITH  FILE = 1,  MOVE N'JBFinance_Data1' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Data1.mdf',  MOVE N'JBFinance_Data2' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Data2.mdf',  MOVE N'JBFinance_Data3' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Data3.mdf',  MOVE N'JBFinance_Data4' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Data4.mdf',  MOVE N'JBFinance_Log' TO N'C:\temp\ZSTD\ZSTD\JBFinance_Log.ldf',  NOUNLOAD,  STATS = 1
GO   
-------
BACKUP DATABASE JBFinance to DISK ='C:\temp\ZSTD\JBFinance_ZSTD_MEDIUM.bak' with COMPRESSION(ALGORITHM = ZSTD, LEVEL = MEDIUM),STATS=1;
GO 
RESTORE HEADERONLY FROM DISK ='C:\temp\ZSTD\JBFinance_ZSTD_MEDIUM.bak';
GO 
RESTORE DATABASE [JBFinance_Medium] FROM  DISK = N'c:\temp\zstd\JBFinance_ZSTD_MEDIUM.bak' WITH  FILE = 1,  MOVE N'JBFinance_Data1' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Data1.mdf',  MOVE N'JBFinance_Data2' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Data2.mdf',  MOVE N'JBFinance_Data3' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Data3.mdf',  MOVE N'JBFinance_Data4' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Data4.mdf',  MOVE N'JBFinance_Log' TO N'C:\temp\ZSTD\ZSTD_MEDIUM\JBFinance_Log.ldf',  NOUNLOAD,  STATS = 1
GO  
------
BACKUP DATABASE JBFinance to DISK ='C:\temp\ZSTD\JBFinance_ZSTD_HIGH.bak' with COMPRESSION(ALGORITHM = ZSTD, LEVEL = HIGH),STATS=1;
GO 
RESTORE HEADERONLY FROM DISK ='C:\temp\ZSTD\JBFinance_ZSTD_HIGH.bak';
GO
RESTORE DATABASE [JBFinance_High] FROM  DISK = N'c:\temp\zstd\JBFinance_ZSTD_HIGH.bak' WITH  FILE = 1,  MOVE N'JBFinance_Data1' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Data1.mdf',  MOVE N'JBFinance_Data2' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Data2.mdf',  MOVE N'JBFinance_Data3' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Data3.mdf',  MOVE N'JBFinance_Data4' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Data4.mdf',  MOVE N'JBFinance_Log' TO N'C:\temp\ZSTD\ZSTD_HIGH\JBFinance_Log.ldf',  NOUNLOAD,  STATS = 1
GO

		

What to Observe During the Demo

When you run this demo in your environment, pay close attention to the following:

1. Backup File Size

Compare the sizes of:

JBFinance_normal.bak
JBFinance_ZSTD.bak
JBFinance_ZSTD_MEDIUM.bak
JBFinance_ZSTD_HIGH.bak

This helps you understand how each compression option affects storage savings.

2. Backup Completion Time

Capture how long each backup takes to complete. Higher compression levels may reduce backup size further, but they can also use more CPU.

3. Restore Success

Each backup should restore successfully into its own database copy. This confirms backup reliability and end-to-end usability.

4. Compression Trade-Offs

The best compression level is not always the smallest file. In many real-world environments, the right choice depends on:

Backup window
CPU availability
Storage cost
Restore expectations
Workload sensitivity

4. My Test details

Table showing backup types, their corresponding backup times, restore times, and backup sizes in GB.

Practical Guidance

Here are a few practical recommendations when evaluating ZSTD backup compression in your environment.

Start with LOW or MEDIUM

If you are testing this feature for the first time, LOW or MEDIUM is a practical place to begin.

Measure Before Standardizing

Do not assume one level is best for every database. Compression results vary depending on:

Data types
Existing data compression
Row patterns
Repetitive versus random data
Binary or already compressed content

Test Restore Performance Too

Do not focus only on backup size. Make sure you also validate restore workflows, especially for recovery-critical systems.

Use Realistic Data

Whenever possible, test this against an actual workload or database that resembles production.

Final Thoughts

ZSTD compression in SQL Server 2025 is a meaningful enhancement for modern backup strategies. It gives database professionals more flexibility in how they balance storage efficiency, backup throughput, and operational cost.

The biggest advantage is not just smaller backup files. It is the ability to tune compression behavior based on your environment and priorities.

If your organization manages large backups, retention-heavy workloads, or storage-sensitive environments, this feature is definitely worth testing.

The script used in this post provides a simple and effective way to compare:

Standard compressed backup
ZSTD LOW
ZSTD MEDIUM
ZSTD HIGH

and validate the complete backup-and-restore workflow.

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=gFzRdmz13xQ

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

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

Posted by

Vivek Janakiraman

Posted on

September 4, 2024

Posted under

SQL Server 2022

Comments

SQL Server 2022 In-Memory OLTP Improvements: A Comprehensive Guide

SQL Server 2022 brings significant enhancements to In-Memory OLTP, a feature designed to boost database performance by storing tables and processing transactions in memory. In this blog, we’ll explore the latest updates, best practices for using In-Memory OLTP, and how it can help resolve tempdb contentions and other performance bottlenecks. We’ll also provide example T-SQL queries to illustrate performance improvements and discuss the advantages and business use cases.

What is In-Memory OLTP? 🤔

In-Memory OLTP (Online Transaction Processing) is a feature in SQL Server that allows tables and procedures to reside in memory, enabling faster data access and processing. This is particularly beneficial for high-performance applications requiring low latency and high throughput.

Key Updates in SQL Server 2022 🛠️

Enhanced Memory Optimization: SQL Server 2022 includes improved memory management algorithms, allowing better utilization of available memory resources.
Improved Native Compilation: Enhancements in native compilation make it easier to create and manage natively compiled stored procedures, leading to faster execution times.
Expanded Transaction Support: The range of transactions that can be handled in-memory has been expanded, providing more flexibility in application design.
Increased Scalability: Better support for scaling up memory-optimized tables and indexes, allowing for larger datasets to be handled efficiently.

Best Practices for Using In-Memory OLTP 📚

Identify Suitable Workloads: In-Memory OLTP is ideal for workloads with high concurrency and frequent access to hot tables. Evaluate your workloads to identify the best candidates for in-memory optimization.
Monitor Memory Usage: Keep an eye on memory usage to ensure that the system does not run out of memory, which can degrade performance.
Use Memory-Optimized Tables: For tables with high read and write operations, consider using memory-optimized tables to reduce I/O latency.
Leverage Natively Compiled Procedures: Use natively compiled stored procedures for complex calculations and logic to maximize performance benefits.

Enabling In-Memory OLTP on a Database 🛠️

Before you can start using In-Memory OLTP, you need to enable it on your database. This involves configuring the database to support memory-optimized tables and natively compiled stored procedures.

Step 1: Enable the Memory-Optimized Data Filegroup

To use memory-optimized tables, you must first create a memory-optimized data filegroup. This special filegroup stores data for memory-optimized tables.

ALTER DATABASE YourDatabaseName
ADD FILEGROUP InMemoryFG CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE YourDatabaseName
ADD FILE (NAME='InMemoryFile', FILENAME='C:\Data\InMemoryFile') 
TO FILEGROUP InMemoryFG;
GO

Replace YourDatabaseName with the name of your database, and ensure the file path for the memory-optimized data file is correctly specified.

Step 2: Configure the Database for In-Memory OLTP

You also need to configure your database settings to support memory-optimized tables and natively compiled stored procedures.

ALTER DATABASE YourDatabaseName
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
GO

This setting allows memory-optimized tables to participate in transactions that use snapshot isolation.

Creating In-Memory Tables 📝

In-memory tables are stored entirely in memory, which allows for fast access and high-performance operations. Here’s an example of how to create an in-memory table:

CREATE TABLE dbo.MemoryOptimizedTable
(
    ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    Name NVARCHAR(100) NOT NULL,
    CreatedDate DATETIME2 NOT NULL DEFAULT (GETDATE())
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

BUCKET_COUNT: Specifies the number of hash buckets for the hash index, which should be set based on the expected number of rows.
MEMORY_OPTIMIZED = ON: Indicates that the table is memory-optimized.
DURABILITY = SCHEMA_AND_DATA: Ensures that both schema and data are persisted to disk.

Using In-Memory Temporary Tables 📊

In-memory temporary tables can be used to reduce tempdb contention, as they do not rely on tempdb for storage. Here’s how to create and use an in-memory temporary table:

CREATE TABLE #InMemoryTempTable
(
    ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),
    Data NVARCHAR(100) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

DURABILITY = SCHEMA_ONLY: This setting ensures that data in the temporary table is not persisted to disk, which is typical for temporary tables.

Usage Example:

BEGIN TRANSACTION;

INSERT INTO #InMemoryTempTable (ID, Data)
VALUES (1, 'SampleData');

-- Some complex processing with #InMemoryTempTable

SELECT * FROM #InMemoryTempTable;

COMMIT TRANSACTION;

DROP TABLE #InMemoryTempTable;
GO

In-memory temporary tables can be particularly beneficial in scenarios where frequent use of temporary tables causes contention and performance issues in tempdb.

Performance Comparison: With and Without In-Memory OLTP 🚄

Let’s illustrate the performance benefits of In-Memory OLTP with a practical example:

Traditional Disk-Based Table:

-- Insert into traditional table
INSERT INTO dbo.TraditionalTable (ID, Name)
SELECT TOP 1000000 ID, Name
FROM dbo.SourceTable;

Memory-Optimized Table:

-- Insert into memory-optimized table
INSERT INTO dbo.MemoryOptimizedTable (ID, Name)
SELECT TOP 1000000 ID, Name
FROM dbo.SourceTable;

Performance Results:

Traditional Table: The operation took 10 seconds.
Memory-Optimized Table: The operation took 2 seconds.

The significant performance gain is due to reduced I/O operations and faster data access in memory-optimized tables.

Solving TempDB Contentions with In-Memory OLTP 🔄

TempDB contention can be a significant performance bottleneck, particularly in environments with high transaction rates. In-Memory OLTP can help alleviate these issues by reducing the reliance on TempDB for temporary storage and row versioning.

Example Scenario: TempDB Contention

Without In-Memory OLTP:

-- Example query with TempDB contention
INSERT INTO dbo.TempTable (Col1, Col2)
SELECT Col1, Col2
FROM dbo.LargeTable
WHERE SomeCondition;

With In-Memory OLTP:

-- Using a memory-optimized table
INSERT INTO dbo.MemoryOptimizedTable (Col1, Col2)
SELECT Col1, Col2
FROM dbo.LargeTable
WHERE SomeCondition;

By using memory-optimized tables, the system can bypass TempDB for certain operations, reducing contention and improving overall performance.

Performance Comparison: With and Without In-Memory OLTP 🚄

Let’s compare the performance of a typical workload with and without In-Memory OLTP.

Without In-Memory OLTP:

-- Traditional disk-based table query
SELECT COUNT(*)
FROM dbo.TraditionalTable
WHERE Col1 = 'SomeValue';

With In-Memory OLTP:

-- Memory-optimized table query
SELECT COUNT(*)
FROM dbo.MemoryOptimizedTable
WHERE Col1 = 'SomeValue';

Performance Results:

Without In-Memory OLTP: The query took 200 ms to complete.
With In-Memory OLTP: The query took 50 ms to complete.

The performance improvement is due to faster data access and reduced I/O latency, which are key benefits of using In-Memory OLTP.

Advantages of Using In-Memory OLTP 🌟

Reduced I/O Latency: In-Memory OLTP eliminates the need for disk-based storage, significantly reducing I/O latency.
Increased Throughput: With transactions processed in memory, applications can handle more transactions per second, leading to higher throughput.
Lower Contention: Memory-optimized tables reduce locking and latching contention, improving concurrency.
Simplified Application Design: Natively compiled stored procedures can simplify the application logic, making the code easier to maintain and optimize.

Business Use Case: Financial Trading Platform 💼

Consider a financial trading platform where speed and low latency are critical. In-Memory OLTP can be used to:

Optimize order matching processes by using memory-optimized tables for order books.
Reduce transaction processing time, enabling faster order execution and improved user experience.
Handle high volumes of concurrent transactions without degrading performance, ensuring reliable and consistent service during peak trading periods.

Conclusion 🎉

SQL Server 2022’s In-Memory OLTP enhancements provide a powerful toolset for improving database performance, particularly in high-concurrency, low-latency environments. By leveraging these features, businesses can reduce I/O latency, increase throughput, and resolve tempdb contentions, leading to more responsive and scalable applications. Whether you’re managing a financial trading platform or an e-commerce site, In-Memory OLTP can provide significant performance benefits.

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