In this article I will describe SQL Server Architecture. In the article, I will proceed through the following image that I received from wikipedia. And I will briefly discuss what these layers do.
As you can see, SQL Server Architecture consists of 3 main layers.
Layer 1: SQL Server Network Interface (SNI)
The components in the External Protocols section that you see in the top are called SQL Server Network Interface (SNI).
Layer 2: Database Engine
Database Engine includes Storage Engine, Query Processor and other components.
Layer 3: SQLOS API
After this part of the article we will detail these layers.
Layer 1: SQL Server Network Interface (SNI)
SNI is the protocol layer that provides the connection between client and server. SQL Server supports the following 4 protocols.
You can configure these protocols on SQL Server Configuration Manager. As you can see below, there are different protocol configurations for each instance. In the picture below, the Shared Memory and TCP / IP protocol is active.
For other SQL Server Configuration Manager settings, I recommend that you read the article “SQL Server Configuration Manager Settings“.
Shared Memory
The protocol used when the Client and Server are on the same machine. You cannot configure. You can only disable it. If you are using the server where the sql server is installed as a client, by default the sql server attempts to connect to the instance using this protocol.
Named Pipes
This protocol is used when Client and Server are on the same LAN. It uses the TCP 445 port. It can be used in environments where there is no TCP / IP protocol.
TCP / IP
The most widely used and accepted protocol all over the world. When a client connecting to a sql server, it uses an IP or server name and port (eg 10.56.43.23,1433). The default tcp port of SQL Server is 1433.
Virtual Interface Adapter (VIA)
You cannot see this protocol in SQL Server Configuration Manager. Although it is a high performance protocol, it is not preferred because it requires additional hardware in client and server.
Layer 2: Database Engine
We will examine the Database Engine as Relational Engine(Query Processor) and Storage Engine.
Relational Engine(Query Processor)
Relational Engine is responsible for running queries in the most optimized way. It consists of the following components.
SQL Manager: Manages the procedure cache. It responsible for the execution of Stored Procedures and automatic parameterization of ad hoc queries. When a query is run, SQL Manager first searches for the query in the cache. If the query is not in the cache, it determines that the query must be compiled. Then the Parse process starts.
Parser: Converts the query to be processed more easily by a computer. It also checks for typos.
Optimizer: There are many ways to run the query to transfer data to the end-user. For example, index seek + key lookup or Clustered Index Scan can produce a query result. The optimizer prepares the execution plan to run the query in the most optimized way by using the statistics. You may be interested in the following articles.
“Statistic Concept and Performance Effect on SQL Server“,
“Index Concept and Performance Effect on SQL Server”
Query Executor: This is the part where the query is executed step by step using the execution plan created by the optimizer.
It also contacts with the Storage Engine.
Storage Engine
As the name implies, it is responsible for storing data. You can find its subcomponents below.
Buffer Manager: The process of transferring data that is not in memory from disk to memory and rewriting the changed data from memory to disk is managed here.
Transaction Services:
• Ensures that transactions meet ACID rules. You may want to read the article “SQL Server ACID Rules“.
• Write Ahead Logging (WAL) is managed here. I recommend that you read the article “What is Database Checkpoint” for WAL.
• Concurrency controls are managed here. I recommend you to read “Optimistic and Pessimistic Concurrency Control“.
Lock Manager: All lock types are managed here. In the article “SQL Server Lock Types” you will find the lock types and their descriptions.
File Manager: Responsible for allocating space for data in the database.
You can find information about storage concepts in the article “SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM)“.
Layer 3: SQLOS API
SQLOS is the layer that manages all operating system resources specific to SQL Server. Announced with SQL Server 2005. Prior to 2005, operating system resources were managed by different parts of the SQL Server Engine. You can see the components managed by SQLOS in the image I shared at the beginning of the article.
