How to Set Up DHCP Server Authorisation on Windows Server 2022

How to Configure SQL Server Performance Tuning on Windows Server 2022

SQL Server performance tuning on Windows Server 2022 is a disciplined process that involves identifying bottlenecks through Dynamic Management Views (DMVs), analyzing execution plans, configuring server-level settings, and maintaining indexes and statistics. This guide covers the most impactful performance tuning techniques for SQL Server 2019 and 2022, from wait statistics analysis and missing index identification to TempDB optimization and blocking resolution.

Understanding Wait Statistics

Wait statistics are the foundation of SQL Server performance analysis. Every time SQL Server cannot immediately execute a task, it records a wait. Analyzing which wait types dominate your workload tells you exactly where the bottleneck is — whether it is disk I/O, CPU, memory, network, or locking.

Query the sys.dm_os_wait_stats DMV to see accumulated wait statistics since the last server restart:

SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(5,2)) AS pct_total_wait
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- Filter out benign background waits
    'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SLEEP_DBSTARTUP', 'SLEEP_DBTASK',
    'SLEEP_TEMPDBSTARTUP', 'SNI_HTTP_ACCEPT', 'DISPATCHER_QUEUE_SEMAPHORE',
    'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
    'CLR_MANUAL_EVENT', 'WAITFOR', 'LAZYWRITER_SLEEP', 'SLEEP_MASTERUPGRADED',
    'SLEEP_MASTERMDREADY', 'SLEEP_MASTERSTARTED', 'SLEEP_MASTERDBREADY',
    'SLEEP_TEMPDBSTARTUP', 'SNI_HTTP_ACCEPT', 'DBMIRROR_EVENTS_QUEUE',
    'SQLTRACE_BUFFER_FLUSH', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_DISPATCHER_WAIT',
    'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'CHECKPOINT_QUEUE',
    'BROKER_EVENTHANDLER', 'XE_TIMER_EVENT', 'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
    'HADR_WORK_QUEUE', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
)
ORDER BY wait_time_ms DESC;

Common actionable wait types and their meanings: PAGEIOLATCH_SH and PAGEIOLATCH_EX indicate disk I/O waits reading data pages — usually a sign of insufficient memory (buffer pool too small) or slow storage. CXPACKET and CXCONSUMER indicate parallel query waits, which may require MAXDOP tuning. LCK_M_* waits indicate locking and blocking between sessions. SOS_SCHEDULER_YIELD indicates CPU pressure. ASYNC_NETWORK_IO indicates the client is not consuming results fast enough. WRITELOG indicates transaction log write waits, often caused by slow storage or high write volume.

Identifying Missing Indexes with DMVs

SQL Server records information about indexes that the query optimizer wished existed. Query these DMVs to find high-impact missing indexes:

SELECT TOP 25
    DB_NAME(mid.database_id) AS DatabaseName,
    OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' 
        + REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_') + '_' 
        + REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_') + '] '
        + 'ON ' + mid.statement 
        + ' (' + ISNULL(mid.equality_columns, '') 
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL 
               THEN ', ' ELSE '' END
        + ISNULL(mid.inequality_columns, '') + ')'
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups AS mig
JOIN sys.dm_db_missing_index_group_stats AS migs 
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid 
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

Do not blindly create every index suggested by this DMV. Review them for relevance, check for overlapping existing indexes, and consider the write overhead each new index introduces. Test index additions under a realistic workload before applying to production.

Query Store

Query Store was introduced in SQL Server 2016 and automatically captures query plans, execution statistics, and runtime performance data. It is invaluable for identifying query plan regressions. Enable it on a database:

ALTER DATABASE appdb
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);

View the top resource-consuming queries:

SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    SUM(rs.avg_duration) AS total_avg_duration_us,
    SUM(rs.avg_cpu_time) AS total_avg_cpu_us,
    SUM(rs.avg_logical_io_reads) AS total_avg_logical_reads,
    SUM(rs.count_executions) AS total_executions,
    MAX(rs.last_execution_time) AS last_execution
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_avg_cpu_us DESC;

When a query plan regression is detected (a query suddenly performing worse after a parameter change or statistics update), use Query Store to force the last good plan:

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

Execution Plan Analysis

Execution plans show exactly how SQL Server executes a query. In SSMS (SQL Server Management Studio), turn on actual execution plan capture before running a query with Ctrl+M, or include the following in a query batch:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'pending'
  AND o.order_date >= '2024-01-01';

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

STATISTICS IO output shows logical reads per table — a high number indicates the query is doing a lot of work and likely needs an index. In the graphical execution plan, look for: Table Scan or Index Scan operators (full scans are expensive on large tables), thick arrows between operators (indicating large row estimates that may overflow TempDB), operators with a high cost percentage, and yellow warning triangles indicating implicit conversions or missing statistics.

For stored procedures with plan caching issues (parameter sniffing), view cached plans:

SELECT 
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_worker_time / qs.execution_count AS avg_cpu_us,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_logical_reads DESC;

MAXDOP and Cost Threshold for Parallelism

Max Degree of Parallelism (MAXDOP) controls how many CPU cores a single query can use for parallel execution. The default of 0 (unlimited) can cause excessive parallelism on servers with many cores, leading to high CXPACKET waits and reduced concurrency.

Microsoft’s current guidance for MAXDOP on SQL Server 2019 and 2022:

-- For servers with  8 logical processors:
-- MAXDOP = 8 (general recommendation)

-- For NUMA-aware configuration:
-- MAXDOP = number of logical processors per NUMA node (max 8)

-- Set server-level MAXDOP
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

Cost Threshold for Parallelism determines the minimum estimated cost at which SQL Server considers a parallel plan. The default of 5 is extremely low for modern hardware — most queries that cost 5 on today’s servers run in milliseconds and do not benefit from parallelism. Raise it to a value between 25 and 50 to reduce unnecessary parallelism:

EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

Max Server Memory Configuration

SQL Server’s buffer pool will grow to consume all available memory if max server memory is not set. This can starve the operating system and other processes, causing paging. Always set max server memory explicitly.

Leave at least 10% of RAM (or a minimum of 4 GB, whichever is larger) for the OS and other processes:

-- Example: 32 GB server, leave 4 GB for OS = 28 GB for SQL Server
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max server memory (MB)', 28672;  -- 28 GB in MB
RECONFIGURE;

-- Verify the setting
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = 'max server memory (MB)';

TempDB Configuration

TempDB is a shared resource used by all databases for temporary tables, work tables, row versioning (RCSI), and index rebuild operations. Contention on TempDB allocation pages (SGAM, GAM, PFS) was a significant bottleneck in earlier SQL Server versions. Best practices for TempDB:

Create one TempDB data file per logical CPU core, up to 8 files. All files should be of equal size. SQL Server 2016 and later configure this during setup if you check the recommended settings, but verify it post-installation:

-- Check current TempDB files
USE tempdb;
SELECT name, physical_name, size * 8 / 1024 AS size_mb, type_desc
FROM sys.database_files;

-- Add TempDB data files to match CPU count (example: adding 3 more for 4 total)
ALTER DATABASE tempdb
ADD FILE (NAME = N'tempdev2', FILENAME = N'D:TempDBtempdev2.ndf', 
          SIZE = 1024MB, FILEGROWTH = 256MB);
ALTER DATABASE tempdb
ADD FILE (NAME = N'tempdev3', FILENAME = N'D:TempDBtempdev3.ndf', 
          SIZE = 1024MB, FILEGROWTH = 256MB);
ALTER DATABASE tempdb
ADD FILE (NAME = N'tempdev4', FILENAME = N'D:TempDBtempdev4.ndf', 
          SIZE = 1024MB, FILEGROWTH = 256MB);

Place TempDB data files on a fast, dedicated drive (NVMe SSD or a dedicated volume). Never place TempDB on the OS drive. Pre-size TempDB files to avoid autogrowth events during peak usage.

Enable trace flag 1118 (forces uniform extent allocations for TempDB) if on SQL Server 2014 or earlier. On SQL Server 2016+, this is the default behavior and no trace flag is needed.

Locking and Blocking Analysis

Blocking occurs when one session holds a lock that another session is waiting for. Use sys.dm_exec_requests to identify blocking chains:

SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_seconds,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS current_statement,
    s.login_name,
    s.host_name,
    s.program_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

To see the SQL text the blocking session is currently executing or last executed:

SELECT 
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_start_time,
    SUBSTRING(st.text, 1, 500) AS last_sql_text
FROM sys.dm_exec_sessions s
CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) st
WHERE s.session_id = 55; -- Replace with the blocking session_id

Consider enabling Read Committed Snapshot Isolation (RCSI) on databases with heavy read/write contention. RCSI allows readers to not block writers and vice versa by using row versioning in TempDB:

ALTER DATABASE appdb SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE appdb SET READ_COMMITTED_SNAPSHOT ON;

Index Maintenance

Indexes become fragmented as data is inserted, updated, and deleted. Fragmentation above 30% typically warrants a REBUILD; between 10% and 30%, a REORGANIZE. Check fragmentation levels:

SELECT 
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id 
                   AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Rebuild or reorganize as appropriate:

-- Reorganize (online, low-impact)
ALTER INDEX IX_orders_customer_id ON dbo.orders REORGANIZE;

-- Rebuild (can be done online in Enterprise Edition)
ALTER INDEX IX_orders_customer_id ON dbo.orders REBUILD WITH (ONLINE = ON);

-- Rebuild all indexes on a table
ALTER INDEX ALL ON dbo.orders REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);

Updating Statistics

Out-of-date statistics cause the query optimizer to make poor cardinality estimates, leading to suboptimal plans. SQL Server updates statistics automatically by default, but heavily modified tables may benefit from manual updates:

-- Update all statistics on a table
UPDATE STATISTICS dbo.orders WITH FULLSCAN;

-- Update all statistics in the database (can be resource-intensive)
EXEC sp_updatestats;

-- Check statistics age and modification counter
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stat_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.orders')
ORDER BY sp.modification_counter DESC;

DBCC CHECKDB

DBCC CHECKDB verifies the structural and logical integrity of all objects in a database. It should be run regularly (weekly for most databases) to detect and prevent corruption from escalating:

-- Run a full integrity check (can be resource-intensive on large databases)
DBCC CHECKDB (appdb) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Check only physical page structure (faster, less thorough)
DBCC CHECKDB (appdb) WITH PHYSICAL_ONLY, NO_INFOMSGS;

-- Check a single table
DBCC CHECKTABLE ('dbo.orders') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Run DBCC CHECKDB during a maintenance window. For very large databases (multi-terabyte), consider using PHYSICAL_ONLY during regular schedules and running the full check less frequently, or use Availability Group read replicas to offload CHECKDB from the primary.

Summary

SQL Server performance tuning on Windows Server 2022 is a methodical process that begins with wait statistics analysis to identify the dominant bottleneck, followed by targeted interventions. Missing index DMVs and Query Store guide index and query optimization. Server-level settings — MAXDOP, cost threshold for parallelism, max server memory, and TempDB file count — establish the foundation for efficient resource utilization. Ongoing maintenance through index rebuilds, statistics updates, and regular DBCC CHECKDB checks ensures sustained performance and data integrity. Each tuning decision should be validated with before-and-after metrics to confirm improvement.