SQL Server Architecture

SQL Server is constructed from a series of many small components that work together, which fall into 3 categories

1. Query Execution Layer
   This has three sub categories:
• The Parser – checks you’ve followed the T-SQL rules
• The Algebrizer – which lists what you want to achieve and converts it to a series of logical operations
• The Query Optimizer – which considers the different ways your query can be executed. It finds an acceptable plan, which is not necessarily the most efficient.
2. Storage Engine Layer
   This manages how data is stored on disk and in memory. The main sub-categories:
• The Access Methods component – works with how you access data
• The Page Cache – stores cached copies of data pages to minimise data retrieval time.
• The Locking and Transaction Management components – maintain consisteny, integrity of data, with help of database log file.
3. SQL OS Layer
   This provides operating system functionality (ie interfaces) to SQL Server components.
   The most important functions provided by this layer are memory management and scheduling.

The most important resources SQL Server utilizes from the server platform are CPU, memory and I/O.

CPU and I/O

When a SQL Server component needs to execute code, it creates a task which represents the time it’ll take to run. These tasks are scheduled by Windows. SQL Server tasks spend most of their time waiting for something external to happen, usually I/O. When a task needs to wait for a resource, it is placed in a waiting list until the resource is available. When it is told the resource is available, it then waits for a share of CPU time, allocated by SQL OS.
SQL Server keeps records of how long tasks spend waiting and the types of resources they wait for. Query the following:
sys.dm_os_waiting_tasks
sys.dm_os_wait_stats

Memory

The Buffer Pool is the main memory object of the SQL Server, and is divided into 8KB pages – the same size as database data pages. It has three sections:

1. Free Pages – not yet used
2. Stolen Pages – used pages
3. Data Cache – used for caching database data pages. Data modification happens here, then the pages are considered dirty and so the changes are written to the database by a process called CheckPoint.
   The Data Cache uses a least recently used (LRU) algorithm to determine which pages to drop from memory when cache space is needed.

SQL Server memory and Windows memory

The memory manager within SQL OS manages the SQL Server memory. It does this by checking the Windows memory, and calculating a value it thinks appropriate to use. Once this has been exceeded, it answers requests for memory from components by freeing up memory used by other components. Basically, it impacts on component performance.

Physical and Logical I/O

1. Logical I/O – when a requested page is available in the Buffer Cache
2. Physical I/O – when a requested page is not available in the Buffer Cache and must be read from the data page into the Buffer Cache

You can minimise the number of Physical I/O operations by:

• Providing enough memory for the data cache
• Optimising the physical/logical layout (including indexes)
• Optimise queries to request as few I/O operations as possible

You can monitor logical and physical I/O operation counts by:

SET STATISTICS IO ON;

You can monitor the overall physical I/O operations by querying the sys.dm_io_virtual_file_stats system function. The values are cumulative from the last system restart.

Parallelism

The Query Optimiser decides if tasks should be synchronised and monitored (which happens if they’re expensive) and only does so if it’s worth it. Basically, Parallelism involves spreading tasks across CPUs, rather than running them sequentially.
Further reading : SQL Server threads, Affinity Mask, MAXDOP, Lazy Writer,