#SQLinternal SQL Server Architecture – Network Protocols, Database, Storage & Relation Engine, SQLOS

#SQLServerArchitecture #SQLtutorial #SQLinternal #SQLforbeginners #techtalks #DecodeITeS

Microsoft SQL Server Architecture is very complex internal mechanism with 3 major components.
1. Network Protocols (SNI – SQL Server Network Interface)
2. Database Engine
a. Storage Engine
b. Relation Engine
3. SQLOS
1. Network Protocols (SNI – SQL Server Network Interface): SQL Server Network Interface is a network protocol layer to connect DB instance. Type of Protocal
Shared Memory:
TCP/IP:
Named Pipes
2. Database Engine: DB engine represents the logical architecture of the SQL server which consists of or works with physical architecture and relation engine to execute user requests. Logical architecture is Tables, Indexes, Views, Store Procedures, Functions, Triggers and other logical entities which group data for user representation.

a. Storage Engine: Storage Engine consists of Physical Database architecture along with access and buffer manager.

• Physical Database architecture is a description of how actual data is stored in SQL. This has 2 layers. In Layer 1, Data in SQL server stored in a database that resides on files at the OS files system.
Types of Database files:-
Primary data file
Secondary data files
Transaction Log

In Layer 2, This is the smallest layer which represents Pages and Extents storage of database files at the disk level.
1. Extents
2. Pages
Tyes of pages in SQL Server.
 Data – Data excluding ntext, text and images
 Index − Index Details
 TextImage − ntext, text, and images
 GAM – Global allocation Map saves Information for allocated extents
 SGAM − Shared Global Allocation Map saves Information for allocated mixed extents
 Page Free Space (PFS) – Saves Information about free pages
 Index Allocation Map (IAM) – Details of extents used for table or index.
 Bulk Changed Map – BCM pages used to save extent details modified by bulk operations since last log backup
 Differential Changed Map − DCM pages used to save extent details modified since last differential backup

• Access Methods: Access methods are the type of commands used to access data and desired results.

DDL: Data Definition Language to create, alter and drop DB objects
DML: Data Definition Language to select, insert, modify and delete data

• Buffer Manager: Buffer Manager is one of the critical aspects of Database system performance. Buffer consist of:-
1) Plan Cache
2) Data Pages
3) Buffer Pool
• Transaction Services: Transaction Services works between data storage and a transaction log file(.ldf). It controls transaction details and modified data logging to ensure recovery by dealing with transaction file.

• Lock Manager: Each data access and modification required adequate lock on data. These data lock needs to be upgraded to the next level or released as and when required by the system based on user request. Lock Manager governs this process and helps in maintaining consistency and isolation.
b. Relation Engine: Relation engine is responsible for evaluating user requests SQL Commands and performs execution.
1) Parser – Parse the query or command for syntax and T-SQL
2) Algebrizer – Resolve all object names and bind them
3) Query Optimizer – Generate execution plans on the basis on stats and chose best one for execution
4) Query Execution – Actual query execution. Actual execution plan may change now and same will be stored in the plan cache for future reference
5) Query output to user

3. SQL OS: SQL OS was introduced in SQL 2005. SQL OS is a layer between the SQL Server DB engine and the Windows Operating system.

Why Microsoft feel the need for SQL OS?

We know Windows OS is the layer with end-user and hardware. SQL OS is similar to Windows OS. Microsft feels the SQL engine needs a layer between the SQL engine and OS to deals with Memory Management. scheduling, threading, NUMA in a more controlled way with windows OS.
Primary Functions of SQL OS:
• Thread Scheduler: SQL OS is responsible for scheduling CPU thread for SQL operations
• Synchronization Services: SQL is a multithreaded application. SQL OS governs synchronization between threads
• I/O Manager: I/O is time taking process as it depends on disk speed and type. SQL OS keeps monitoring I/O operations and signal threads when completed
• External API: SQL Server provides a feature of controls and working with CLR (DLLs) and MDAC (Open Data Source Connections). SQL OS is responsible to manage it.
• Thread Management: SQL OS Control Lazy write process to maintain free pages in buffer. This is the primary function for buffer management. SQL OS also responsible for Monitor, Detect and Management of deadlocks.
-Rohit Garg