SQL Server Architecture

SQL Server Architecture

SQL Server follows a client-server architecture. Whenever the user performs any action on the client machine, it converts in the form of a query. This query moves from client to server in the form of network packets using protocols for connection and communication between the source and destination servers.

Two Major components

Relational Engine
Storage Engine

Relational Engine (Query Processor) prepares the execution plan and handover to the Storage Engine.

Storage Engine The work of the Storage Engine is to store data in a storage system like Disk or SAN (Storage Area Network) and retrieve the data when needed. It is a central repository, responsible for the execution of query using execution plan, the response sent to the user.

The buffer pool is another important component that contains plan cache and data cache which is used for query execution.

SQL OS is a core to SQL Server architecture, used for scheduling, I/O completion, Memory Management, and resource management. It is a thin layer between any OS and SQL server.

Components of SQL Server

SQL Server Network Interface (SNI)

SNI is a Protocol layer that establishes the network connection between the client and the server. It uses TCP/IP protocol to send queries in the form of Tabular Data Stream (TDS) packets.

Command Parser

Command Parser first checks for syntax errors, then it generates a query plan (or) find an existing plan query plan contains detailed steps on how a query is going to execute. Command parser checks whether a plan already exists in the plan cache of the buffer pool. If finds plan passes to query executor for execution. If it does not find then the query passes to the optimizer.

The principal job of CMD Parser is to check the query for Syntactic and Semantic error. Finally, it generates a Query Tree.

Optimizer

Optimizer prepares query plans for one query in that SQL server select best plan based on response time, the query plan passes to query executor for execution.

Optimizer’s role is to find the cheapest, not the best, cost-effective execution plan.

Query Executor

Query executor requires data to read the query plan it passes to access methods of the storage engine.

Query executor calls Access Method. It provides an execution plan for data fetching logic required for execution.

Once data is received from Storage Engine, the result gets published to the Protocol layer. Finally, data is sent to the end user.

Access Methods

Access methods require data to complete the query it asks buffer managers to provide a data page. Once it receives the required data, the query results pass back to the relational engine and there to the user.

This Component Determine whether the query is Select or Non-Select Statement. Invokes Buffer and Transfer Manager accordingly.

Buffer Manager

Buffer manager checks in the data cache of the buffer pool to see if it has the page already in cache memory. If the page exists, it passes results to Access methods. If not exists it pulls required pages from the MDF data file, puts them in the data cache, and passes them back to Access methods.

Buffer manager manages core functions for Plan Cache, Data Parsing & Dirty Page.

Buffer Pool

Plan Cache

Part of SQL servers buffer pool used to store previously executed execution plans in case they are needed later.

Data Cache

Data cache is the largest part of the buffer pool. Every data page that is read from disk is written a copy here before use. Under memory pressure, these pages are flushed from the cache using LRU (Least recently used) policy.

SQL Server Architecture Diagram

Steps in executing a query

Server Network Interface (SNI) of the user establishes the connection between client and server using TCP/IP protocol, sends a query in TDS packets.
Query at command parser checks syntax errors then checks plan in plan cache of the buffer pool. If the plan not exists, pass the query to the optimizer.
The optimizer generates the best plan and passes it to the query executor, it reads the plan and passes it to the access method of the storage engine through OLEDB.
The access method requests the buffer manager to provide the data.
Buffer manager checks in the data cache of the buffer pool for an existing page. If the page not exists it pulls the required pages from the data (MDF) file, puts them in the data cache, and passes them to the access method.
Finally, the Access method passes the results back to the relational engine, from there it sent back to the user who executed the query.

Protocols available in SQL Server

SQL Server Network Interface (SNI) is a protocol layer that establishes the network connection between the client and the server. SQL Server supports 4 protocols.

Shared memory
Named pipes
TCP/IP
VIA

Shared Memory: It is the default protocol used to connect client and SQL Server on the same machine

Named Pipes: Client and server will connect within a LAN. It has certain limitations.

TCP/IP: TCP/IP is the most used protocol for SQL Server client establishes a connection with SQL server using an IP Address and a port number 1433 we can access the databases using the internet hence there are no boundaries for this protocol.

VIA (Virtual Interface Adapter): VIA is a wireless internet protocol for connecting clients and servers within a certain range.

To establish a secure SQL connection we need a port number along with the protocol. The default port number for TCP/IP protocol is 1433 we can change the port number from configuration manager — SQL server network configures — protocols we can change.

#Ref. Ram Mohan & Rama Krishna notes

Summary:

Three Type of Client Server Architecture exist: 1) Shared Memory 2) TCP/IP 3)Named Pipes
TDS, developed by Sybase and now owned by Microsoft, is a packet which is encapsulated in Network packets for data transfer from the client machine to the server machine.
Relational Engine contains three major components:

CMD Parser: This is responsible for Syntactic and Semantic error & finally generate a Query Tree.

Optimizer: Optimizer role is to find the cheapest, not the best, cost-effective execution plan.

Query Executor: Query executer calls Access Method and provides execution plan for data fetching logic required for execution.
Three type of files exists

Primary file .mdf
Secondary file .ndf
Log files .ldf

Storage Engine: Has following important components

Access Method: This Component Determine whether the query is Select or Non-Select Statement. Invokes Buffer and Transfer Manager accordingly.

Buffer Manager: Buffer manager manages core functions for Plan Cache, Data Parsing & Dirty Page.

Transaction Manager: It manager Non-Select Transaction with help of Log and Lock Managers. Also, facilitates important implementation of Write Ahead logging and Lazy writers.

ACID

Atomicity Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely, or fails completely. No partial commits.
Consistency (Correctness) Consistency ensures that a transaction method can only bring the database from one valid state to another, maintaining database invariants. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct.
Isolation Transactions are often executed concurrently (e.g., multiple transactions reading and writing to a table at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.
Durability Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory