Index and Query Optimization

Optimising queries and indexes

Index types and when to use them

SQL Server Index Types

Index Type	Structure	Best For
Clustered index	B-tree, data stored in leaf nodes (IS the table)	Primary key, range scans, one per table
Non-clustered index	B-tree, pointers to data rows	Filtered lookups, covering queries, multiple per table
Columnstore index	Column-oriented compressed storage	Analytics, aggregations, large scans
Filtered index	Non-clustered with a WHERE clause	Queries that always filter on a specific value (e.g., Status = 'Active')
Included columns index	Non-clustered + extra columns in leaf	Covering queries (avoid key lookups)
Full-text index	Inverted index for text search	CONTAINS, FREETEXT queries on text columns

Identifying missing indexes

Using DMVs

-- Top missing index recommendations
SELECT TOP 20
    CONVERT(DECIMAL(18,2), migs.avg_user_impact) AS avg_improvement_pct,
    migs.user_seeks,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    'CREATE NONCLUSTERED INDEX IX_' +
    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 create_index_statement
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact * migs.user_seeks DESC;

Using Query Store

Query Store shows which queries are expensive. Cross-reference with their execution plans to see where index seeks could replace scans.

Using execution plan warnings

Yellow triangle warnings in execution plans often indicate missing indexes — SSMS even suggests the CREATE INDEX statement.

Identifying unused indexes

Over-indexing is as bad as under-indexing:

-- Indexes that are never used for reads but are maintained on writes
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    ius.user_seeks, ius.user_scans, ius.user_lookups,
    ius.user_updates  -- writes (maintenance cost)
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE ius.database_id = DB_ID()
  AND i.type_desc = 'NONCLUSTERED'
  AND ius.user_seeks + ius.user_scans + ius.user_lookups = 0
  AND ius.user_updates > 0
ORDER BY ius.user_updates DESC;

Rule of thumb: If an index has zero seeks/scans/lookups but thousands of updates, it’s costing you write performance with no read benefit. Consider dropping it.

Covering indexes and included columns

A covering index contains all columns a query needs — no need to go back to the base table (no key lookup).

-- Query: find active orders with customer name and amount
SELECT CustomerName, OrderAmount
FROM Orders
WHERE Status = 'Active' AND OrderDate > '2025-01-01';

-- Covering index: key columns for filtering + included columns for the SELECT
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders (Status, OrderDate)
INCLUDE (CustomerName, OrderAmount);

Why INCLUDE? Included columns are stored only in the leaf level (not in the B-tree navigation). They make the index cover the query without bloating the index key.

Query construct modifications

Common optimisation patterns

Anti-Pattern	Problem	Fix
SELECT *	Returns unnecessary columns, prevents covering indexes	Select only needed columns
WHERE YEAR(OrderDate) = 2025	Function on column prevents index seek	WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
Cursors for row-by-row processing	Extremely slow for large sets	Set-based operations (UPDATE … FROM, MERGE)
WHERE column LIKE '%search%'	Leading wildcard prevents index seek	Full-text search, or LIKE 'search%' if possible
WHERE column <> 'value'	Inequality scans most of the index	Rewrite as positive filter if possible
Implicit conversions	Type mismatch prevents index use	Match parameter types to column types
Multiple singleton queries in a loop	Network round-trips multiply	Batch into set operations or table-valued parameters

SARGable vs non-SARGable predicates

SARGable (Search ARGument able) predicates allow the optimizer to use indexes:

SARGable (Good)	Non-SARGable (Bad)
WHERE Price > 100	WHERE Price + 10 > 110
WHERE Name LIKE 'Smith%'	WHERE Name LIKE '%Smith'
WHERE OrderDate >= '2025-01-01'	WHERE YEAR(OrderDate) = 2025
WHERE Status = 'Active'	WHERE ISNULL(Status, 'Active') = 'Active'

💡 Exam tip: functions on columns

Any function applied to a column in a WHERE clause typically makes the predicate non-SARGable:

• WHERE CONVERT(DATE, CreatedDateTime) = '2025-04-21' — non-SARGable
• WHERE CreatedDateTime >= '2025-04-21' AND CreatedDateTime < '2025-04-22' — SARGable

The exam tests this pattern frequently. Always rewrite function-on-column predicates as range comparisons.

Question

What is a covering index?

Click or press Enter to reveal answer

Answer

An index that contains all columns a query needs (in key columns + INCLUDE columns). The query can be satisfied entirely from the index without going back to the base table — no key lookup needed.

Click to flip back

Question

What makes a WHERE clause predicate non-SARGable?

Click or press Enter to reveal answer

Answer

Applying a function to the column: WHERE YEAR(Date) = 2025. This prevents index seeks because SQL Server must evaluate the function for every row. Rewrite as a range: WHERE Date >= '2025-01-01' AND Date < '2026-01-01'.

Click to flip back

Question

How do you find unused indexes that are costing write performance?

Click or press Enter to reveal answer

Answer

Query sys.dm_db_index_usage_stats: look for nonclustered indexes where user_seeks + user_scans + user_lookups = 0 but user_updates > 0. These indexes are maintained on every write but never used for reads.

Click to flip back

Knowledge Check

A query filters on OrderDate and Status but the execution plan shows a Key Lookup for CustomerName and Amount. How should Tomas eliminate the lookup?

AAdd CustomerName and Amount as key columns in the existing indexBCreate a new index with INCLUDE (CustomerName, Amount)CCreate a clustered index on OrderDate and StatusDAdd a filtered index on Status = 'Active'

Check Answer

Knowledge Check

Priya's query: WHERE CONVERT(VARCHAR, PhoneNumber) = '555-1234' is doing a full table scan despite an index on PhoneNumber. Why?

AThe index is fragmented and needs rebuildingBThe CONVERT function makes the predicate non-SARGableCPhoneNumber column has too many NULL valuesDThe statistics on PhoneNumber are outdated

Check Answer

Next up: Database Maintenance: Indexes, Statistics, and Integrity — keep your databases healthy with routine maintenance tasks.