Scribd
Upload
13 views3 pages

SQL Server Query Processing Explained

When a client sends a SQL query, it is processed through the SQL Server via TDS and SNI, entering the Database Engine where it is handled by the Relational Engine and Storage Engine. The Relational Engine parses and optimizes the query, while the Storage Engine manages data access, distinguishing between read and write operations. Data is stored in MDF/NDF files and changes are logged in the LDF file, with transaction and locking mechanisms ensuring consistency.

Uploaded by

Shreemahathma Hd
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views3 pages

SQL Server Query Processing Explained

When a client sends a SQL query, it is processed through the SQL Server via TDS and SNI, entering the Database Engine where it is handled by the Relational Engine and Storage Engine. The Relational Engine parses and optimizes the query, while the Storage Engine manages data access, distinguishing between read and write operations. Data is stored in MDF/NDF files and changes are logged in the LDF file, with transaction and locking mechanisms ensuring consistency.

Uploaded by

Shreemahathma Hd
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Interview Answer (Simple English, Clear Flow)

“When a client sends a SQL query, it first reaches the SQL Server through a protocol called TDS – Tabular
Data Stream. This goes through the SQL Server Network Interface (SNI), which handles how the request
comes in — whether it’s via Shared Memory, TCP/IP, or Named Pipes.”

“Then, the query enters the SQL Server Database Engine, where it is handled in two parts: the Relational
Engine and the Storage Engine.”

Relational Engine (Query Processor)

“The Relational Engine first parses the query to check if it is written correctly (syntax and semantics).
Then it checks if a similar query plan already exists in the plan cache. If not, the Query Optimizer creates
a new plan — it could be a quick plan or a fully optimized one depending on the query. Once the plan is
ready, the Query Executor runs the plan and connects to the Storage Engine to get or update the data.”

Storage Engine (Accessing Data)

“Now, the Storage Engine checks if the query is a read (SELECT) or a write (like INSERT, UPDATE,
DELETE).”

If it’s a SELECT query:

“It checks in memory (Buffer Cache) if the data is already available. If yes, that’s called soft parsing — it
just reads and returns. If not, that’s called hard parsing — it fetches from disk into memory, then returns
the result.”

If it’s a Non-SELECT query (INSERT, UPDATE, DELETE):

“SQL Server first writes this change to the Transaction Log (LDF file) using the Log Manager. Then, it
updates the data in memory (Buffer Cache). That updated page in memory is called a dirty page —
because it’s changed in memory but not yet saved to disk.”

“Later, SQL Server will write that dirty page to the data file (MDF or NDF) using a process called
Checkpoint or Lazy Writer.”

Lock & Transaction Control

“The Lock Manager ensures there are no conflicts — for example, two users updating the same data at
the same time. The Transaction Manager controls the transaction flow — and makes sure everything is
committed or rolled back properly.”
Data Files in Physical Storage

• MDF is the main data file — it stores your actual tables, indexes, etc.

• NDF is optional — extra storage files for spreading data.

• LDF is the transaction log file — it stores all changes to help with rollback and recovery.

Example Summary You Can Use in Interview:

“So to summarize: The query is parsed, optimized, and executed. If it’s a SELECT, data is fetched from
memory or disk. If it’s a non-SELECT, it’s logged first, then memory is updated, then eventually the
changes go to disk. Everything is stored in MDF/NDF files, and logs go into the LDF file. SQL Server
ensures consistency with transaction and locking mechanisms.”

If you’d like, I can now help you memorize this flow with a one-page cheat sheet or practice question
answers. Want that?

You might also like