How to Configure SQL Server Performance Tuning on Windows Server 2025

How to Configure SQL Server Performance Tuning on Windows Server 2025

Installing SQL Server is only the beginning of the journey. A default SQL Server installation on Windows Server 2025 uses conservative settings designed for broad compatibility rather than peak throughput, leaving significant performance potential untapped. Database administrators who invest time in performance tuning — establishing a baseline, correcting memory limits, configuring parallelism, maintaining indexes and statistics, and leveraging Query Store — often achieve dramatic throughput improvements and latency reductions without changing a single line of application code. This guide covers the essential SQL Server performance tuning steps for SQL Server 2022 (or 2019) on Windows Server 2025, from initial baselining through to execution plan analysis.

Prerequisites

• SQL Server 2022 (or 2019) installed on Windows Server 2025
• SQL Server Management Studio (SSMS) 19+ or Azure Data Studio installed
• Membership in the sysadmin fixed server role or equivalent
• A working database with a representative workload (ideally at least one hour of production traffic captured for baselining)
• Basic familiarity with T-SQL and the SQL Server object model

Step 1 — Establish a Performance Baseline

Before changing anything, capture a baseline. You cannot measure improvement without knowing where you started. The Dynamic Management Views (DMVs) provide real-time performance counters accumulated since SQL Server last restarted.

Identify Top Wait Statistics

Wait statistics reveal what SQL Server is spending time waiting for. This is the single most powerful diagnostic tool in SQL Server:

-- Top wait types since last restart (exclude benign background waits)
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(5,2)) AS pct_total
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- Exclude known benign waits
    'SLEEP_TASK','SLEEP_DBSTARTUP','SLEEP_DBTASK','SLEEP_TEMPDBSTARTUP',
    'SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED',
    'SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP',
    'WAITFOR','WAIT_XTP_OFFLINE_CKPT_NEW_LOG','BROKER_TO_FLUSH',
    'BROKER_EVENTHANDLER','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE',
    'SERVER_IDLE_CHECK','SLEEP_DBSTARTUP','SLEEP_DBTASK','SNI_HTTP_ACCEPT',
    'SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    'WAITFOR','XE_DISPATCHER_WAIT','XE_TIMER_EVENT','BROKER_RECEIVE_WAITFOR',
    'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_WORK_QUEUE','LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',
    'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
    'SLEEP_DBSTARTUP','SLEEP_DBTASK','SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY',
    'SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP',
    'SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH',
    'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','TASK_MANAGER_SLEEP','XE_DISPATCHER_WAIT','XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

Identify Top CPU-Consuming Queries

-- Top 20 queries by total CPU time since last reset
SELECT TOP 20
    qs.total_worker_time / 1000                    AS total_cpu_ms,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count    AS avg_logical_reads,
    SUBSTRING(qt.text,
              (qs.statement_start_offset / 2) + 1,
              (CASE qs.statement_end_offset
                   WHEN -1 THEN DATALENGTH(qt.text)
                   ELSE qs.statement_end_offset END
               - qs.statement_start_offset) / 2 + 1) AS query_text,
    DB_NAME(qt.dbid)                               AS database_name,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Check Key Performance Counters

-- Critical SQL Server performance counters
SELECT
    object_name,
    counter_name,
    instance_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Buffer cache hit ratio',          -- should be > 95%
    'Page life expectancy',            -- should be > 300 (ideally thousands)
    'Batch Requests/sec',              -- baseline: measure at peak load
    'SQL Compilations/sec',            -- high values = too many ad hoc queries
    'SQL Re-Compilations/sec',         -- should be  0 warrants investigation
    'Checkpoint pages/sec',            -- indicates I/O checkpoint pressure
    'Lazy writes/sec'                  -- > 20/sec = memory pressure
)
ORDER BY object_name, counter_name;

Step 2 — Configure Maximum Server Memory

The most impactful single configuration change for most SQL Server instances: by default, SQL Server will consume all available memory on the OS, leaving nothing for the operating system and other processes, which causes paging and performance collapse.

-- View current memory configuration
EXEC sp_configure 'show advanced options';
EXEC sp_configure 'max server memory (MB)';

-- Enable advanced options and set max server memory
-- Rule of thumb: leave ~10% or at least 4 GB for the OS
-- Example: 64 GB server → set max server memory to 56 GB (57,344 MB)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max server memory (MB)', 57344;
RECONFIGURE;

-- Also set a minimum to prevent SQL Server from releasing too much memory
EXEC sp_configure 'min server memory (MB)', 4096;
RECONFIGURE;

-- Verify
EXEC sp_configure 'max server memory (MB)';
EXEC sp_configure 'min server memory (MB)';

Check current memory allocation at runtime:

