Spotlight on SQL Server 10.0.3 - Getting Started Guide

1

SQL Statements

The client application sends "batches" of SQL statements to SQL Server for execution.

2

Syntax Check

The Parser checks the syntax of the SQL statements.

3

Cache Lookup

The Parser checks the syntax of the SQL statements, and calls the SQL Manager to see if matching SQL is already in the Procedure Cache. If a match is found, the Optimizer is bypassed.

4

Parsing

If no matching SQL is found in the Procedure Cache, the Parser translates the batch into a Query Tree and passes it to the Optimizer.

5

Optimization

The Optimizer takes the Query Tree and produces an optimal Execution Plan based on data sizes, indexes, join techniques and CPU, I/O & memory estimates.

6

Execution

The Query Executor runs the Execution Plan, often interacting heavily with the Access Methods Manager and Transaction Manager.

7

Results

The Query Executor passes results back to ODS.

8

Results

ODS buffers the results and returns them to the client only when the buffer fills or the batch of SQL statements ends.

9

Row Operations

The Access Methods Manager locates and modifies data at the row and index level. It calls the Buffer Manager to retrieve and modify individual pages.

10

Page Retrieval

When a data or index page is required, the Buffer Manager checks the data cache. If the page is already there, disk reads are avoided. If not, it is read from disk.

11

Page Modification

Pages in the cache that are modified will eventually be written back to disk (usually by the Checkpoint process). They may be modified many times in the cache before this happens.

12

Checkpoints

The Checkpoint process minimizes the amount of work SQL Server has to do on startup by periodically flushing dirty pages to disk.

13

Free Pages

The Lazy Writer process maintains the list of buffer pages that are available for immediate re-use. If necessary, it will write dirty pages to disk to make them available.

14

Log Caching

When the Buffer Manager modifies a page, it gets the Log Manager to add the log record(s) to the Log Cache. Once the user commits their changes, the Log Manager places these pages into a queue to be written to disk by the Log Writer. The user process waits for the log writer to flush the log pages to disk.

15

Log Flushes

The Log Writer process writes pages from the log flush queue out to disk. Once the writes are complete, any user processes waiting on that log cache can continue.