Skip to main content

Architecture of SQL Server

 We have classified the architecture of SQL Server into the following parts for easy understanding −

  • General architecture
  • Memory architecture
  • Data file architecture
  • Log file architecture

General Architecture

Client − Where the request initiated.

Query − SQL query which is high level language.

Logical Units − Keywords, expressions and operators, etc.

N/W Packets − Network related code.

Protocols − In SQL Server we have 4 protocols.

  • Shared memory (for local connections and troubleshooting purpose).

  • Named pipes (for connections which are in LAN connectivity).

  • TCP/IP (for connections which are in WAN connectivity).

  • VIA-Virtual Interface Adapter (requires special hardware to set up by vendor and also deprecated from SQL 2012 version).

Server − Where SQL Services got installed and databases reside.

Relational Engine − This is where real execution will be done. It contains Query parser, Query optimizer and Query executor.

Query Parser (Command Parser) and Compiler (Translator) − This will check syntax of the query and it will convert the query to machine language.

Query Optimizer − It will prepare the execution plan as output by taking query, statistics and Algebrizer tree as input.

Execution Plan − It is like a roadmap, which contains the order of all the steps to be performed as part of the query execution.

Query Executor − This is where the query will be executed step by step with the help of execution plan and also the storage engine will be contacted.

Storage Engine − It is responsible for storage and retrieval of data on the storage system (disk, SAN, etc.,), data manipulation, locking and managing transactions.

SQL OS − This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure.

Checkpoint Process − Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk. Apart from this, it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.

It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each database individually. Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.


Checkpoints in SQL Server

In SQL Server 2012 there are four types of checkpoints −

  • Automatic − This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval − Server Configuration Option.

  • Indirect − This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected, this will override the Recovery Interval specified for the server and avoid automatic checkpoint on such DB.

  • Manual − This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual checkpoint runs for your current database only. You can also specify the Checkpoint_Duration which is optional - this duration specifies the time in which you want your checkpoint to complete.

  • Internal − As a user you can’t control internal checkpoint. Issued on specific operations such as

    • Shutdown initiates a checkpoint operation on all databases except when shutdown is not clean (shutdown with nowait).

    • If the recovery model gets changed from Full\Bulk-logged to Simple.

    • While taking backup of the database.

    • If your DB is in simple recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.

    • Alter database command to add or remove a data\log file also initiates a checkpoint.

    • Checkpoint also takes place when the recovery model of the DB is bulk-logged and a minimally logged operation is performed.

    • DB Snapshot creation.

  • Lazy Writer Process − Lazy writer will push dirty pages to disk for an entirely different reason, because it needs to free up memory in the buffer pool. This happens when SQL server comes under memory pressure. As far as I am aware, this is controlled by an internal process and there is no setting for it.

SQL server constantly monitors memory usage to assess resource contention (or availability); its job is to make sure that there is a certain amount of free space available at all times. As part of this process, when it notices any such resource contention, it triggers Lazy Writer to free up some pages in memory by writing out dirty pages to disk. It employs Least Recently Used (LRU) algorithm to decide which pages are to be flushed to the disk.

If Lazy Writer is always active, it could indicate memory bottleneck.

Memory Architecture

Following are some of the salient features of memory architecture.

  • One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations.

  • Memory in windows can be called with Virtual Address Space, shared by Kernel mode (OS mode) and User mode (Application like SQL Server).

  • SQL Server "User address space" is broken into two regions: MemToLeave and Buffer Pool.

  • Size of MemToLeave (MTL) and Buffer Pool (BPool) is determined by SQL Server during startup.

  • Buffer management is a key component in achieving I/O highly efficiency. The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer pool to reduce database file I/O.

  • The buffer pool is further divided into multiple sections. The most important ones being the buffer cache (also referred to as data cache) and procedure cache. Buffer cache holds the data pages in memory so that frequently accessed data can be retrieved from cache. The alternative would be reading data pages from the disk. Reading data pages from cache optimizes performance by minimizing the number of required I/O operations which are inherently slower than retrieving data from the memory.

  • Procedure cache keeps the stored procedure and query execution plans to minimize the number of times that query plans have to be generated. You can find out information about the size and activity within the procedure cache using DBCC PROCCACHE statement.

Other portions of buffer pool include −

  • System level data structures − Holds SQL Server instance level data about databases and locks.

  • Log cache − Reserved for reading and writing transaction log pages.

  • Connection context − Each connection to the instance has a small area of memory to record the current state of the connection. This information includes stored procedure and user-defined function parameters, cursor positions and more.

  • Stack space − Windows allocates stack space for each thread started by SQL Server.








Comments

Post a Comment

Popular posts from this blog

Select Names from table which have vowels

  Problem Query the list of  CITY  names from  table  which have vowels (i.e.,  a ,  e ,  i ,  o , and  u ) as both their first  and  last characters. Your result cannot contain duplicates. Input Format The  STATION  table is described as follows: Field Type ID NUMBER CITY VARCHAR2(21) STATE VARCHAR2(2) LAT_N NUMBER LONG_W NUMBER STATION where  LAT_N  is the northern latitude and  LONG_W  is the western longitude. MYSQL select distinct city from station where (city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%' ) and ( city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u' )
1 comment
Read more

Write a query that prints a list of employee names for employees in Employee having a salary greater than 2000

  Write a query that prints a list of employee names (i.e.: the  name  attribute) for employees in  Employee  having a salary greater than  2000  per month who have been employees for less than  10  months. Sort your result by ascending  employee_id . Input Format The  Employee  table containing employee data for a company is described as follows: Column Type employee_id Integer name String months Integer salary Integer where  employee_id  is an employee’s ID number,  name  is their name,  months  is the total number of months they’ve been working for the company, and  salary  is the their monthly salary. Sample Input employee_id name months salary 12228 Rose 15 1968 33645 Angela 1 3443 45692 Frank 17 1608 56118 Patrick 7 1345 59725 Lisa 11 2330 74197 Kimberly 16 4372 78454 Bonnie 8 1771 83565 Michael 6 2017 98607 Todd 5 3396 99989 Joe 9 3573 Sample Output Angela Michael Todd Joe Explanat...
1 comment
Read more

SQL SERVER – Fix : Error 1702 CREATE TABLE failed because column in table exceeds the maximum of columns

  Error 1702 CREATE TABLE failed because column in table exceeds the maximum of columns SQL Server 2000 supports table with maximum 1024 columns. This errors happens when we try to create table with 1024 columns or try to add columns to table which exceeds more than 1024. Fix/Solution/WorkAround: Reduce the number of columns in the table to 1,024 or less
Post a Comment
Read more