-- Memory breakdown: Buffer Pool, in-use, and available
SELECT
    physical_memory_in_use_kb / 1024       AS memory_used_mb,
    locked_page_allocations_kb / 1024      AS locked_pages_mb,
    page_fault_count,
    memory_utilization_percentage
FROM sys.dm_os_process_memory;

-- Buffer pool page life expectancy (PLE) — key health indicator
SELECT
    object_name,
    counter_name,
    cntr_value AS page_life_expectancy_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer Manager%';

Step 3 — Configure MAXDOP and Cost Threshold for Parallelism

Parallelism allows SQL Server to spread a single query across multiple CPU cores. Misconfigured parallelism is a leading cause of performance issues — both using too many cores (CXPACKET waits) and not enough (under-utilised hardware).

-- MAXDOP: Maximum Degree of Parallelism
-- Microsoft recommendation for Windows Server 2025 (NUMA-aware):
--   For NUMA: MAXDOP = number of logical CPUs per NUMA node (max 8)
--   For non-NUMA: MAXDOP = number of logical CPUs (max 8)
--   For OLTP (many short queries): MAXDOP = 1 or 2
--   For DW/analytics (few complex queries): MAXDOP = higher value

-- Check NUMA topology
SELECT
    node_id,
    node_state_desc,
    memory_node_id,
    processor_group,
    online_scheduler_count
FROM sys.dm_os_nodes
WHERE node_state_desc != 'ONLINE DAC';

-- Set MAXDOP (example: 8-core non-NUMA server → MAXDOP 8)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

-- Cost Threshold for Parallelism: the query cost above which parallelism is considered
-- Default is 5 (extremely low — almost any query uses parallelism)
-- Recommended starting point: 25-50 for OLTP, leave lower for DW
EXEC sp_configure 'cost threshold for parallelism', 40;
RECONFIGURE;

-- Verify both settings
EXEC sp_configure 'max degree of parallelism';
EXEC sp_configure 'cost threshold for parallelism';

Step 4 — Index Maintenance: Rebuild and Reorganise

Fragmented indexes slow down both reads (more I/O per page) and writes (page splits). Regular index maintenance is essential for sustained performance.

Identify Fragmented Indexes

-- Find fragmented indexes in a specific database
USE YourDatabase;

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,
    ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(),        -- current database
    NULL,           -- all tables
    NULL,           -- all indexes
    NULL,           -- all partitions
    'LIMITED'       -- LIMITED mode is fast; use DETAILED for exact fragmentation
) AS ips
INNER JOIN sys.indexes AS i
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10   -- only show fragmented indexes
  AND ips.page_count > 1000                   -- ignore tiny indexes
ORDER BY ips.avg_fragmentation_in_percent DESC;

Rebuild or Reorganise Based on Fragmentation Level

-- REORGANIZE: online, incremental, minimal locking; use for 10-30% fragmentation
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;

-- REBUILD: more thorough, updates statistics, compacts pages; use for > 30% fragmentation
-- ONLINE = ON allows the table to remain accessible during the rebuild (Enterprise/Developer edition)
ALTER INDEX IX_Orders_OrderDate ON dbo.Orders
    REBUILD WITH (ONLINE = ON, FILLFACTOR = 90, SORT_IN_TEMPDB = ON);

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

-- Generate dynamic rebuild script for all fragmented indexes
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CASE
    WHEN avg_fragmentation_in_percent BETWEEN 10 AND 30
        THEN N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(ips.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(ips.object_id)) + N' REORGANIZE;' + CHAR(13)
    WHEN avg_fragmentation_in_percent > 30
        THEN N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(ips.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(ips.object_id)) + N' REBUILD WITH (ONLINE = ON, FILLFACTOR = 90);' + CHAR(13)
    END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 10 AND page_count > 1000 AND i.name IS NOT NULL;

PRINT @sql;
-- EXEC sp_executesql @sql;  -- Uncomment to execute

Step 5 — Update Statistics

The SQL Server query optimiser relies on statistics (histograms) to estimate row counts and choose execution plans. Stale statistics lead to poor plan choices, table scans where seeks are possible, and memory grant miscalculations.

-- Update statistics on a specific table (with full scan for accuracy)
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Update all statistics in the current database
EXEC sp_updatestats;     -- updates only statistics that need updating (fast)

-- Force full scan update of all statistics (more accurate, slower)
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN';

-- Check when statistics were last updated
SELECT
    OBJECT_NAME(s.object_id)    AS table_name,
    s.name                      AS stats_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter,
    CAST(100.0 * sp.rows_sampled / NULLIF(sp.rows, 0) AS DECIMAL(5,1)) AS sample_pct
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECT_NAME(s.object_id) NOT LIKE 'sys%'
ORDER BY sp.modification_counter DESC;

Step 6 — Enable and Use Query Store

