Optimization Techniques for Microsoft SQL Server 2022: A Comprehensive Scientific Analysis

A scientific analysis of performance optimisation in Microsoft SQL Server 2022: query cost models, Intelligent Query Processing, indexing, TempDB, memory, storage and monitoring — with ready-to-run T-SQL experiments.

Abstract

Microsoft SQL Server 2022 introduces a broad range of technological advancements, including improvements in query optimization, memory management, indexing, intelligent query processing, In-Memory OLTP and hybrid cloud features. As organizations continue to generate unprecedented volumes of data, optimizing database systems remains a critical requirement for high performance, scalability and reliability in both transactional and analytical environments.

This paper provides a scientific analysis of performance optimization in SQL Server 2022, synthesizing theoretical foundations, practical tuning methodologies and architectural best practices. It presents mathematical models of query cost estimation, memory allocation and data distribution, together with tables summarizing optimization metrics, comparative performance outcomes and recommended indexing strategies. The findings highlight the indispensable role of Intelligent Query Processing (IQP), Parameter Sensitive Plan Optimization, TempDB tuning, columnstore indexes and buffer management in improving performance.

1. Introduction

The rapid evolution of enterprise information systems and the exponential growth of datasets place considerable demands on relational database engines. SQL Server 2022, the latest major version of Microsoft’s flagship RDBMS, incorporates advanced query optimization, adaptive plan adjustments, extended memory and storage optimization models, and enhanced cloud integration.

Performance optimization is no longer limited to manual indexing and query rewrites — it now involves intelligent engine features, deep architectural tuning and adaptive behaviour during execution. This article combines theoretical models with practical techniques used in high-load transactional systems (OLTP), analytical environments (OLAP) and hybrid architectures.

2. Background and theoretical foundations

Optimization in SQL Server rests on three fundamental pillars:

1. Query optimization and execution plans
2. Memory and storage management
3. Indexing and data modelling

SQL Server 2022 uses a cost-based optimizer. It looks for the query plan with the lowest estimated cost:

Cost(Q) = Σ Cᵢ   (i = 1 … n)

Q  — query
Cᵢ — estimated cost of an individual operator (scan, seek, sort, join, aggregate)

A more detailed model for I/O-bound operations is:

C_IO = α · P_read + β · P_write

P_read  — number of logical reads
P_write — number of writes
α, β    — weight factors reflecting device latency

SQL Server 2022 extends these models with adaptive mechanisms in the Intelligent Query Processing framework.

3. Intelligent Query Processing (IQP) in SQL Server 2022

3.1 Batch mode on rowstore

Batch execution, originally reserved for columnstore indexes, now also supports rowstore operations such as aggregations, window functions, sorts and joins. Batch mode significantly reduces CPU usage through vectorized processing.

3.2 Approximate query processing

For large analytical queries, approximate functions such as APPROX_COUNT_DISTINCT and approximate percentile functions can cut execution time from minutes to seconds while keeping statistical accuracy within acceptable bounds.

3.3 Parameter Sensitive Plan Optimization (PSPO)

SQL Server can generate multiple execution plans for different parameter value distributions. For a parameter P with data distribution D(P), the engine maintains:

{ Plan₁ (P ∈ D₁), Plan₂ (P ∈ D₂), …, Planₖ (P ∈ Dₖ) }

This feature addresses the long-standing parameter sniffing problem, where a single cached plan performs well for some parameter values and poorly for others.

4. Data modelling and schema optimization

Correct schema design remains the foundation of performance.

4.1 Normalization vs. denormalization

Normalization (1NF, 2NF, 3NF) reduces redundancy and ensures structural integrity, but highly normalized models may require excessive joins. Denormalization can accelerate read-heavy workloads.

4.2 Datatype optimization

Datatype selection directly affects storage footprint:

4.3 Table partitioning

Partitioning strategies include range partitioning (time-based), list partitioning (categories) and hash partitioning (distribution). Formally, partitioning splits table T into disjoint parts:

T = P₁ ∪ P₂ ∪ … ∪ Pₖ,   where Pᵢ ∩ Pⱼ = ∅ for i ≠ j

5. Index optimization

Indexing is the most impactful optimization method.

5.1 Index types

SQL Server 2022 supports clustered, nonclustered, columnstore, filtered and memory-optimized indexes.

Experiment 1: the impact of a nonclustered index. The script below compares the cost of the same query before and after creating a nonclustered index with INCLUDE columns. Compare the SET STATISTICS IO, TIME output and the execution plans for both runs.

-- Experiment 1: impact of a nonclustered index on performance

