Database Performance Tuning: Diagnose Plan Regressions in Production

Start Database Performance Tuning With a Reproducible Baseline

Good database training starts with evidence that survives an incident, not with cache clears. For SQL Server, enable Query Store before investigating a regression so plans, runtime intervals, and query text are retained across restarts. This configuration gives a 15-minute comparison window and caps retained data; monitor Query Store storage because a full store can move to read-only mode and stop collecting new plans.

ALTER DATABASE Sales SET QUERY_STORE = ON;
ALTER DATABASE Sales SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    INTERVAL_LENGTH_MINUTES = 15,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 2048,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
);

For a workload-level baseline, schedule a SQL Agent job every five minutes to snapshot waits into a durable table. Calculate deltas between snapshots; do not run DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) on a shared production instance, because it destroys the evidence needed by every other investigation. A rise in PAGEIOLATCH_* points to data-page reads waiting on storage, while LCK_M_* requires a blocking-chain investigation rather than an index recommendation.

CREATE TABLE dbo.WaitStatsSnapshot (
    captured_at_utc datetime2 NOT NULL,
    wait_type nvarchar(60) NOT NULL,
    waiting_tasks_count bigint NOT NULL,
    wait_time_ms bigint NOT NULL,
    signal_wait_time_ms bigint NOT NULL
);

INSERT dbo.WaitStatsSnapshot
    (captured_at_utc, wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms)
SELECT SYSUTCDATETIME(), wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%';

In sql training, measure the exact request shape before changing schema: parameter values, returned row count, logical reads, CPU time, duration, and concurrent request count. Run the representative statement with SET STATISTICS IO, TIME ON in a non-production clone, record the output, then rerun the identical workload after a change. A query that becomes 5 ms faster alone but changes from 20 logical reads to 20,000 under a common tenant is not a successful fix.

Use SQL Server Training to Compare Runtime Plans, Not Estimated Cost

SQL Server training should teach developers to compare plans for the same query_id, because the graphical plan's estimated subtree cost is only a model-local number and cannot rank two executions reliably. Query Store exposes measured average duration and CPU per plan and interval. Start with this query, then inspect plan XML for the fastest and slowest plan_id under the same query ID.

SELECT TOP (20)
       q.query_id,
       p.plan_id,
       rs.count_executions,
       CAST(rs.avg_duration / 1000.0 AS decimal(12,2)) AS avg_duration_ms,
       CAST(rs.avg_cpu_time / 1000.0 AS decimal(12,2)) AS avg_cpu_ms,
       qt.query_sql_text
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_runtime_stats_interval AS i
  ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(hour, -24, SYSUTCDATETIME())
ORDER BY rs.avg_duration DESC;

Open the actual execution plan in SSMS or Azure Data Studio and compare estimated versus actual rows at every join, sort, hash, and lookup. A 100-row estimate that produces 2 million rows can select nested loops plus repeated key lookups; the lookup is not inherently bad, but its cost is multiplied by the actual outer input. Use SET STATISTICS XML ON for a controlled reproduction, or capture the actual plan through Extended Events with query_post_execution_showplan; do not enable that event indiscriminately on a high-throughput server because plan XML collection is expensive.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;

EXEC sys.sp_executesql
    N'SELECT OrderId, Status, Total
      FROM dbo.Orders
      WHERE TenantId = @tenantId AND CreatedAt >= @fromDate;',
    N'@tenantId int, @fromDate datetime2',
    @tenantId = 42,
    @fromDate = '2026-08-01';

Check memory grants when a plan contains a sort or hash aggregate. In an actual plan, a spill warning means an operator wrote intermediate rows to tempdb because its memory grant was insufficient; simply adding RAM does not repair a cardinality estimate that asked for too little memory. Correlate the plan with sys.dm_exec_query_memory_grants during the event and compare requested_memory_kb, granted_memory_kb, and queue wait time. A large grant can also create concurrency stalls by reserving workspace memory that other requests need.

Repair Parameter Sensitivity Before Adding Database Indexing

Parameter sensitivity is a common reason one stored procedure is fast for a small tenant and slow for a large one: SQL Server compiles a reusable plan using cardinality assumptions available for the first compilation context, then reuses it for materially different data distributions. First prove the pattern by executing representative small and large parameter sets separately with SET STATISTICS IO, TIME ON. For a selective, infrequently called reporting statement, statement-level recompilation is a direct and testable fix because the optimizer sees the current literal-equivalent parameter values.

CREATE OR ALTER PROCEDURE dbo.GetRecentOrders
    @TenantId int,
    @FromDate datetime2
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, Status, Total, CreatedAt
    FROM dbo.Orders
    WHERE TenantId = @TenantId
      AND CreatedAt >= @FromDate
    OPTION (RECOMPILE);
END;

Use OPTION (RECOMPILE) deliberately: it trades plan reuse for compilation CPU, so it is usually wrong for a hot endpoint called thousands of times per second. For stable query shapes where the predicate is equality on TenantId followed by a date range, test an index ordered by the equality key first. The key order matters because an index beginning with CreatedAt cannot efficiently seek all dates for one tenant in the same way.

