You cannot tune what you cannot measure, and the single most valuable measurement tool shipped with the engine is the Query Store. It captures query text, execution plans, and runtime statistics over time, allowing you to identify regressed queries, compare plan choices, and force a known-good plan when the optimizer drifts. In SQL Server 2022, Query Store is enabled by default on new databases and underpins several automatic-tuning and Intelligent Query Processing features, which makes it foundational rather than optional.

Treat Query Store as the system of record for performance. Before you change an index, a setting, or a query, capture the baseline; after the change, compare against it objectively rather than relying on impressions. On high-volume systems, tune the capture mode to avoid recording trivial ad-hoc statements, set a retention window that matches your investigation horizon, and size the storage so the store never silently flips to read-only and stops collecting. The Microsoft documentation on the Query Store details capture modes and retention policies worth tuning for busy systems.

-- Enable Query Store with sensible capture settings
ALTER DATABASE SalesDB SET QUERY_STORE = ON
  (OPERATION_MODE = READ_WRITE,
   QUERY_CAPTURE_MODE = AUTO,
   DATA_FLUSH_INTERVAL_SECONDS = 900,
   MAX_STORAGE_SIZE_MB = 2048);

-- Find the most expensive queries in the last 24 hours
SELECT TOP (20) qt.query_sql_text,
       rs.avg_duration, rs.avg_logical_io_reads, rs.count_executions
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p   ON p.plan_id = rs.plan_id
JOIN sys.query_store_query q  ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
ORDER BY rs.avg_duration DESC;

Indexing is where most SQL Server performance is won or lost. The goal is to support the read patterns the application actually issues while keeping the write and storage overhead in check. Start by reading the missing-index dynamic management views and the actual execution plans, then design covering indexes with carefully chosen key columns and INCLUDE columns so that high-value queries can be satisfied without a key lookup. Equally important is removing indexes that are never used for seeks but are maintained on every insert, update, and delete.

For analytical and reporting workloads, a columnstore index can deliver order-of-magnitude gains over a traditional rowstore index by combining columnar storage with batch-mode execution. Resist the temptation to act on the missing-index DMVs blindly: they suggest candidates, not a strategy. A deliberate index design considers the whole query workload, not one statement at a time.

-- Review missing-index suggestions, then design holistically
SELECT d.statement, d.equality_columns, d.inequality_columns,
       d.included_columns, s.avg_user_impact, s.user_seeks
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g  ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY s.avg_user_impact DESC;

-- A covering index that eliminates key lookups
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Covering
  ON dbo.Orders (CustomerID, OrderDate)
  INCLUDE (Status, TotalAmount);

The query optimizer is a cost-based engine, and its decisions are only as good as the statistics that describe your data distribution. Stale or inaccurate statistics lead to poor cardinality estimates, which lead to the wrong join algorithm, the wrong memory grant, and the wrong plan shape. On large tables, the automatic update threshold can leave statistics unrefreshed for far too long, so a deliberate maintenance strategy matters.

Keep automatic statistics updates enabled, consider the per-table incremental and AUTO_UPDATE_STATISTICS_ASYNC options for volatile large tables, and schedule manual updates with an appropriate sample — or a full scan — for the tables that drive your most critical queries. The Microsoft guidance on statistics explains the cardinality estimator versions and when a compatibility-level change can alter plan quality. When a workload regresses after an upgrade, statistics and the cardinality estimator are among the first places to look.

-- Inspect staleness and modification counters
SELECT OBJECT_NAME(s.object_id) AS table_name, s.name AS stat_name,
       sp.last_updated, sp.rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 0
ORDER BY sp.modification_counter DESC;

-- Refresh statistics on a critical table with a full scan
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

Parameter sniffing is one of the most common causes of intermittent, hard-to-reproduce slowdowns. SQL Server compiles a plan based on the parameter values present at first execution and caches it; when a later execution uses values with very different selectivity, the cached plan can be catastrophically wrong. The symptom is a query that is fast for some inputs and slow for others, with no code change in between.

SQL Server 2022 introduced Parameter Sensitive Plan optimization, which can cache multiple plan variants for a single parameterized statement based on cardinality, and it is active under the latest database compatibility level. Where you need finer control, the classic tools remain valuable: OPTIMIZE FOR, RECOMPILE for low-frequency statements, and Query Store plan forcing. The Parameter Sensitive Plan optimization documentation describes the eligibility rules in detail.

-- Force a fresh plan for a statement with skewed parameters
SELECT * FROM dbo.Orders
WHERE Status = @status
OPTION (RECOMPILE);

-- Or steer the optimizer toward an average-case plan
SELECT * FROM dbo.Orders
WHERE Status = @status
OPTION (OPTIMIZE FOR (@status = 'OPEN'));

Under concurrency, the constraint is frequently not CPU or I/O but contention. Transactions wait on locks held by other transactions, throughput collapses, and the application surfaces timeouts that look like a performance problem but are really a concurrency-design problem. The first step is always to identify the blocking chain and the resource at its head.

Long-term remedies fall into three categories: shorten transactions so locks are held briefly, index appropriately so that updates touch fewer rows and avoid lock escalation, and choose the right isolation level for the workload. For read-heavy systems, enabling Read Committed Snapshot Isolation lets readers see a consistent version without blocking writers, often eliminating the majority of contention. Microsoft’s snapshot isolation documentation covers the tempdb and version-store implications you must plan for before enabling it in production.

-- Identify current blocking chains
SELECT w.session_id, w.wait_duration_ms, w.wait_type,
       w.blocking_session_id, t.text AS batch_text