-- 1. Test table (simulating a transactional table)
IF OBJECT_ID('dbo.SalesBig', 'U') IS NOT NULL
    DROP TABLE dbo.SalesBig;
GO

CREATE TABLE dbo.SalesBig
(
    SalesId     BIGINT        IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerId  INT           NOT NULL,
    OrderDate   DATETIME2(0)  NOT NULL,
    Amount      DECIMAL(18,2) NOT NULL,
    Status      CHAR(1)       NOT NULL
);
GO

-- 2. Load test data (1 million rows)
INSERT INTO dbo.SalesBig (CustomerId, OrderDate, Amount, Status)
SELECT TOP (1000000)
    ABS(CHECKSUM(NEWID())) % 50000                                  AS CustomerId,
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, SYSDATETIME())       AS OrderDate,
    CAST(ABS(CHECKSUM(NEWID())) % 100000 / 100.0 AS DECIMAL(18,2))   AS Amount,
    CASE WHEN ABS(CHECKSUM(NEWID())) % 10 = 0 THEN 'R' ELSE 'C' END  AS Status
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
GO

-- 3. Baseline query WITHOUT the index
SET STATISTICS IO, TIME ON;
GO
SELECT  CustomerId, SUM(Amount) AS TotalAmount
FROM    dbo.SalesBig
WHERE   Status = 'C'
  AND   OrderDate >= DATEADD(DAY, -30, SYSDATETIME())
GROUP BY CustomerId;
GO
SET STATISTICS IO, TIME OFF;
GO

-- 4. Create a nonclustered index with INCLUDE
CREATE NONCLUSTERED INDEX IX_SalesBig_Status_OrderDate
ON dbo.SalesBig (Status, OrderDate)
INCLUDE (Amount, CustomerId);
GO

-- 5. The same query AFTER creating the index
SET STATISTICS IO, TIME ON;
GO
SELECT  CustomerId, SUM(Amount) AS TotalAmount
FROM    dbo.SalesBig
WHERE   Status = 'C'
  AND   OrderDate >= DATEADD(DAY, -30, SYSDATETIME())
GROUP BY CustomerId;
GO
SET STATISTICS IO, TIME OFF;
GO

For analytical workloads, columnstore indexes can be many times faster than rowstore — the illustrative comparison in the original notebook assumes a 12× relative speed-up.

5.2 Fragmentation and maintenance

Fragmentation affects read performance. A common rule of thumb:

ALTER INDEX ALL ON TableName REBUILD;

The illustrative data below shows how query time grows with fragmentation:

5.3 Index selectivity

Selectivity is one of the key metrics determining whether an index will be effective:

S = number of distinct values / total number of rows

High selectivity (close to 1.0) means the column contains mostly unique values, so a nonclustered index will likely perform very well. Low selectivity (close to 0) means mostly repeated values, which makes an index far less useful.

Experiment 2: measuring fragmentation and selectivity.

-- Experiment 2: measuring index fragmentation and selectivity

-- Physical index fragmentation
SELECT
    DB_NAME(ips.database_id)         AS DatabaseName,
    OBJECT_NAME(ips.object_id)       AS TableName,
    i.name                           AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
    ON ips.object_id = i.object_id
   AND ips.index_id  = i.index_id
WHERE ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;
GO

-- Approximate selectivity based on statistics
SELECT
    OBJECT_NAME(s.object_id)   AS TableName,
    i.name                     AS IndexName,
    s.stats_id,
    sp.rows,
    sp.rows_sampled,
    sp.steps,
    CAST(sp.rows_sampled AS FLOAT) / NULLIF(sp.rows, 0) AS SampleRatio
FROM sys.stats AS s
JOIN sys.indexes AS i
    ON s.object_id = i.object_id
   AND s.stats_id  = i.index_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('dbo.SalesBig');
GO

6. TempDB optimization

TempDB is one of the most heavily used databases in SQL Server. Sorting, hashing, row versioning, intermediate query results, triggers and temporary tables all depend on its performance.

6.1 Best practices

TempDB configuration diagnostics:

-- TempDB files and their sizes
SELECT
    name,
    type_desc,
    size * 8 / 1024   AS SizeMB,
    max_size,
    growth * 8 / 1024 AS GrowthMB,
    is_percent_growth
FROM tempdb.sys.database_files;
GO

-- Active allocations in TempDB
SELECT
    session_id,
    task_allocations_page_count,
    task_deallocations_page_count
FROM sys.dm_db_task_space_usage
ORDER BY task_allocations_page_count DESC;
GO

Illustrative throughput depending on the number of TempDB data files:

6.2 A model of TempDB contention

When allocations are spread across multiple files, the probability of contention decreases roughly in proportion to the number of files:

C ≈ 1 / F

