1. The command parser evaluates the query and generates a hash to see if the buffer pool has an execution plan already stored.

2. The buffer pool maintains the execution plan cache (via a hash to identify each plan cached), and a the data cache.

3.Creating execution plans can be time consuming and resource intensive. Which is why SQL Server checks the cache first for a plan.

4.If no plan is found in the cache, the command parser generates a query tree based on the SQL.

5.The query tree is then passed to the Query Optimizer where an execution plan is created.The query optimizer evaluates multiple ways to execute the query when creating the plan. The Query Optimizer does not always pick the BEST plan but a good plan in a reasonable amount of time

6.The Query Optimizer has its own Rules Processor to examine the query. Query Optimizer go thru multiple stages for generating a good acceptable plan

7.Stage 1 (Pre Optimization Stage): possibility of TRIVIAL PLAN

8.Stage 2 (Simple to Full optimization stages) will generate and evaluate many plans and will choose plan with LOWEST COST

9.Execution plan is stored in Plan cache for reuse and passed to Query Executor

10.Query Executor checks PAGE availability in DATA CACHE. If not available PAGE is retrieved from Disk System pages (like GAM, SGAM, IAM) and Index pages etc are used to manage, identify pages quicker)

11.Result set is formatted as relational table (as XML if FOR XML is specified) and encapsulated in TDS packet

12.Server side SNI encapsulates the TDS (Tabular Data Stream) packet inside a standard communication protocol, such as TCP/IP or named pipes and move it to the client

13.Client SNI receives the TDS packet (Default port 1433), unpack the result set and show it in Grid

Here is some database level factors influence query performance:
1. Usable Index
2. Sargable arguments
3. Statistics
4. Database configurations
5. Query Hints

NOTES:
* SQL Servers uses write-ahead logging to maintain durability
* Data modifications are written to the log first, then to memory only. This is done for performance reasons and enables you to recover changes form the transaction log if necessary
* When data is read from disk to memory (cache), it is considered to be a “clean” page
* A dirty page is a page in memory that has been updated but not persisted back to disk
* You can use the following to see the number of dirty pages per database:

* The dirty pages will be written periodically to disk when the “free buffer” list is low or a checkpoint occurs
* The Lazy Writer is a thread that periodically checks the size of the Free Buffer List and when it is low, ages-out the oldest pages, which causes them to be written back to disk
* The Checkpoint process ensures that all “committed” transactions written to the transaction log are written to disk (hence no dirty pages in memory)

The Lazy Writer process

* Periodically checks the size of the Free Buffer List and, when it is low of memory, will do the following:
*# Scans the whole data cache and “ages-out” any pages that have not been used in a while
*# Write to disk and dirty pages that haven’t been used in a while
*Monitors free memory on the server and will do the following:
*# Releases memory from the Free Buffer List in low memory situations
*# Adds memory to the Free Buffer List when SQL Server is busy to meet demand if there is free memory available and the “Max Server Memory” threshold has not been met.

The Checkpoint Process

A checkpoint is a point in time created by the checkpoint process at which SQL Server can be sure that any committed transactions have had all their changes written to disk. This checkpoint then becomes the marker from which database recovery can start.

The checkpoint process ensures that any dirty pages associated with a committed transaction will be flushed to disk. It can also flush uncommitted dirty pages to disk to make efficient use of writes but unlike the lazy writer, a checkpoint does not remove the page from cache; it ensures the dirty page is written to disk and then marks the cached paged as clean in the page header.

By default, on a busy server, SQL Server will issue a checkpoint roughly every minute, which is marked in the transaction log. If the SQL Server instance or the database is restarted, then the recovery process reading the log knows that it doesn’t need to do anything with log records prior to the checkpoint.

Views – 5375