CREATE INDEX IX_Orders_TenantId_CreatedAt
ON dbo.Orders (TenantId, CreatedAt)
INCLUDE (OrderId, Status, Total);

Validate database indexing with a before-and-after table: logical reads from STATISTICS IO, elapsed time under concurrent load, index size, and write latency for inserts into dbo.Orders. Do not force the currently fast Query Store plan as the permanent first response. Plan forcing is useful containment during an outage, but it can preserve an accidental assumption about statistics or parameter distribution and can become invalid after a schema change.

Make Database Optimization Account for Statistics and Write Amplification

An unused-looking index is not automatically removable, but it should be measured. In the current database, compare seeks, scans, and lookups with user_updates from sys.dm_db_index_usage_stats. Capture this output over a business cycle because these counters reset on engine restart, failover, detach/attach, and some maintenance operations; a single snapshot is not a deletion decision.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS table_name,
       i.name AS index_name,
       COALESCE(s.user_seeks, 0) + COALESCE(s.user_scans, 0)
         + COALESCE(s.user_lookups, 0) AS reads,
       COALESCE(s.user_updates, 0) AS writes
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.database_id = DB_ID()
 AND s.object_id = i.object_id
 AND s.index_id = i.index_id
WHERE i.index_id > 0
  AND i.is_hypothetical = 0
ORDER BY writes DESC, reads ASC;

Before creating another index, inspect whether stale statistics explain the bad row estimate. This query identifies statistics objects whose modification counter is large relative to their sampled rows. Refresh only the statistic implicated by the plan, then rerun the measured workload; broad UPDATE STATISTICS jobs can create avoidable I/O and compilation churn on large databases.

SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
       OBJECT_NAME(s.object_id) AS table_name,
       s.name AS stats_name,
       sp.rows,
       sp.modification_counter,
       sp.last_updated
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.rows > 0
ORDER BY CAST(sp.modification_counter AS float) / sp.rows DESC;

Avoid treating missing-index DMVs as DDL generators. Their suggestions do not model existing-index overlap, filtered indexes, index maintenance cost, or the combined storage budget of several recommendations. In a write-heavy table, every additional nonclustered index adds a B-tree entry for insert, delete, and relevant update operations; measure this with application write latency and transaction-log growth before accepting a read-side improvement.

Apply the Same Database Optimization Discipline to NoSQL Training

NoSQL training benefits from the same evidence-first workflow. In MongoDB, use explain('executionStats') and compare totalKeysExamined, totalDocsExamined, and nReturned. For a tenant-scoped open-order feed, a partial compound index limits index entries to open orders and preserves the requested descending date order after the equality predicate on tenantId.

db.orders.createIndex(
  { tenantId: 1, createdAt: -1, orderId: 1 },
  {
    name: 'open_orders_by_tenant_date',
    partialFilterExpression: { status: 'open' }
  }
);

db.orders.explain('executionStats').find(
  { tenantId: 42, status: 'open' },
  { _id: 0, orderId: 1, createdAt: 1 }
).sort({ createdAt: -1 }).limit(50);

A useful target for this bounded feed is examined keys and documents close to returned documents, not merely seeing an IXSCAN stage. The partial index above is eligible only when the query predicate implies status: 'open'; a query using { status: { $in: ['open', 'pending'] } } cannot safely use it for all requested documents. This eligibility rule is a frequent source of confusing plan changes after an apparently equivalent API filter is introduced.

For sharded MongoDB collections, verify routing before tuning a local index. Run db.orders.getShardDistribution() and inspect explain('executionStats') for the number of shards contacted. A query that omits the shard-key prefix can scatter to every shard, so even a fast per-shard index may be dominated by merge latency and network fan-out. This is the NoSQL equivalent of checking waits and plan shape before declaring a database optimization complete.

Frequently Asked Questions

How do I begin database performance tuning without clearing SQL Server caches?

Enable Query Store, snapshot wait statistics every five minutes, and reproduce the slow request with its real parameter values. Compare logical reads, CPU, duration, and actual row counts before and after one change. Clearing caches changes the workload and removes evidence; it is not a baseline.

Which database indexing metrics should I collect before creating an index?

Collect STATISTICS IO logical reads for the target query, actual-plan row estimates versus actual rows, index size, user_updates from sys.dm_db_index_usage_stats, transaction-log growth, and insert/update latency under representative concurrency. Test the index in a production-like copy and retain it only when its read reduction justifies its write maintenance.

What should SQL Server training teach about parameter sniffing?

Teach developers to execute the same stored procedure with both sparse and dense parameter values, inspect the actual plans and logical reads, and then choose a measured response: a supporting index, statement-level OPTION(RECOMPILE) for low-frequency skewed requests, or a Query Store forced plan as temporary containment. Do not use local-variable tricks, because they replace a known parameter with a generic estimate rather than modelling the distribution.

How can NoSQL training verify that a MongoDB index is actually effective?

Use explain('executionStats') on the exact production-shaped filter, projection, sort, and limit. Check totalKeysExamined and totalDocsExamined against nReturned, verify that the index is eligible for the predicate, and confirm shard targeting when the collection is sharded. An IXSCAN alone is insufficient if the query still examines thousands of documents to return 50.