SQL server architecture

Memory Architecture

T-SQL    -->  Query fired by the end user

TDS        -->  Tabular data structure (Creates packages of T-SQL)

N/W       --> In this layer data will get encapsulated

Protocol --> his layer encapsulate the data and pass it to..

Relational enng --> This pass data to store enginn

Store enng -->  Pass data to SQL OS

This architecture mainly divided into four part

1) Configuration manager
2) Relational engine
3) Storage engine
4) Buffer pool

1) Configuration Manager:

·        This is mainly included Protocol Layer.

·        In SQL server we have 4 type of protocol.

1)     Shared Memory For local connection and troubleshooting purpose

2)     Name Pipes If the machines and server on same network LAN

3)     TCP/IP this will work on WAN that is for RDP from any network. This will overwrite Share memory and name pipes. Default port for this is 1433 we need to change it for security reason.

4)     VIA Virtual Interface Adaptor. Required special hardware to set up by vendor and deprecated from SQL 2012 version. 

2) Relational Engine:

TThis is the main part where all the pre-operation happens on the T-SQL Data.

1)     Query (Command) Parser and compiler (Translator) This will check data about data i.e. metadata.  This will check if you have entered correct syntax or not also the statement is made is logically correct or not. Once the data check happen it will convert your query to machine language. Compiler will check your declaration and conditions. Once this process done Parser will create a Parse Tree.

2)     Query Optimizer The main work of optimizer to generate the best execution plane. This will communicate with storage engine to find if the already any plane is in use. If yes it will use it otherwise it will generate the new execution Plan. It generates the execution plane depend on the query, statics and algebraize tree as input.

3)     Execution Plan It is considered as road map, which contain order of all the steps to be performed as part of the query execution.

4)     Query Executor in this your query will get executed with the help of execution plan and the storage engine gets contacted.

3) Storage engine:

This is responsible for storage and retrieval of data on the storage system, data manipulation, locking and managing transactions.


1. Access method This will check if the data available in buffer manager and if yes retrieve the data. Access method create OLEDB row set and send it to Query executor.


2. Buffer manager check if the previous data is available or not. If not available, then it will check buffer pool. If the data available at buffer pool it will retrieved. If the data is not available in buffer pool it will check and retrieve data from .mdf file.


3. Buffer pool if query like insert / update/ delete fired then first buffer pool will get pages. After that whatever transaction happen the changes are being made in T-Log file that called as WAL (Walk Ahead Logging). Between this procedure the pages that available in buffer manager are called as Dirty pages. At check point dirty pages flush in .mdf . When there is pressure on the SQL memory at that time Lazy Writer flushes data to mdf files and pages removed from the buffer pool. Only Lazy writer will remove the pager from buffer pool.

*In log file recovery done after check point.

Checkpoint

** In SQL server 2012 there are 4 types of check points.


1. Automatic This will run in background as a process to make sure server can recover in the time limit defined by the recovery interval.


2. Indirect This is new in sql server 2012 and this run as process to meet a user specific target recovery time. Once the Target_Recovery_Time for a given database is selected it will overrides the recovery interval specified for the server and avoid automatic checkpoint on such DB. Example as ALTER DATABASE AdventureWorks2012 SET TARGET_RECOVERY_TIME = 60 SECONDS;


3. Manual you can run this command as like any other T-SQL command. You can also specify the checkpoint_duration which is optional. This duration specifies the time in which you want your check point to complete. CHECKPOINT [ checkpoint_duration ]


4. Internal When shutdown initiated. If the recovery mode change from full\bulk-logged to simple. While taking backup of data.Alter database command to add or remove a data\log file also initiated a checkpoint.

Lazy Writer

Ø  To free up memory in buffer pool Lazy writer will push dirty pages to disk. It uses Employs Least Recently Used (LRU) algorithm to decide which pages are to be flushed to the disk.