FROM sys.dm_os_waiting_tasks w
JOIN sys.dm_exec_requests r ON r.session_id = w.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE w.blocking_session_id IS NOT NULL;

-- Enable RCSI to stop readers blocking writers
ALTER DATABASE SalesDB SET READ_COMMITTED_SNAPSHOT ON
  WITH ROLLBACK IMMEDIATE;

tempdb is a shared, system-wide resource used for temporary tables, table variables, sort and hash spills, version stores, and online index builds. On a busy instance it becomes a contention point, with sessions waiting on allocation-page latches (PFS, GAM, and SGAM) when too few data files are available. A correctly configured tempdb is one of the most reliable concurrency improvements you can make, and it costs nothing but attention.

Provision multiple, equally sized tempdb data files — a common starting point is one file per logical processor up to eight, then add more only if latch contention persists — and place tempdb on the fastest storage available. SQL Server 2022 adds memory-optimized tempdb metadata, which removes a long-standing system-table bottleneck on high-throughput systems. Microsoft’s tempdb configuration guidance describes file sizing and the metadata option in depth.

-- Enable memory-optimized tempdb metadata (restart required)
ALTER SERVER CONFIGURATION
  SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

-- Confirm equally sized tempdb data files
SELECT name, size * 8 / 1024 AS size_mb, growth, is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb') AND type_desc = 'ROWS';

Two server-level settings have an outsized effect on overall behavior: the memory ceiling and the parallelism configuration. Leaving max server memory at the default allows the buffer pool to starve the operating system and any co-located services, so set it deliberately based on the physical memory and what else runs on the host. Memory is the cache that keeps your working set off disk; the more of it the engine can use safely, the less I/O you pay.

Parallelism is governed by the maximum degree of parallelism and the cost threshold for parallelism. The historical default cost threshold of five is far too low for modern hardware and causes trivial queries to go parallel, generating CXPACKET and CXCONSUMER waits. Raise the threshold and set MAXDOP according to your core and NUMA topology. Microsoft’s MAXDOP configuration guidance provides recommended values by processor count.

-- Cap buffer pool memory (value in MB)
EXEC sys.sp_configure 'max server memory (MB)', 49152;
RECONFIGURE;

-- Sensible parallelism defaults for modern hardware
EXEC sys.sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

The cheapest read is the one you never issue, and the second cheapest is the one served from memory. Data compression advances both goals: by storing more rows per page it reduces the I/O needed to scan a table and increases the effective capacity of the buffer pool, often improving CPU-bound analytical scans as well. Row and page compression are available across editions in modern SQL Server, and the savings on wide, repetitive data are substantial.

For very large tables, table partitioning enables partition elimination, so range queries scan only the relevant partitions, and it makes maintenance operations such as archiving and index rebuilds far more efficient through partition switching. Combine compression with partitioning and a sound storage layout, and you reduce both the volume of I/O and its latency. Where the workload is overwhelmingly analytical, consider a clustered columnstore index in place of page compression, since it delivers both superior compression ratios and batch-mode execution on the same data. Measure the estimated savings before applying compression, since the benefit depends heavily on data shape.

-- Estimate before you compress
EXEC sys.sp_estimate_data_compression_savings
  @schema_name = 'dbo', @object_name = 'Orders',
  @index_id = NULL, @partition_number = NULL,
  @data_compression = 'PAGE';

-- Apply page compression to a clustered index
ALTER INDEX PK_Orders ON dbo.Orders
  REBUILD WITH (DATA_COMPRESSION = PAGE);

One of the most compelling reasons to modernize the database compatibility level is the Intelligent Query Processing family, a set of optimizer features that improve performance with little or no code change. The 2022 wave includes memory grant feedback that now persists across recompilations, degree-of-parallelism feedback, cardinality estimation feedback, optimized plan forcing, and broader batch-mode execution on rowstore — all building on adaptive joins and interleaved execution introduced in earlier releases.

These features address precisely the failure modes that consume DBA time: oversized or undersized memory grants that cause spills or waste, parallelism choices that do not match the workload, and repeated cardinality misestimates. The practical step is to move databases to the latest Intelligent Query Processing compatibility level after validating against a representative workload, ideally using Query Store to compare before and after. Adopt it deliberately, but do adopt it — leaving these gains on the table is a common and avoidable cost.

-- Move to the SQL Server 2022 compatibility level (160)
-- to enable the latest IQP features
ALTER DATABASE SalesDB
  SET COMPATIBILITY_LEVEL = 160;

-- Confirm the current level
SELECT name, compatibility_level
FROM sys.databases WHERE name = 'SalesDB';

The most important habit of a high-performing DBA is to let the server tell you what it is waiting on, rather than guessing. Wait statistics are SQL Server’s self-diagnosis: every time a request cannot proceed, it records why. Aggregated, these waits point directly at the dominant constraint — whether that is I/O latency, lock contention, memory pressure, parallelism, or network. Tuning without consulting waits is tuning in the dark.

Establish a baseline of wait statistics during normal operation so that an abnormal pattern stands out, and pair the aggregate view from sys.dm_os_wait_stats with targeted, low-overhead Extended Events sessions to capture the specific statements behind a problem. Proactive monitoring turns performance work from firefighting into engineering, and it is the foundation of every Database SRE practice we run. The server already knows where it hurts; your job is to read the signal and act on it.

-- Top waits, excluding benign background waits
SELECT TOP (15) wait_type,
       wait_time_ms / 1000.0 AS wait_seconds,
       100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
  ('CLR_SEMAPHORE','SLEEP_TASK','BROKER_TASK_STOP','CHECKPOINT_QUEUE')
ORDER BY wait_time_ms DESC;