Do I need SQL Server Management Studio to read an execution plan?

No — SET SHOWPLAN_TEXT ON returns the plan as plain text rows from sqlcmd or any client, and SET STATISTICS IO ON reports the actual page reads a query performed. SSMS's graphical plan viewer is more visual for complex plans, but the same information is available from a terminal with no GUI at all.

What does 'logical reads' actually measure?

The number of 8KB pages SQL Server had to read from the buffer cache to satisfy the query — not disk I/O specifically (physical reads track that separately), but the amount of data the engine had to touch. Fewer logical reads means less work per query, which is why comparing this number before and after an index is a direct, un-hand-wavy measure of whether the index actually helped.

Why was there no separate table scan operator — it said 'Clustered Index Scan'?

Every SQL Server table with a clustered index (the default for a PRIMARY KEY) has its data physically stored in that index's leaf level — there's no separate heap to scan. A 'table scan' in SQL Server is a full read of the clustered index when no better index applies, which is exactly what the plan showed before ix_books_title existed.

What's the difference between an Index Seek and an Index Scan?

A Seek navigates the index's B-tree structure directly to the matching row(s), touching only the pages actually needed — the 4 logical reads in this lesson. A Scan reads every leaf page of the index in order, checking each one against the filter — the 40 logical reads before the index existed. Seek is what you want whenever a query returns a small fraction of a table's rows.

Does adding an index always reduce logical reads for every query?

No — an index only helps queries whose filter or join actually matches that index's key columns. A query filtering on a different column entirely gets no benefit from ix_books_title, and every index adds write overhead (it has to be maintained on every INSERT/UPDATE/DELETE) — an index is a targeted tool for specific query patterns, not a blanket performance switch.

Reading a SQL Server Execution Plan From the Terminal

No SSMS required: SET STATISTICS IO and SET SHOWPLAN_TEXT reveal a Clustered Index Scan reading 40 pages, and the Index Seek that replaces it after one CREATE INDEX -- a real 10x drop in logical reads.

By SkillByExample · Published Sep 13, 2026

sqlserver tsql execution-plans indexing sqlcmd

SET STATISTICS IO and SET SHOWPLAN_TEXT turn “the query got faster” into an actual number — no SSMS, no graphical plan viewer, just sqlcmd and a terminal.

The query, before any index

SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM books WHERE title = 'Book Title 2500';
GO

sqlcmd -S localhost -U sa -P '<password>' -C -i plan_before.sql

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Clustered Index Scan(OBJECT:([bookshop].[dbo].[books].[PK__books__490D1AE1E186C24D]), WHERE:([bookshop].[dbo].[books].[title]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))

Clustered Index Scan — not a separate “table scan” operator, because in SQL Server a table with a clustered index (the default for a PRIMARY KEY) stores its actual data in that index’s leaf pages. Scanning the whole table means scanning the whole clustered index.

Measuring the actual cost

SET STATISTICS IO ON;
SELECT * FROM books WHERE title = 'Book Title 2500';

Table 'books'. Scan count 1, logical reads 40, physical reads 0, page server reads 0,
read-ahead reads 0, ...

40 logical reads — every page of the books table’s clustered index had to be checked, since nothing narrows the search before that.

The fix: an index on the filtered column

CREATE INDEX ix_books_title ON books(title);

sqlcmd -S localhost -U sa -P '<password>' -C -d bookshop -Q "SET STATISTICS IO ON; SELECT * FROM books WHERE title = 'Book Title 2500';"

book_id     title                        author_id   price    published_year
----------- ---------------------------- ----------- -------- --------------
       2500 Book Title 2500                      50    36.77            2011

(1 rows affected)
Table 'books'. Scan count 1, logical reads 4, physical reads 0, ...

40 logical reads down to 4 — a real, measured 10x drop, not an assumption. The plan now shows why:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([bookshop].[dbo].[books].[book_id]))
       |--Index Seek(OBJECT:([bookshop].[dbo].[books].[ix_books_title]), SEEK:([bookshop].[dbo].[books].[title]=N'Book Title 2500') ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([bookshop].[dbo].[books].[PK__books__490D1AE1E186C24D]), SEEK:([bookshop].[dbo].[books].[book_id]=[bookshop].[dbo].[books].[book_id]) LOOKUP ORDERED FORWARD)

Index Seek on the new index finds the matching row directly by navigating its B-tree, then a Clustered Index Seek “lookup” fetches the rest of the row’s columns using the book_id found in the first step — two seeks instead of one full scan.

Practice

1. Query a column with no index and confirm the scan comes back.

SET STATISTICS IO ON;
SELECT * FROM books WHERE published_year = 2011;

Table 'books'. Scan count 1, logical reads 40, physical reads 0, ...

40 logical reads again — published_year has no index, so every query filtering on it scans the whole clustered index regardless of how selective the filter looks.

2. Check exactly which indexes exist on a table.

SELECT name FROM sys.indexes WHERE object_id = OBJECT_ID('books');

name
------------------------------------
PK__books__490D1AE1E186C24D
ix_books_title

(2 rows affected)

sys.indexes lists every index by name, including the automatically-named clustered primary key index and the one just created — the direct way to check “does an index for this query already exist” without guessing from query performance alone.

3. Drop the index and confirm the scan and the 40-page cost return.

DROP INDEX ix_books_title ON books;

Table 'books'. Scan count 1, logical reads 40, physical reads 0, ...

Same query, same 40 logical reads as the very first measurement — the index was the entire reason for the improvement, confirmed by removing it and watching the cost come back exactly.

Next: clustered vs. nonclustered indexes — a genuinely SQL-Server-specific distinction.

The query, before any index

Measuring the actual cost

The fix: an index on the filtered column

Practice

Frequently Asked Questions