How SQL Server runs a query: plans and indexes

The optimiser, indexes and the difference between a seek and a scan.

UnderstandStudy an examplePractise

Understand the concept

When SQL Server receives a query it parses it, then the query optimiser picks the execution plan with the lowest estimated cost. That decision relies on statistics: estimates of how many rows hold which values. The chosen plan is cached; when a parameterised query of the same shape arrives again it is not recompiled. That is also where much of the performance benefit of parameterised queries comes from.

An index is like the index at the back of a book. With an index on Slug, ‘WHERE Slug = @p’ goes straight to the row (Index Seek). Without it SQL Server must read the whole table (Scan). On a small table nothing shows; on millions of rows it is the difference between seconds and milliseconds.

Indexes are not free: every INSERT and UPDATE maintains them, and they use disk space. The right index is chosen by looking at real queries. Reading the execution plan (‘Include Actual Execution Plan’ in SSMS) and measuring pages read (SET STATISTICS IO ON) is how you measure instead of guessing. See the SQL learning centre’s performance module for details.

WHEN TO USE IT

The slowest part of an app is often a missing or wrong index.

COMMON PITFALL

Indexing every column; writes slow down and the optimiser’s job gets harder.

01 / PRACTICE

One topic. Three levels of depth.

Illustrative example

CREATE UNIQUE INDEX IX_Posts_Slug ON dbo.Posts (Slug);
-- WHERE Slug = @p sorgusu artık Index Seek kullanır

An index for a frequently searched, unique column.

This page connects to no real server or database. Code samples are illustrative for .NET 8, Next.js and SQL Server; timings are example values in the simulation, not measurements. Try samples only in your own development environment; never share real connection strings, tokens or passwords.

02 / YOUR TURN

Try it yourself first.

The ‘latest 10 published posts’ query takes 900 ms on a 200k-row table; the plan shows ‘Clustered Index Scan’. Query: WHERE IsPublished = 1 ORDER BY PublishedAtUtc DESC. What do you suggest?

Show the solution and explanation

Worked solution

CREATE INDEX IX_Posts_Published_Date
  ON dbo.Posts (IsPublished, PublishedAtUtc DESC)
  INCLUDE (Title, Slug);
-- Seek + sıralı okuma; TOP 10 ilk 10 satırda durur.

With filter and sort columns in the key and returned columns included, the query finishes without scanning or sorting the table.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.