SQL Server Query Optimization: Execution Plans and Performance Tuning

'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.', '_') + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NOT NULL THEN ', ' + mid.inequality_columns ELSE '' END + ')' + CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END AS CreateIndexStatement``` FROM sys.dm_db_missing_index_details mid INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle WHERE mid.database_id = DB_ID() ORDER BY ImprovementScore DESC;

## Statistics

![Statistics](/images/articles/sql/2025-04-07-sql-server-query-optimization-execution-plans-performance-tuning-ctx-1.svg)

### Auto-Create and Auto-Update Statistics







```sql
-- Check database statistics settings
SELECT 
```text
name,
is_auto_create_stats_on,
is_auto_update_stats_on,
is_auto_update_stats_async_on```
FROM sys.databases
WHERE name = 'AdventureWorks';

-- Enable auto-create/update (recommended)
ALTER DATABASE AdventureWorks SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS_ASYNC ON;  -- Async for large tables

Manual Statistics Update

-- Update statistics for specific table
UPDATE STATISTICS dbo.Orders;

-- Update statistics with full scan (more accurate)
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Update specific index statistics
UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH FULLSCAN;

-- Update all statistics in database
EXEC sp_updatestats;

Viewing Statistics

-- View statistics histogram
DBCC SHOW_STATISTICS('dbo.Orders', 'IX_Orders_OrderDate');

-- Results include:
-- 1. Header: Rows, rows sampled, last update date
-- 2. Density vector: Column correlations
-- 3. Histogram: Distribution of values (up to 200 steps)

-- Script to check stale statistics
SELECT 
```text
OBJECT_NAME(s.object_id) AS TableName,
s.name AS StatName,
sp.last_updated,
sp.rows,
sp.rows_sampled,
sp.modification_counter,
sp.modification_counter * 100.0 / sp.rows AS PercentChanged```
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_NAME(s.object_id) LIKE 'Orders%'
```text
AND sp.modification_counter > 0```
ORDER BY sp.modification_counter DESC;

Filtered Statistics

-- Create filtered statistics for subset of data
CREATE STATISTICS ST_Orders_Recent
ON dbo.Orders(OrderDate)
WHERE OrderDate >= '2025-01-01';

-- Useful for partitioned or skewed data
CREATE STATISTICS ST_Products_Active
ON dbo.Products(Price)
WHERE IsActive = 1;

Query Hints

Table Hints

-- NOLOCK (READ UNCOMMITTED) - allows dirty reads
SELECT * FROM dbo.Orders WITH (NOLOCK);

-- ROWLOCK - forces row-level locks
UPDATE dbo.Products WITH (ROWLOCK)
SET Price = Price * 1.1
WHERE ProductID = 100;

-- PAGLOCK - forces page-level locks
UPDATE dbo.Orders WITH (PAGLOCK)
SET Status = 'Processed'
WHERE OrderDate < '2024-01-01';

-- TABLOCK - forces table-level lock
DELETE FROM dbo.OrdersArchive WITH (TABLOCK)
WHERE OrderDate < '2020-01-01';

-- UPDLOCK - prevents deadlocks in read-then-update scenarios
BEGIN TRANSACTION;
SELECT * FROM dbo.Inventory WITH (UPDLOCK, ROWLOCK)
WHERE ProductID = 100;

UPDATE dbo.Inventory
SET Quantity = Quantity - 5
WHERE ProductID = 100;
COMMIT;

Join Hints

-- Force nested loops join
SELECT 
```text
c.CustomerID,
o.OrderID```
FROM dbo.Customers c
INNER LOOP JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;

-- Force hash join
SELECT 
```text
p.ProductID,
od.OrderID```
FROM dbo.Products p
INNER HASH JOIN dbo.OrderDetails od ON p.ProductID = od.ProductID;

-- Force merge join
SELECT 
```text
c.CategoryID,
p.ProductID```
FROM dbo.Categories c
INNER MERGE JOIN dbo.Products p ON c.CategoryID = p.CategoryID;

Query Hints

-- FORCESEEK - forces index seek instead of scan
SELECT 
```text
CustomerID,
CustomerName```
FROM dbo.Customers WITH (FORCESEEK)
WHERE CustomerID > 1000;

-- FORCESCAN - forces index/table scan
SELECT 
```text
OrderID,
TotalAmount```
FROM dbo.Orders WITH (FORCESCAN)
WHERE OrderDate >= '2025-01-01';

-- OPTIMIZE FOR - optimizes for specific parameter value
SELECT 
```text
OrderID,
OrderDate,
TotalAmount```
FROM dbo.Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID = 1001));

-- OPTIMIZE FOR UNKNOWN - uses average statistics
SELECT 
```text
OrderID,
TotalAmount```
FROM dbo.Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

-- RECOMPILE - generates new plan each execution
SELECT 
```text
ProductID,
ProductName,
Price```
FROM dbo.Products
WHERE CategoryID = @CategoryID
OPTION (RECOMPILE);

