SQL Execution Plans

An execution plan, simply put, is the result of the SQL query optimizer’s attempt to calculate the most efficient way to execute your query. Simply put this turns your Here’s what I want into SQL’s Here’s the best way to get it

You can use Microsoft SQL Server Management Studio to see execution plans, this can be done from the tool bar in 3 ways.

To see this tool bar right click your tool bar area and select SQL Editor, you need to have a SQL statement ready and executed for these options to be visible.

SSMS Tool Bar

Estimated Execution Plan

This is a representative view based on the query optimizer - it doesn’t actually run the results of the query. Execution plan can identify possible missing indexes.

Suggested Index

Key Things To Look For

  1. Reading many rows will show as a much thicker line
    1. Check things like INNER JOIN vs LEFT OUTER JOIN
    2. Check things like UNION vs UNION ALL
    3. Do you actually need all the columns you are selecting?
  2. High costs shown as a percentage, example 90%
  3. Scan where we may expect a seek

Sargable

In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.

This means you don’t necessarily need a new index, you may need to tweak a query by questioning its sargability.

1
2
3
4
5
6
7
--Example querys where the cost in the plan are simliar however the first query cost relative to the batch may be significantly lower.

Use MyInstance;
SELECT COUNT(*) FROM dbo.myTable WITH (NOLOCK) WHERE some_date > '28 July 1983';

-- May result in a Scan, where a seek is more efficient
SELECT COUNT(*) FROM dbo.myTable WITH (NOLOCK) WHERE YEAR(some_date) > '1983';

Include Actual Execution Plan

This shows the plan on the back of actually executing your query.

Include Live Query Stats

This shows the plan and stats while the query is executing.

References