Query planner

As explained in 100 SQL Server Mistakes and How to Avoid Them

A query optimizer selects an efficient execution plan for a declarative database query.

A query planner determines an efficient way to return the results described by declarative T-SQL. After parsing and algebraization produce a normalized query tree, it evaluates plans using object types, data types, statistics, indexes, and other metadata. section 1.2.1

Figure 1.3 The flow of a query through the Database Engine

Follow a query from the protocol layer through parsing, algebraization, the relational engine, and optimization. section 1.2.1

Avoid working against the planner:

• Do not assume a manually prescribed plan is better. The planner usually makes good decisions, although a suboptimal plan can harm performance. section 10.5
• Use query hints only in exceptional circumstances. Hints override optimizer decisions and can become overprescriptive. section 10.5
• Before forcing a join operator, investigate indexes and other causes; maintaining indexes and statistics helps the planner choose effectively. section 1.2.1 section 10.5

Nested Loops or Hash Join?

A join between Marketing.marketing.Impressions and MarketingArchive.dbo.ImpressionsArchive takes 19 seconds with Nested Loops. A Hash Join hint changes the selected operator, but creating indexes on the join columns is another remedy to investigate first.

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes

Explains parsing, algebraization, metadata, costs, and physical joins, but does not detail the optimizer’s full algorithm.

Examples

Uses an impressions join, execution-plan figures, join hints, and indexes on join columns to illustrate plan selection.

What it assumes

Assumes familiarity with T-SQL queries, joins, indexes, and statistics while introducing the optimizer’s role from the execution pipeline.

What you can do after

Diagnose/debug with it by examining plans, questioning forced operators, and checking indexes and statistics before applying hints.

How it connects

Connects declarative language with parsing, statistics, indexes, query hints, join operators, Query Store, and intelligent query processing.

liveBookmanning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

• 

  liveBookmanning.com
  PostgreSQL Mistakes and How to Avoid ThemA query planner or optimizer is the database component that selects and may transform an execution strategy for a SQL query.

Compare how 2 books explain query planner →

Related concepts

• execution planproduces · An execution plan is a structured description of the operations or tasks arranged to carry out a query or other task.
• indexinguses · Indexing is the organization or use of positions, keys, or lookup structures to identify and retrieve selected data or elements.
• statisticsuses · Statistics is the discipline and set of methods for collecting, summarizing, analyzing, and interpreting data, including numerical descriptions used to characterize observations and support inference.
• hidden indexcontrasts with · A hidden index is an index configured to be omitted from certain ordinary discovery, listing, planning, or selection operations.
• composite indexuses · A composite index is a database index whose key contains multiple fields or columns, ordered according to the index definition.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.