DP-800: Indexes, SARGable queries, and reading execution plans (course section 13)

[zkarachiwala]

Exam objective

Design and create appropriate indexes, identify SARGable predicates, and read query execution plans to evaluate and improve query performance
(Secure, optimize, and deploy — Evaluate query performance — 35–40%)

Read first

• Indexes in SQL Server
• SARGable predicates
• Reading execution plans

What to build

An index analysis on the three most common TimeTracker query patterns, with before/after execution plans showing the improvement.

Your task

1. Enable actual execution plans in SSMS (Ctrl+M) and run this query without any non-clustered indexes:

   SELECT * FROM app.TimeEntries
   WHERE UserId = 'user@example.com' AND StartTime >= '2026-01-01';

   Note: Table Scan or Clustered Index Scan in the plan.

2. Identify whether these predicates are SARGable (can use an index seek):

   WHERE YEAR(StartTime) = 2026              -- not SARGable (function wraps column)
   WHERE StartTime >= '2026-01-01'           -- SARGable ✓
   WHERE LOWER(Description) = 'meeting'     -- not SARGable
   WHERE Description = 'meeting'            -- SARGable ✓ (case-insensitive collation)

3. Create a composite index and observe the plan change:

   CREATE NONCLUSTERED INDEX IX_TimeEntries_UserId_StartTime
   ON app.TimeEntries (UserId, StartTime)
   INCLUDE (Description, EndTime, ProjectId);

4. Re-run the query — confirm Index Seek replaces the Scan in the execution plan

5. Create two more indexes for the other common patterns (project filter, active timer lookup) and verify each produces a Seek

Explore in SSMS / Azure Data Studio

-- Find missing index suggestions from the optimizer:
SELECT
    mid.statement AS TableName,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS ImprovementMeasure,
    'CREATE INDEX IX_' + REPLACE(mid.statement, '.', '_') +
        ' ON ' + mid.statement +
        ' (' + ISNULL(mid.equality_columns, '') +
        ISNULL(', ' + mid.inequality_columns, '') + ')' +
        ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS CreateIndexStatement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY ImprovementMeasure DESC;

-- Check index usage stats (are your new indexes actually being used?):
SELECT i.name, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE ius.database_id = DB_ID()
AND i.object_id = OBJECT_ID('app.TimeEntries');

Exam checkpoint

• What is a SARGable predicate and why does it matter for index usage?
• What is the difference between an Index Seek and an Index Scan in an execution plan?
• What is an INCLUDE column in a non-clustered index and when would you use one?
• What is a key lookup (bookmark lookup) and why is it a performance concern?
• What is a covering index?

[zkarachiwala]

Cert tracking: study-plans#4 — close both when done (Claude will handle both if asked).