Indexes are the primary lever for SQL Server query performance. The optimizer chooses seek vs scan based on indexes, statistics, and predicates — understanding both helps you fix slow queries systematically.

Clustered vs Nonclustered

Type Count per Table Storage
Clustered One (usually PK) Data rows sorted by key
Nonclustered Up to 999 (999 + clustered) Separate B-tree with row locator 
  -- Default: PK creates clustered index
CREATE TABLE dbo.Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,  -- Clustered by default
    CustomerID INT NOT NULL,
    CreatedAt DATETIME2(3) NOT NULL,
    Total DECIMAL(18,2) NOT NULL
);

-- Nonclustered for lookups
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID)
INCLUDE (Total, CreatedAt);

Changing clustered key is expensive — choose narrow, increasing keys (IDENTITY).

Index Patterns 

  -- Composite — equality columns first, then range/sort
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON dbo.Orders (CustomerID, CreatedAt DESC)
INCLUDE (Total, Status);

-- Filtered — smaller index for skewed predicates
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON dbo.Orders (CustomerID, CreatedAt)
WHERE Status = N'Pending';

-- Unique
CREATE UNIQUE NONCLUSTERED INDEX IX_Users_Email
ON dbo.Users (Email);

-- Columnstore — analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Fact
ON dbo.SalesFacts (SaleDate, RegionID, ProductID, Amount);

Covering Indexes

When all query columns appear in the index key or INCLUDE list, the optimizer can satisfy the query without key lookups:

  SELECT CustomerID, Total, CreatedAt
FROM dbo.Orders
WHERE CustomerID = 42 AND CreatedAt >= '2024-01-01';
-- IX_Orders_Customer_Date with INCLUDE (Total) covers this

Watch INCLUDE column bloat — wide indexes slow INSERT/UPDATE/DELETE.

Reading Execution Plans

Enable Include Actual Execution Plan in SSMS (Ctrl+M):

Operator Concern
Table Scan / Clustered Index Scan Large reads — index candidate
Key Lookup (RID Lookup) Noncovering index — add INCLUDE
Sort Memory grant; missing index on ORDER BY
Hash Match Large builds — stats or index issue
Parallelism Good for big scans; bad for tiny queries 
  SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM dbo.Users WHERE Email = N'[email protected]';

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Compare logical reads before and after index changes.

Missing Index DMVs 

  SELECT TOP 20
    migs.avg_user_impact,
    migs.user_seeks + migs.user_scans AS total_usage,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

Caution: DMVs suggest indexes per query fragment — validate with full workload. Drop unused indexes:

  SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() AND i.type_desc = N'NONCLUSTERED'
ORDER BY s.user_updates DESC, s.user_seeks + s.user_scans ASC;

High updates + zero reads = drop candidate.

Statistics 

  -- Update all stats on table
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Auto stats usually sufficient — verify for large tables after bulk load
SELECT
    OBJECT_NAME(object_id) AS table_name,
    name AS stats_name,
    STATS_DATE(object_id, stats_id) AS last_updated
FROM sys.stats
WHERE object_id = OBJECT_ID(N'dbo.Orders');

Stale statistics cause estimated vs actual row mismatches in plans.

Index Maintenance 

  -- Check 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(), NULL, NULL, NULL, N'LIMITED') AS ips
INNER JOIN sys.indexes AS 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;

-- Reorganize (online, light fragmentation)
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;

-- Rebuild (heavy fragmentation, Enterprise online)
ALTER INDEX ALL ON dbo.Orders REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);

Schedule maintenance during low-traffic windows — rebuild generates log.

Query Hints (Last Resort) 

  SELECT * FROM dbo.Orders WITH (INDEX(IX_Orders_Customer_Date))
WHERE CustomerID = 5;

SELECT * FROM dbo.LargeTable OPTION (MAXDOP 4, RECOMPILE);

Hints override optimizer — document why; remove when underlying issue fixed.

Common Mistakes

Mistake Effect
Index every column Write slowdown, storage cost
Wrong column order in composite Index unused
Functions on indexed columns Scan — use persisted computed column
Ignoring included columns Key lookups remain
Never maintaining stats Bad plans after data growth

Performance Tips

  1. Index foreign keys — joins and cascades benefit
  2. Avoid SELECT * — prevents covering index use
  3. Parameterize ad-hoc SQL from apps — plan reuse
  4. Use Query Store to catch plan regressions
  5. Test on production-like data volumes — 100-row dev DB hides scans

Production Scenario: Hot Query Fix

Problem: Dashboard query scans Orders (50M rows).

  -- Before: 45 seconds, 2M logical reads
SELECT COUNT(*) FROM dbo.Orders WHERE Status = N'Pending' AND CreatedAt >= DATEADD(day, -7, GETDATE());

-- Fix: filtered index
CREATE NONCLUSTERED INDEX IX_Orders_Pending_Recent
ON dbo.Orders (CreatedAt) INCLUDE (OrderID)
WHERE Status = N'Pending';

Re-test: seek + partial index — sub-second response.

Troubleshooting

Symptom Check
Index not used Implicit conversion, wrong predicate, outdated stats
Scan despite index OR across columns, LIKE '%x', low selectivity
Key lookup storm Add INCLUDE columns
Slow after upgrade CE version change — Query Store force plan 
  ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
-- Temporary — investigate root cause

Proper indexing is iterative — measure with plans and IO stats, change one index at a time, and validate under load.