SQL Server Q&A Topic 2: SQL Server Architecture & Internals
SQL Server Interview Questions & Answers
Topic 2: SQL Server Architecture & Internals
Questions 11–20 | Beginner to Intermediate
This section explains how SQL Server processes queries, stores data, manages memory, writes transaction log records and recovers databases after an unexpected shutdown.
Understanding these internal components helps database professionals troubleshoot performance, storage, memory and recovery-related issues more effectively.
11. What happens internally when SQL Server executes a query?
Answer:
When SQL Server receives a query, it passes through several internal
stages before results are returned to the application.
- Parsing: SQL Server checks the T-SQL syntax and converts the query into an internal representation known as a parse tree.
- Binding: SQL Server verifies that referenced objects and columns exist and checks whether the user has the required permissions.
- Optimization: The Query Optimizer evaluates candidate execution strategies and selects a low-cost execution plan based on available indexes, statistics, estimated rows and other factors.
- Plan cache lookup: SQL Server may reuse a suitable execution plan that already exists in the plan cache. Otherwise, a new plan is compiled.
- Execution: The Execution Engine performs the operations defined in the selected execution plan.
- Data access: The Storage Engine retrieves the required pages from the Buffer Pool. If a page is not already in memory, SQL Server reads it from storage.
- Result: SQL Server processes the rows and returns the requested result to the client application.
A strong answer should mention parsing, binding, optimization, plan compilation or reuse, execution and data access through the Buffer Pool.
12. What is an 8 KB data page?
Answer:
A page is the fundamental unit of data storage in SQL Server.
Each page has a fixed size of 8 KB.
A data page contains a 96-byte page header, data rows and a row-offset array at the end of the page.
- Page size: 8 KB or 8,192 bytes
- Page header: 96 bytes
- Approximate space available for rows: 8,096 bytes, excluding row-offset entries and other overhead
SQL Server reads data pages from storage into memory and performs most data access operations against pages held in the Buffer Pool.
Common SQL Server page types include:
- Data pages
- Index pages
- LOB data pages
- Row-overflow data pages
- Allocation pages
SQL Server generally performs storage and memory operations at the page level rather than reading individual rows directly from disk.
13. What is an extent?
Answer:
An extent is a collection of eight physically contiguous 8 KB pages.
Therefore, the total size of an extent is:
8 pages × 8 KB = 64 KBSQL Server uses extents to manage the allocation of storage space efficiently.
Historically, SQL Server described two types of extents:
- Uniform extent: All eight pages belong to the same database object.
- Mixed extent: Pages within the extent can belong to different database objects.
Modern SQL Server versions generally favor uniform extent allocation for user objects. This reduces allocation contention and simplifies space management.
Remember the basic relationship: 1 page = 8 KB and 1 extent = 8 pages = 64 KB.
14. What are PFS, GAM and SGAM pages?
Answer:
PFS, GAM and SGAM are special allocation pages used by SQL Server
to track space usage and page allocation inside database files.
-
PFS: Page Free Space
Tracks page allocation status and the approximate amount of free space available on individual pages. -
GAM: Global Allocation Map
Tracks whether extents are free or allocated. -
SGAM: Shared Global Allocation Map
Tracks mixed extents that are allocated but still have one or more unallocated pages available.
In simplified terms:
- PFS tracks page-level space usage.
- GAM identifies free extents.
- SGAM identifies mixed extents with free pages.
Allocation-page contention may be observed in high-concurrency
environments, particularly in tempdb.
15. What is the Buffer Pool?
Answer:
The Buffer Pool is a major area of SQL Server memory used to cache
database pages.
When SQL Server needs a data or index page, it first checks whether that page is already available in memory.
- If the page is available in memory, SQL Server can access it without performing a physical disk read.
- If the page is not available in memory, SQL Server reads it from storage and places it into the Buffer Pool.
Frequently used pages may remain in memory, reducing physical I/O and improving query performance.
Cached pages may include:
- Table data pages
- Index pages
- Allocation pages
- Previously modified pages waiting to be written to storage
Most SQL Server data access takes place through memory. A physical disk read is required only when the requested page is not already present in the Buffer Pool.
16. What is a checkpoint?
Answer:
A checkpoint is a SQL Server process that writes eligible dirty data
pages from the Buffer Pool to database data files.
Checkpoints help reduce the amount of work required during database crash recovery because fewer modified pages remain only in memory.
A checkpoint does not mean that every dirty page in memory is written immediately. SQL Server manages page writes based on checkpoint processing, recovery requirements and other background processes.
Common checkpoint types include:
- Automatic checkpoint
- Indirect checkpoint
- Manual checkpoint
- Internal checkpoint
A manual checkpoint can be issued using:
CHECKPOINT;A checkpoint writes dirty data pages to data files. Transaction log records must already be hardened according to Write-Ahead Logging before their associated data pages can be written.
17. What is a dirty page?
Answer:
A dirty page is a data or index page in the Buffer Pool that has been
modified in memory but whose latest version has not yet been written
to the database data file.
For example, when an UPDATE statement modifies a row,
SQL Server changes the corresponding page in memory. That page becomes
dirty until the modified version is written to storage.
Dirty pages may be written to the data files by:
- Checkpoint processing
- The Lazy Writer
- Other internal SQL Server write processes
A dirty page is different from an uncommitted transaction. A page may contain changes from committed and uncommitted transactions. SQL Server uses the transaction log to maintain consistency and support recovery.
18. What is Write-Ahead Logging?
Answer:
Write-Ahead Logging, commonly called WAL, is the principle that
transaction log records describing a database modification must be
written to stable storage before the related dirty data page can be
written to the database data file.
The simplified sequence is:
- A transaction modifies a page in memory.
- SQL Server generates the corresponding transaction log records.
- The required log records are hardened to the transaction log file.
- The modified data page can later be written to the database data file.
WAL enables SQL Server to recover committed transactions and roll back incomplete transactions after a failure.
Think of WAL as: log first, data page later.
19. Does a clustered index physically sort a table forever?
Answer:
No. A clustered index organizes table data at the leaf level of a
B-tree according to the clustered index key, but it does not guarantee
that rows will remain physically contiguous or permanently ordered on
disk.
As data is inserted, updated and deleted, SQL Server may experience:
- Page splits
- Logical fragmentation
- Page movement
- Pages allocated in different physical locations
The clustered index maintains the logical ordering of keys through its B-tree structure.
A query should never rely on a clustered index to guarantee the order of returned rows.
To guarantee result ordering, use an explicit
ORDER BY clause.
SELECT
CustomerID,
CustomerName
FROM dbo.Customers
ORDER BY CustomerID;A clustered index provides logical key ordering at the leaf level, but only
ORDER BY guarantees the presentation order of
a query result.
20. What happens during SQL Server crash recovery?
Answer:
Crash recovery is the process SQL Server uses to return a database
to a transactionally consistent state after an unexpected shutdown
or failure.
SQL Server uses information stored in the transaction log to perform recovery.
The main recovery phases are:
-
Analysis:
SQL Server examines the transaction log to determine the recovery starting point, identify dirty pages and find active transactions. -
Redo:
SQL Server reapplies logged changes that may not yet be reflected in the database data files. -
Undo:
SQL Server rolls back transactions that were incomplete when the failure occurred.
The simplified recovery principle is:
- Changes from transactions that should be preserved are rolled forward.
- Changes from incomplete transactions are rolled back.
Remember the three crash-recovery phases: Analysis, Redo and Undo.
Simplified SQL Server Query Flow
Client Query
↓
Parser and Binder
↓
Query Optimizer
↓
Execution Plan
↓
Execution Engine
↓
Storage Engine
↓
Buffer Pool
↓
Data and Index Pages
Interview Quick Recap
- SQL Server parses, binds, optimizes and executes a query.
- An SQL Server page is 8 KB.
- An extent contains eight pages and is 64 KB.
- PFS, GAM and SGAM pages track space allocation.
- The Buffer Pool caches database pages in memory.
- A dirty page has been modified in memory but has not yet been written to the data file.
- A checkpoint writes eligible dirty pages to database data files.
- Write-Ahead Logging requires the relevant log records to be hardened before their associated data pages are written.
- A clustered index maintains logical key ordering but does not guarantee query-result order.
- Crash recovery includes Analysis, Redo and Undo phases.
← Topic 1: SQL Server Fundamentals
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning
Comments
Post a Comment