Saturday, April 11, 2020

SQL Server Architecture

Hello Guys,

To learn any technology, we should clear about basics of technology, other knowledge we can achieve with experience and by handling different issues. Same principle applies to SQL server if you know architecture of SQL server, whether you are SQL developer or DBA you can take correct decision quickly when you are handling any issue.
In this post, I have tried to explain SQL server architecture in detail which will help you to understand SQL server more. So let’s begin...


 

Above is the basic diagram of SQL architecture. We can divide SQL engine in 2 parts – Relational engine which perform SQL OS operations and storage engine which process data at actual storage level. 

1)      Relational Engine:
Relational engine performs SQL OS operations means, this part of SQL engine where actual SQL server transactions received and operated.

Relational engine contains below blocks or operators.

a)      Network Protocol layer:
Here actual client – server communication happens at default port 1433. For this Microsoft has developed API called SNI (SQL server Network Interface). Communication happens in the form of TDS (Tabular Data String developed by Sybase and later adopted by MS), means whatever query client execute it gets converted in TDS and transferred to SQL. Same for SQL server whatever data derived converts in TDS and transfers to client.

Now SQL server have 4 types of network architecture/protocols:

                   I. Shared Memory: This the default protocol used to connect to a client residing locally (i.e. on the same machine).
                  II. TCP/IP: This protocol helps us to connect to the SQL server over the network via an IP address and a port number. The default TCP port used by SQL Server is 1433.
                 III. Named Pipes: Originally developed for LAN, this protocol is similar to TCP/IP, but is efficient for slower networks like WAN. It uses port number 445.
                  IV. VIA: Virtual Interface Adapter, is a protocol used for high performance communication between two connected systems, with the help of specialized hardware on both ends.

b)      Command Parser:
Network layer coverts query received as TDS into language event and pass it to command parser. Command parser performs syntax and semantic checks, if query is incorrect gives respective error back to the client from error library which contains more than 50k defined errors. If query is correct command parser passes it to Optimizer in the form of query tree.

c)       Optimizer:
Optimizer is the most important part of relational engine because it defines performance of query execution. When optimizer receives query tree it checks all statistics and all execution plan available from plan cache. If query is new it creates new execution plan and pass it to query executer for execution.

d)      Executer:
Executer executes query on storage engine with best execution plan provided by optimizer and transfers derived data back to client.

2)      Storage Engine:
Storage engine operates actual physical data on disk/storage and memory.
Storage engine also contains below operators/units.

a)      Access Methods:
As we know SQL server works as write log ahead, transactions which includes Update/Insert/Delete are written to log file via log manager before writing it to data file. Where in Select, data is derived direct from data file via buffer manager. So Access method checks whether transaction contains SELECT or Update/Insert/Delete and accordingly route it to buffer manager or log manager.

b)      Buffer manager:
SQL server does not read or write data directly in disk, it calls pages’ in memory and performs operation there this is called as hard parsing. Sometimes data may present in data cache already due to recent transaction so data directly read from memory this is called Soft parsing.
SQL server always wants to keep all data in Memory to access easily, but it’s not possible due to limited RAM so periodically checkpoint runs which moves dirty pages again to disk on LRU basis (Least Recently Used pages).
Now there are two different functions performs similar activity – Checkpoint and Lazy writer both moves pages from memory to disk but there is below difference
Checkpoint is executed periodically with purpose to shorten database recovery after crash down.
Where lazy writer executed only when there is memory pressure with purpose to lower down pressure on memory.
Buffer/cache also have some part reserved for plan cache which optimizer use to refer recently used execution plans.
So buffer manager operates all these activities in memory and manages transaction to use memory efficiently.

c)       Log manager:
This manages logs and locks when every transaction written to log file.

d)      Log file:
Log file keeps logging every Update/Insert/Delete transaction and it gets truncated after checkpoint or log file space usage reaches to 70% if database in simple recovery mode, where after log backup in full recovery mode.
Each transaction is written sequentially on log file as record and sequence is maintained with LSN (Log Sequence Number).
Log file is logically divided into small VLFs (Virtual log files) We can’t predict size of VLFs but number of VLFs can predict when log file created according to size of log file as below.

Size of log File
Number of VLFs
Less than 1MB
2
1 MB to 64 MB
4
64 MB to 1 GB
8
More than 1 GB
16

Later number VLFs can varied according to size of auto growth set for log file and transactions. VLFs is topic could be studied in more details but it’s not part of our post for now so just described at basic.

e)      Data file:
Data file is where actual data stored on the disk, where basic unit where data stored is PAGE of 8KB size. 8 Pages are logically grouped together and called as extent which is size of 8x8KB = 64 KB.
Now there are two types of extent mixed extent and uniform extent.
Pages in the mixed extent are shared by two or more different objects (tables or indexes) where pages in uniform extent are used only for one object.
For better performance uniform extent is always better. Till SQL version 2014 it could be controlled with trace flag 1118. Enabling trace flag starts using uniform extent. From SQL server 2016 onward uniform extent is enabled by default.

Below is the structure of page 


    Header:
Out of 8KB in page initial 96 bytes are reserved for system information of page like page number, page type, allocation ID etc.
    Row offset:
Row offset’s each unit is of 2 bytes and added at the end of pages. One entry of row offset mapped to one row on page and each row offset gives information how far 1st byte of each row is from start of page.
Data
Other part o page contains respective page data. 

             Below are the types of pages which we will discuss in another post.

  • Data page
  • Index page
  • LOB (Large Object pages) for text or image
  • GAM (Global Allocation Map)
  • SGAM (Shared Global Allocation Map)
  • PFS (Page free space)
  • IAM (Index Allocation Map)
  • BCM (Bulk Change Map)
  •  DCM (Differential Change Map)
 
 

No comments:

Post a Comment