SQL Query Processing

Table of Contents Overview Query Optimizer Execution Engine Query Plan Explained Example & Interactive Demo Further Reading

Overview

SQL query processing is the series of steps a relational database engine follows to transform a T‑SQL statement into an executable plan and finally produce the result set. The process can be divided into three major phases:

Parsing & Binding – The query string is parsed into a syntax tree and objects (tables, columns, functions) are validated.
Optimization – The optimizer evaluates alternative logical and physical execution strategies.
Execution – The chosen plan is executed by the execution engine.

Query Optimizer

The optimizer is the heart of the engine. It uses statistics, indexes, and transformation rules to generate the most efficient query plan. Key components include:

Cost Model – Estimates I/O, CPU, and memory consumption for each candidate plan.
Rule‑Based Transformations – Rewrites the query (e.g., predicate push‑down, join reordering).
Enumeration – Generates alternative join orders and access paths.

For detailed information see Query Optimizer.

Execution Engine

Once a plan is chosen, the execution engine carries it out. It consists of:

Operators – Physical implementations like Index Seek, Hash Join, Sort.
Buffers – Memory structures that hold intermediate rows.
Parallelism – The engine can split work across multiple threads/cores.

Learn more in Execution Engine.

Query Plan Explained

A query plan is a tree of operators. Below is an example of a textual plan produced by SQL Server.

SELECT * FROM Sales.Orders o
JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate > '2023-01-01'

-- Execution Plan (estimated)
|--Hash Join(Inner Join, Hash:([c].[CustomerID]), 
   Residual:([o].[CustomerID] = [c].[CustomerID]))
   |--Clustered Index Scan(OBJECT:([Sales].[Orders].[PK_Orders]))
   |   Output List: [o].[OrderID], [o].[CustomerID], ...
   |--Clustered Index Scan(OBJECT:([Sales].[Customers].[PK_Customers]))
       Output List: [c].[CustomerID], [c].[Name], ...

The plan shows two scans (one per table) and a hash join that combines rows based on the equality predicate.

Interactive Demo

Enter a simple SELECT query to see a generated plan (mocked for demo purposes).