Execution plans
An execution plan shows how SQL Server processes a query: which indexes it uses, where it reads whole tables, where rows get sorted. NovaSql displays plans graphically and tells you concretely what stands out.
Two kinds of plan
- Estimated Plan: button in the toolbar. SQL Server plans the query without running it. Good for expensive queries and for
UPDATE/DELETEstatements that should not change anything. - Include Plan: switch in the toolbar. When it is on, the next run also returns the actual plan, with real row counts and executions per operator.
The plan appears in the result area on the Plan tab. With several statements there is one section per statement with its cost.
Diagram
The diagram shows operators as boxes connected by arrows. The mouse wheel zooms, dragging pans, Reset restores the view.
Clicking an operator opens its details: object, estimated and actual rows, executions, operator and subtree cost, share of the total cost, warnings and all attributes.
Tree
The same plan as an indented list. Handy for very large plans.
Suggestions
NovaSql examines the plan and lists its findings, each with a severity:
| Finding | Meaning |
|---|---|
| Missing index recommended | SQL Server estimates that an index would speed the query up considerably. The ready-made CREATE INDEX can be copied. |
| Implicit type conversion | CONVERT_IMPLICIT probably prevents an index seek. Align the data types or convert explicitly. |
| Expensive scan | A scan accounts for more than 20 % of the cost. A matching index could allow a seek instead. |
| Key/RID lookup | The index used does not cover all columns. A covering index with INCLUDE can fix that. |
| Operator warnings | What SQL Server itself reports in the plan, such as a spill to tempdb or a join without a join predicate. |
| Estimate off | Estimated and actual rows differ by more than ten times. Stale statistics are a common cause: check UPDATE STATISTICS. |
Estimate errors and actual numbers are only available with Include Plan, not with the estimated plan.
A suggested index is a hint, not an order. Before creating it, check whether a similar index already exists: every index costs storage and slows down writes.
XML
The plan in raw form. Copy XML puts it on the clipboard. Open as .sqlplan tab opens it in a query tab; Save As then produces a .sqlplan file that SQL Server Management Studio can open too.