-- MAXDOP - controls parallelism
SELECT 
```text
COUNT(*)```
FROM dbo.Orders
OPTION (MAXDOP 4);  -- Use maximum 4 cores

Index Recommendations

Missing Index DMVs

-- Comprehensive missing index analysis


SELECT TOP 25
```sql
CAST(ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) AS INT) AS ImprovementMeasure,
DB_NAME(mid.database_id) AS DatabaseName,
OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,
migs.user_seeks,
migs.user_scans,
migs.last_user_seek,
migs.last_user_scan,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
'CREATE NONCLUSTERED INDEX IX_' + 
    OBJECT_NAME(mid.object_id, mid.database_id) + '_' +
    REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') +
    CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' + REPLACE(REPLACE(REPLACE(mid.inequality_columns, ', ', '_'), '[', ''), ']', '') ELSE '' END +
    ' ON ' + mid.statement +
    ' (' + ISNULL(mid.equality_columns, '') +
    CASE WHEN mid.inequality_columns IS NOT NULL THEN ', ' + mid.inequality_columns ELSE '' END + ')' +
    CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END +
    ' WITH (ONLINE = ON, FILLFACTOR = 90);' AS CreateIndexStatement```
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
```text
AND migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) > 100```
ORDER BY ImprovementMeasure DESC;

Index Usage Statistics

-- Find unused indexes (consider dropping)
SELECT 
```text
OBJECT_NAME(s.object_id) AS TableName,
i.name AS IndexName,
i.type_desc,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
'DROP INDEX ' + i.name + ' ON ' + OBJECT_SCHEMA_NAME(s.object_id) + '.' + OBJECT_NAME(s.object_id) + ';' AS DropStatement```
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
```text
AND s.database_id = DB_ID()
AND i.type_desc <> 'CLUSTERED'  -- Don't drop clustered indexes
AND s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0
AND s.user_updates > 0  -- Index maintained but never used```
ORDER BY s.user_updates DESC;

-- Find most used indexes
SELECT TOP 20
```text
OBJECT_NAME(s.object_id) AS TableName,
i.name AS IndexName,
s.user_seeks + s.user_scans + s.user_lookups AS TotalReads,
s.user_updates AS TotalWrites,
CAST((s.user_seeks + s.user_scans + s.user_lookups) * 1.0 / NULLIF(s.user_updates, 0) AS DECIMAL(10,2)) AS ReadWriteRatio```
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
```text
AND s.database_id = DB_ID()```
ORDER BY TotalReads DESC;

Covering Indexes

-- Before: Key lookup required (expensive)
SELECT 

> **Architecture Overview:** CustomerID,


## Parameter Sniffing

### Detecting Parameter Sniffing





```sql
-- Symptom: Query sometimes fast, sometimes slow with same structure

-- Check for parameter sniffing
SELECT 
```text
qs.execution_count,
qs.min_elapsed_time / 1000 AS MinDurationMs,
qs.max_elapsed_time / 1000 AS MaxDurationMs,
(qs.max_elapsed_time - qs.min_elapsed_time) / 1000 AS VarianceMs,
qs.total_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 QueryText```
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.max_elapsed_time > qs.min_elapsed_time * 10  -- 10x variance
```text
AND qs.execution_count > 10```
ORDER BY VarianceMs DESC;

Solutions for Parameter Sniffing

Solution 1: OPTIMIZE FOR UNKNOWN

CREATE PROCEDURE dbo.GetOrdersByCustomer
```text
@CustomerID INT```
AS
BEGIN
```sql
SELECT 
    OrderID,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));  -- Use average statistics```
END;
GO

Solution 2: Local Variables

CREATE PROCEDURE dbo.GetOrdersByCustomer
```text
@CustomerID INT```
AS
BEGIN
```sql
-- Use local variable to prevent parameter sniffing
DECLARE @LocalCustomerID INT = @CustomerID;

SELECT 
    OrderID,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerID = @LocalCustomerID;```
END;
GO

Solution 3: RECOMPILE

CREATE PROCEDURE dbo.GetOrdersByCustomer
```text
@CustomerID INT```
AS
BEGIN
```sql
SELECT 
    OrderID,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);  -- Generate new plan each execution```
END;
GO

Solution 4: Plan Guides (Advanced)

-- Create plan guide to force OPTIMIZE FOR UNKNOWN
EXEC sp_create_plan_guide
```sql
@name = N'PlanGuide_GetOrdersByCustomer',
@stmt = N'SELECT OrderID, OrderDate, TotalAmount FROM dbo.Orders WHERE CustomerID = @CustomerID',
@type = N'OBJECT',
@module_or_batch = N'dbo.GetOrdersByCustomer',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN))';

## Query Rewrite Techniques

