SQL Server Query Optimization: Execution Plans & Index Tuning

Reading Execution Plans

In SSMS, press Ctrl+M to enable actual execution plans. Look for Table Scan and Key Lookup operators — these are your optimization targets.

Finding Missing Indexes

SELECT
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS Score,
    mid.statement AS TableName,
    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
ORDER BY Score DESC;

Covering Indexes

-- Include all columns needed by the query to avoid Key Lookup
CREATE INDEX IX_BlogPosts_Status_Published
    ON BlogPosts (Status, PublishedAt DESC)
    INCLUDE (Title, Slug, Excerpt, AuthorId);

Statistics Update

UPDATE STATISTICS BlogPosts WITH FULLSCAN;
-- Or for all tables:
EXEC sp_updatestats;