Skip to main content
Our Tech Ideas

SQL Server Architecture

SQL Server Architecture

Introduction

If you’re curious about how SQL Server works behind the scenes or you’re new to the world of databases, you’ve come to the right place. In this article, we’ll provide you with a simple and easy-to-understand introduction to SQL Server Architecture. We’ll explore the key components that make up its structure and explain how they work together to manage and store data efficiently. Whether you’re a beginner or looking to expand your knowledge, we hope this article will shed some light on the inner workings of SQL Server. So let’s dive in and discover the fascinating world of 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.

  1. Relational Engine
  2. Storage Engine

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

Storage Engine 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.

SQLOS is a core to SQL Server architecture, used for scheduling, I/O completion, Memory Management, and resource management. It is a thin layer between windows OS and SQL server. SQLOS is responsible for managing operating system resources that are specific to SQL Server. Ref. https://learn.microsoft.com/

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 TOS 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.

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.

Query Executor

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

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.

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.

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

SQL Server Architecture Diagram

Steps in executing a query

  1. Server Network Interface (SNI) of the user establishes the connection between client and server using TCP/IP protocol, sends a query in TDS packets.
  2. 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.
  3. 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.
  4. The access method requests the buffer manager to provide the data.
  5. 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.
  6. 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.

  1. Shared memory
  2. Named pipes
  3. TCP/IP
  4. 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

Below topics further included in this series