![Query Rewrite Techniques](/images/articles/sql/2025-04-07-sql-server-query-optimization-execution-plans-performance-tuning-ctx-3.svg)

### EXISTS vs. IN





```sql
-- ✅ GOOD: EXISTS (stops at first match)
SELECT 
```text
c.CustomerID,
c.CustomerName```
FROM dbo.Customers c
WHERE EXISTS (
```sql
SELECT 1 FROM dbo.Orders o
WHERE o.CustomerID = c.CustomerID```
);

-- ❌ SLOWER: IN (may evaluate all matches)
SELECT 
```text
c.CustomerID,
c.CustomerName```
FROM dbo.Customers c
WHERE c.CustomerID IN (
```sql
SELECT CustomerID FROM dbo.Orders```
);

JOIN vs. Subquery

-- ✅ GOOD: JOIN (usually faster)
SELECT 
```text
c.CustomerID,
c.CustomerName,
COUNT(o.OrderID) AS OrderCount```
FROM dbo.Customers c
INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName;

-- ❌ SLOWER: Correlated subquery (executes per row)
SELECT 
```sql
c.CustomerID,
c.CustomerName,
(SELECT COUNT(*) FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID) AS OrderCount```
FROM dbo.Customers c;

UNION ALL vs. UNION

-- ✅ FASTER: UNION ALL (no duplicate removal)
SELECT CustomerID, CustomerName FROM dbo.Customers WHERE CustomerID < 1000
UNION ALL
SELECT CustomerID, CustomerName FROM dbo.CustomersArchive WHERE CustomerID < 1000;

-- ❌ SLOWER: UNION (removes duplicates with sort)
SELECT CustomerID, CustomerName FROM dbo.Customers WHERE CustomerID < 1000
UNION
SELECT CustomerID, CustomerName FROM dbo.CustomersArchive WHERE CustomerID < 1000;

Set-Based vs. Cursor

-- ❌ SLOW: Cursor approach
DECLARE @OrderID INT;
DECLARE cur CURSOR FOR 
```sql
SELECT OrderID FROM dbo.Orders WHERE Status = 'Pending';

OPEN cur; FETCH NEXT FROM cur INTO @OrderID;

WHILE @@FETCH_STATUS = 0 BEGIN

EXEC dbo.ProcessOrder @OrderID;
FETCH NEXT FROM cur INTO @OrderID;```
END;

CLOSE cur;
DEALLOCATE cur;

-- ✅ FAST: Set-based approach
UPDATE o
SET o.Status = 'Processed',
```text
o.ProcessedDate = GETUTCDATE()```
FROM dbo.Orders o
WHERE o.Status = 'Pending';

Architecture Decision and Tradeoffs

When designing data management solutions with SQL Server, consider these key architectural trade-offs:

Recommendation: Start with the managed approach for most workloads and move to custom only when specific requirements demand it.

Validation and Versioning

• Last validated: April 2026
• Validate examples against your tenant, region, and SKU constraints before production rollout.
• Keep module, CLI, and SDK versions pinned in automation pipelines and review quarterly.

Security and Governance Considerations

• Apply least-privilege access using RBAC roles and just-in-time elevation for admin tasks.
• Store secrets in managed secret stores and avoid embedding credentials in scripts or source files.
• Enable audit logging, data protection policies, and periodic access reviews for regulated workloads.

Cost and Performance Notes

• Define budgets and alerts, then monitor usage and cost trends continuously after go-live.
• Baseline performance with synthetic and real-user checks before and after major changes.
• Scale resources with measured thresholds and revisit sizing after usage pattern changes.

Official Microsoft References

• https://learn.microsoft.com/sql/
• https://learn.microsoft.com/azure/azure-sql/
• https://learn.microsoft.com/fabric/database/

Public Examples from Official Sources

• These examples are sourced from official public Microsoft documentation and sample repositories.
• Documentation examples: https://learn.microsoft.com/sql/
• Sample repositories: https://github.com/microsoft/sql-server-samples
• Prefer adapting these examples to your tenant, subscriptions, and governance requirements before production use.

Key Takeaways

• Read execution plans to identify expensive operators
• Keep statistics updated with AUTO_UPDATE or manual updates
• Use query hints sparingly - only when optimizer fails
• Implement missing index recommendations carefully
• Detect parameter sniffing with execution time variance
• Solve parameter sniffing with OPTIMIZE FOR UNKNOWN or RECOMPILE
• Prefer EXISTS over IN for existence checks
• Use JOINs instead of correlated subqueries
• Choose UNION ALL over UNION when duplicates acceptable
• Always use set-based operations over cursors

Next Steps

• Enable execution plans for slow queries
• Update outdated statistics
• Implement high-impact missing indexes
• Audit procedures for parameter sniffing
• Review query patterns for optimization opportunities

Additional Resources

• Execution Plans
• Statistics
• Query Hints
• Parameter Sniffing

Measure. Analyze. Optimize. Repeat.