Query Store, introduced in SQL Server 2016 and greatly enhanced in 2022, captures query plans and performance metrics over time. It is the essential tool for detecting plan regression — when a good plan is replaced by a bad one due to statistics changes, parameter sniffing, or system updates.

-- Enable Query Store on a database
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,        -- captures data actively
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 500,
    QUERY_CAPTURE_MODE = AUTO,          -- AUTO captures only significant queries
    SIZE_BASED_CLEANUP_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200
);

-- Check Query Store status
SELECT
    name,
    is_query_store_on,
    desired_state_desc,
    actual_state_desc,
    query_capture_mode_desc,
    size_based_cleanup_mode_desc,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.databases
WHERE name = 'YourDatabase';

Find regressed queries using Query Store:

-- Identify top resource-consuming queries in Query Store (last 24 hours)
USE YourDatabase;

SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    rs.avg_cpu_time / 1000              AS avg_cpu_ms,
    rs.avg_duration / 1000              AS avg_duration_ms,
    rs.avg_logical_io_reads,
    rs.count_executions,
    rs.last_execution_time,
    p.query_plan
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_cpu_time DESC;

-- Force a specific good plan when regression is detected
-- (Replace plan_id with the ID of the known-good plan)
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

-- Unforce a plan (let the optimiser choose again)
EXEC sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;

Step 7 — Reading and Interpreting Execution Plans

The execution plan shows how SQL Server will physically execute a query. Use it to identify expensive operators, missing indexes, and implicit conversions.

-- Display the actual execution plan for a query (run in SSMS)
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT
    o.OrderID,
    o.OrderDate,
    c.CustomerName,
    SUM(od.Quantity * od.UnitPrice) AS OrderTotal
FROM dbo.Orders AS o
INNER JOIN dbo.Customers AS c ON o.CustomerID = c.CustomerID
INNER JOIN dbo.OrderDetails AS od ON o.OrderID = od.OrderID
WHERE o.OrderDate >= '2025-01-01'
  AND o.Status = 'Completed'
GROUP BY o.OrderID, o.OrderDate, c.CustomerName
ORDER BY OrderTotal DESC;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Look for these warning signs in the execution plan:

• Table Scan / Clustered Index Scan on large tables — suggests a missing or unused index
• Key Lookup operators — the index covers the WHERE clause but not all SELECT columns; consider a covering index
• Fat arrows (thick data flow lines) — large row count estimates; check for stale statistics
• Yellow warning triangles — implicit type conversions, missing statistics, or memory grant warnings
• Nested Loop joins on large tables — may indicate a missing index or bad cardinality estimate
• Sort operators — can be eliminated with correctly ordered indexes

Step 8 — Ongoing Maintenance with SQL Server Agent

Automate index maintenance and statistics updates with SQL Server Agent jobs:

-- Create a weekly index rebuild job outline (run in SSMS or execute via Agent)
-- Ola Hallengren's SQL Server Maintenance Solution is the industry standard:
-- https://ola.hallengren.com

-- Basic weekly rebuild job T-SQL for a single database:
USE YourDatabase;

DECLARE @sql NVARCHAR(MAX) = N'';

-- Generate rebuild/reorganize statements based on current fragmentation
SELECT @sql += CASE
    WHEN ips.avg_fragmentation_in_percent BETWEEN 10 AND 30
        THEN N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' +
             QUOTENAME(OBJECT_SCHEMA_NAME(ips.object_id)) + N'.' +
             QUOTENAME(OBJECT_NAME(ips.object_id)) + N' REORGANIZE;' + CHAR(10)
    WHEN ips.avg_fragmentation_in_percent > 30
        THEN N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' +
             QUOTENAME(OBJECT_SCHEMA_NAME(ips.object_id)) + N'.' +
             QUOTENAME(OBJECT_NAME(ips.object_id)) + N' REBUILD WITH (ONLINE = ON, FILLFACTOR = 90);' + CHAR(10)
    END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
INNER JOIN sys.indexes AS 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 > 500
  AND i.name IS NOT NULL;

EXEC sp_executesql @sql;

-- Update all statistics after index maintenance
EXEC sp_updatestats;

Conclusion

SQL Server performance tuning is not a one-time event — it is an ongoing discipline. The steps covered here form a complete, repeatable cycle: establish a baseline with wait statistics and DMV queries, correct the most impactful configuration errors (max server memory, MAXDOP, cost threshold), keep indexes healthy and statistics fresh, and use Query Store to catch and resolve plan regressions before they affect end users. On Windows Server 2025, SQL Server 2022 benefits from the latest NUMA-aware scheduling improvements, Accelerated Database Recovery (ADR) for near-instant transaction rollback, and enhanced Query Store features including Query Store hints that let you influence query plans without touching application code. By combining these server-level tunings with application-level best practices — parameterised queries, appropriate transaction isolation levels, and well-designed indexes — you create a database environment that scales reliably as your data and user base grow.