SQL Server Query Tuning: Plans, Indexes, Statistics

Optimizing MS SQL Server Queries: Execution Plans, Indexes, Statistics

A query runs in 200 ms, then suddenly jumps to 45 seconds. Data volume hasn't changed, indexes are the same, code is untouched. This is plan regression—the optimizer picks a different execution plan due to outdated statistics or parameter sniffing. We encounter this in 80% of performance-related projects. We deal with such cases daily and know how to restore performance in days. The typical scenario: a query worked perfectly until a statistics update or data change made it slow. Without deep analysis of the execution plan, you risk hours of pointless tweaks. Request an audit—we'll assess your project and suggest concrete steps.

How to Read an Execution Plan

Execution plans are operator trees read from right to left. Arrow thickness indicates data volume. A discrepancy between Estimated Rows and Actual Rows is the first sign of trouble. We see such discrepancies in 80% of slow queries.

Key operators:

-- Get actual execution plan programmatically
SET STATISTICS XML ON;
SELECT o.OrderID, c.CustomerName, SUM(od.Quantity * od.UnitPrice) AS Total
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
WHERE o.OrderDate >= '2022-01-01'
GROUP BY o.OrderID, c.CustomerName;
SET STATISTICS XML OFF;

-- Or via Query Store
SELECT TOP 5 qp.query_plan, qt.query_sql_text, rs.avg_duration / 1000.0 AS avg_ms, rs.avg_logical_io_reads
FROM sys.query_store_plan qp
JOIN sys.query_store_query q ON qp.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats rs ON qp.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

How to Diagnose Parameter Sniffing?

Parameter sniffing is the main cause of unstable performance. SQL Server caches the plan from the first execution and uses it for all subsequent calls. Typical case: a stored procedure for a large client produces a plan with Table Scan, which is inefficient for a smaller client.

-- Diagnose parameter sniffing
SELECT TOP 10
    qs.execution_count,
    qs.plan_generation_num,
    qs.total_worker_time / qs.execution_count AS avg_cpu,
    qs.min_worker_time,
    qs.max_worker_time,
    qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%GetCustomerOrders%'
ORDER BY qs.max_worker_time DESC;

Solutions depending on the situation:

-- 1. OPTIMIZE FOR UNKNOWN — use averaged statistics
CREATE PROCEDURE GetOrdersByCustomer
    @CustomerID INT
AS
SELECT * FROM Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

-- 2. Recompile for unstable queries
CREATE PROCEDURE GetOrdersByDateRange
    @StartDate DATE,
    @EndDate DATE
AS
SELECT * FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
OPTION (RECOMPILE);

-- 3. Clear specific plan cache on degradation
DECLARE @plan_handle VARBINARY(64);
SELECT @plan_handle = plan_handle
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text LIKE '%GetCustomerOrders%';
DBCC FREEPROCCACHE (@plan_handle);

How Query Store Helps Detect Plan Regression

Query Store automatically records plan history and performance metrics. It helps identify a plan that drastically worsened execution time. For example, if avg_duration jumps from 50 ms to 5 seconds, Query Store captures the moment of plan change. You can then force the previous plan using sp_query_store_force_plan. This restores performance immediately without code changes. We use Query Store as a baseline monitoring tool on all projects.

What Indexes Does a Specific Query Need?

Missing index suggestions from execution plans are useful hints, not rules. SQL Server sees one query and proposes an index without considering write load. We use the DMV sys.dm_db_missing_index_details and always check the impact on modification operations.

-- Top missing indexes by potential benefit
SELECT TOP 20
    ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) AS estimated_improvement,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs
    ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID('AppDB')
ORDER BY estimated_improvement DESC;

-- Unused indexes (candidates for removal)
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id
    AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID('AppDB')
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.type > 0
    AND (ius.user_seeks IS NULL OR ius.user_seeks = 0)
    AND (ius.user_scans IS NULL OR ius.user_scans = 0)
    AND (ius.user_lookups IS NULL OR ius.user_lookups = 0)
ORDER BY ius.user_updates DESC;

-- Index fragmentation
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID('AppDB'), 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;

Fragmentation rule: 10-30% → REORGANIZE (online), >30% → REBUILD. For large tables, use ALTER INDEX ... WITH (ONLINE = ON). Using a covering index can reduce logical reads by 10x compared to Key Lookup. Fragmentation >30% on a 100 million-row table can add 20% to execution time. REBUILD in such cases takes 2-3 hours but pays off within a week.

Why Is It Important to Update Statistics?

The optimizer relies on statistics. Outdated statistics lead to poor estimates and bad plans.

-- Statistics that haven't been updated recently
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,
       CAST(100.0 * sp.rows_sampled / NULLIF(sp.rows, 0) AS DECIMAL(5,2)) AS sample_pct
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND (sp.last_updated < DATEADD(DAY, -7, GETDATE()) OR sp.last_updated IS NULL)
ORDER BY sp.modification_counter DESC;

-- Full scan update
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- For large tables — sampling
UPDATE STATISTICS dbo.OrderDetails WITH SAMPLE 30 PERCENT;

Auto-Update Statistics is on by default, but the 20% change threshold is too high for large tables. For tables over 100 million rows, we set up manual jobs with FULLSCAN once a week. This takes 2-3 hours but prevents query degradation. A 10% cardinality estimation error can lead to a plan with 50x difference in execution time.

Optimization Process

1. Identify problematic queries via Query Store or DMV.
2. Obtain actual execution plan; analyze high-cost operators.
3. Diagnose: parameter sniffing, outdated statistics, missing index, non-optimal JOIN.
4. Implement changes: update statistics, create/modify indexes, query hints.
5. Measure results: compare before/after plan, metrics.
6. Load test on staging.

What Is Included in Optimization Work

• Detailed performance audit with report on each problematic query.
• Index recommendations considering write load.
• Configuration of automatic statistics update and plan management.
• Documentation of changes and training for your team.
• Post-optimization monitoring for one week.

Our expertise: 10+ years in SQL Server optimization, 500+ completed projects. We guarantee at least 30% reduction in query execution time. Optimization can save your company up to $6,000 annually on server and licensing costs. Contact us to discuss your project and get a consultation.

Timeline: audit takes 3-5 days; full optimization 1 to 3 weeks depending on scope. Cost is determined individually. Get a consultation—we'll analyze your case and propose the optimal solution.