Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 39 ยท Indexes and Performance

Execution Plans and Statistics

Read SQL Server execution plans: scans, seeks, joins, estimated versus actual rows, STATISTICS IO and TIME, how statistics drive the optimizer and how to fix bad estimates.

When you send a query, SQL Server does not run it as written. SQL is declarative: you describe the result, and the query optimizer chooses how to produce it, which indexes to use, in which order to join tables and with which join algorithm. The chosen strategy is the execution plan. Reading plans is the core skill of performance tuning, because they show where the work really goes.

This lesson explains how to get plans, how to read them and how statistics influence them. The plans below are from SQL Server and are read-only; the editor examples use SQLite's plan viewer for the same ideas. See also query plans and query optimization.

Getting a plan

  • In SSMS press Ctrl+L for the estimated plan (the query isn't run) or Ctrl+M to include the actual plan with results.
  • SET SHOWPLAN_XML ON returns the estimated plan as XML.
  • Query Store and the plan cache DMVs (sys.dm_exec_query_plan) give plans of past queries (next lesson).

Plans read right to left, top to bottom: data flows from the rightmost operators (reading tables) to the leftmost (returning rows). Arrow thickness shows row counts.

The operators you will see most

Operator Meaning
Clustered Index Scan / Table Scan Reads the whole table
Clustered Index Seek Navigates the B-tree to specific rows
Index Seek / Index Scan Same for a nonclustered index
Key Lookup (RID Lookup for heaps) Fetches missing columns row by row
Nested Loops For each outer row, probe the inner side: good for few rows
Hash Match Builds a hash table: good for large, unsorted inputs
Merge Join Merges two inputs already sorted on the key
Sort Sorts rows; may spill to tempdb
Stream Aggregate / Hash Aggregate GROUP BY
Parallelism (Gather Streams) Work was spread over several CPUs

A scan is not always bad: for a small table or a query that needs most of the rows, a scan is the best plan. A seek is not always good either: 100,000 seeks plus key lookups lose to one scan.

flowchart RL
  A["Index Seek: Orders by CustomerId"] --> B["Nested Loops Join"]
  C["Clustered Index Seek: Customers"] --> B
  B --> D["Compute Scalar"]
  D --> E["SELECT"]

Read from the right: the seeks feed the join, which feeds the result.

Estimated versus actual rows

Each operator shows estimated rows (the optimizer's guess) and, in an actual plan, actual rows. A big gap, such as an estimate of 1 and an actual of 500,000, is the most common root cause of slow queries: the optimizer picked a plan for the wrong size. It might choose a nested loop for a million rows, or give a sort too little memory (look for a yellow warning triangle about a spill).

Also look at:

  • Thick arrows feeding a small result: data filtered too late.
  • Key Lookup with a high operator cost (see covering indexes).
  • Warnings: implicit conversion, missing statistics, spills, no join predicate.
  • Cost % as a rough pointer only. It is always an estimate, even in an actual plan.

STATISTICS IO and TIME

Two session settings quantify a query's real work:

SQL
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT OrderId, Total FROM dbo.Orders WHERE CustomerId = 42;
TEXT
Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, ...
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

Logical reads (pages read from memory) is the most stable metric for comparing versions of a query. Fewer pages is better, and it doesn't depend on how busy the server is. Elapsed time varies with the cache and load.

Statistics

To estimate how many rows match a predicate, the optimizer uses statistics: for each index (and for columns SQL Server auto-creates statistics on), a histogram of up to 200 value ranges and counts, plus the density of values.

SQL
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CustomerId');

Statistics go stale as data changes. By default SQL Server updates them automatically after roughly 20% of the rows change, a threshold that scales better on newer versions, and that can be too late for big tables. Update by hand when estimates look wrong:

SQL
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
-- or for the whole database
EXEC sp_updatestats;

Keep AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS on, and schedule regular updates as part of maintenance.

Bad estimates: common causes

  • Stale statistics or no statistics.
  • Skewed data (one customer has 50% of the orders) with a plan built for a typical value: parameter sniffing, see stored procedures.
  • Functions on columns, table variables (older versions) and multi-statement functions: the optimizer guesses.
  • Correlated predicates (City = 'Paris' AND Country = 'France'): the optimizer multiplies selectivities and underestimates.

Seeing a plan in the editor

SQLite's plan output is plain text, but it tells the same story. Watch how it picks a join order and index:

SQL A join plan

Press Run (or Ctrl + Enter) to see the result here.

It scans customers (small, filtered) and probes orders through the index for each match: the SQLite version of a Nested Loops join with an Index Seek on the inner side. SQLite can also refresh its statistics, like UPDATE STATISTICS:

SQL ANALYZE refreshes statistics

Press Run (or Ctrl + Enter) to see the result here.

The stat column reads "rows, rows-per-key": 1000 rows, about 100 rows per distinct grp value. That is the same kind of density information SQL Server keeps.

A tuning checklist

  1. Capture the actual plan and STATISTICS IO.
  2. Compare estimated and actual rows; find where they diverge.
  3. Look for scans that should be seeks, key lookups and spills.
  4. Fix the cause: index, SARGable predicate, statistics, rewrite.
  5. Measure again; keep the change only if reads or time dropped.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In which direction should you read a graphical execution plan to follow data flow?

    Data flows from the rightmost operators (table access) to the leftmost (the result). Execution is driven from the left.
  2. 2. What does a large gap between estimated and actual rows usually indicate?

    Plan choices depend on row estimates. When the estimates are far off, join types, memory grants and index choices are suspect.
  3. 3. Which metric is the most stable for comparing two versions of a query?

    Logical reads count pages touched and do not vary with cache state or server load the way time does.
  4. 4. What is a histogram in SQL Server statistics?

    Each statistics object keeps up to 200 steps describing how values are distributed in the leading column.

Summary

  • The optimizer picks a plan from statistics; read plans right to left and compare estimated and actual rows.
  • Seeks, scans, lookups and joins each have their place; judge them by the numbers.
  • SET STATISTICS IO ON gives logical reads, the best before/after metric.
  • Keep statistics fresh and fix causes of bad estimates.

Next: Query Store and a tuning workflow