C — contention probability
F — number of TempDB data files

More data files reduce latch contention and improve concurrency.

7. Memory optimization

SQL Server 2022 provides advanced memory features, including In-Memory OLTP, buffer management improvements and hybrid memory extensions.

7.1 In-Memory OLTP

In-Memory OLTP eliminates latches and locks through multi-version concurrency control (MVCC). Memory-optimized tables can achieve:

• 5×–30× faster inserts,
• around 10× faster reads,
• predictable latency under heavy concurrency.

Illustrative comparison from the notebook:

7.2 Buffer pool and caching model

A simplified buffer pool hit ratio model:

H ≈ M / D

H — buffer pool hit ratio
M — available memory
D — dataset size

If M ≈ D, most reads are served from RAM and performance is excellent. If M ≪ D, SQL Server performs more physical reads and performance drops.

7.3 Buffer Pool Extension (BPE)

Buffer Pool Extension lets SQL Server use an SSD as an extension of RAM. It helps when memory is limited, although it remains slower than real RAM.

8. Storage optimization

Storage is one of the most critical aspects of SQL Server performance.

8.1 SSD and NVMe

Low-latency SSD/NVMe storage reduces overall I/O cost (C_IO ∝ latency). NVMe drives can reach microsecond latencies, drastically improving checkpoint performance, TempDB throughput, and sort and hash operations.

8.2 Compression

SQL Server supports row, page and columnstore compression. Compression reduces table size, disk I/O and memory consumption — at the cost of potentially higher CPU usage.

8.3 Instant File Initialization

With IFI enabled, SQL Server can grow data files without zeroing out disk space, which speeds up autogrowth, database creation and restore operations.

9. Monitoring tools and DMVs

9.1 Dynamic Management Views

Experiment 3: analysing the main waits in the system.

-- Experiment 3: top waits in the system
WITH Waits AS
(
    SELECT
        wait_type,
        wait_time_ms - signal_wait_time_ms AS resource_wait_ms,
        signal_wait_time_ms                AS signal_wait_ms
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN
    (
        'SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH',
        'BROKER_TASK_STOP','BROKER_TO_FLUSH','BROKER_EVENTHANDLER',
        'XE_TIMER_EVENT','XE_DISPATCHER_WAIT','BROKER_RECEIVE_WAITFOR',
        'ONDEMAND_TASK_QUEUE','FT_IFTS_SCHEDULER_IDLE_WAIT',
        'BROKER_TRANSMITTER','CHECKPOINT_QUEUE',
        'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','LAZYWRITER_SLEEP',
        'SLEEP_BPOOL_FLUSH'
    )
)
SELECT TOP (10)
    wait_type,
    resource_wait_ms / 1000.0                    AS resource_wait_s,
    signal_wait_ms   / 1000.0                    AS signal_wait_s,
    (resource_wait_ms + signal_wait_ms) / 1000.0 AS total_wait_s
FROM Waits
ORDER BY total_wait_s DESC;
GO

9.2 Query Store

Query Store captures query plans, regressions and runtime metrics. It allows you to force a previous plan when SQL Server produces a suboptimal one.

9.3 Extended Events

Extended Events provide a flexible, lightweight tracing subsystem that replaces SQL Profiler.

10. Comparison of optimization techniques

Approximate performance gains for key optimization features:

11. Conclusion

SQL Server 2022 represents a new generation of intelligent database optimization. By combining advanced indexing, intelligent query processing, improved memory and storage management, TempDB optimization and sophisticated monitoring tools, organizations can achieve significant performance gains in both OLTP and OLAP workloads.

Key takeaways:

1. Intelligent Query Processing removes many historical performance bottlenecks.
2. Proper indexing remains the foundation of performance tuning.
3. TempDB and storage configuration significantly influence overall throughput.
4. Modern hardware (NVMe, multi-core CPUs) works in synergy with SQL Server 2022 features.
5. Adaptive and automatic features reduce the need for manual optimization.

Bibliography

1. Microsoft Corporation, SQL Server 2022 Technical Documentation, 2023.
2. K. Tripp, SQL Server Query Performance Tuning, O’Reilly Media, 2022.
3. I. Ben-Gan, T-SQL Querying and Query Tuning, Microsoft Press, 2020.
4. P. Larsson, “Columnstore Index Strategies,” SQLSkills, 2022.
5. G. Lewin, “Adaptive Query Processing in SQL Server,” MSDN Magazine, 2021.
6. ISO/IEC 9075-1, SQL Standard, 2016.

The charts in the original notebook use illustrative sample data; the tables above reproduce those values. The full notebook with runnable code is available in Google Colab (link in the sidebar).