How SQL Server Architecture work with Relational Engine,Storage Engine and SQL OS

MSSQL ADMINISTRATION IN HINDI | MSSQL DBA CLASS IN HINDI | LEARN MSSQL IN HINDI Relational Engine:
 It is also called as the “Query Processor”, Relational Engine includes the components of SQL
Server that determine what your query exactly needs to do and the best way to do it.
 It manages the execution of queries as it requests data from the storage engine and processes the
results returned.
 This is where real execution will be done. It contains Query parser, Query optimizer and Query
executor.
Different tasks of Relational Engine:
 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 − 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:
 Storage Engine is responsible for storage and retrieval of the data on to storage system (Disk,
SAN etc.).
Buffer Manager:
 The buffer manager manages the functions for reading data or index pages from the database disk
files into the buffer cache and writing modified pages back to disk.
 A buffer is an 8-KB page in memory, the same size as a data or index page. Thus, the buffer cache
is divided into 8-KB pages.
Access Methods:
 SQL doesn’t directly retrieve from disk, it makes requests to buffer manager which serves up the
page in cache before rendering out.
 Each component maintains its respective on-disk data structures – rows of data or B-Tree indexes.
 The allocation operations code manages a collection of pages for each database and keeps track
of which pages in the db have already been used, for what purpose and how much space is
available on each page.
Transaction Services:
 Provides support for Atomicity, Consistency, Isolation and Durability .
 Write-ahead logging ensures that the record of each transaction’s changes is captured on disk in
the transaction log before a transaction is acknowledged as committed.
 SQL Server supports two concurrency models
 Optimistic Concurrency – provides consistent data by keeping older versions of rows
with committed values in version store. Readers do not block writers and writers do not
block readers. Writers do block writers
 Pessimistic Concurrency – guarantees correctness and consistency by locking data so it
cannot be changed
Locking Manager:
 To manage all types locks.
 Acquires and releases various types of locks
 Share locks – reading
 Exclusive locks – writing
 Intent locks – taken at a higher granularity to signal a potential "plan" to perform
some operation
 Extent locks – for space allocation
 Manages compatibility between the lock types, resolves deadlocks and escalates locks.
 Controls table, page, and row locks as well as system data locks.
File Manager:
 File manager to manage the file allocation space and file growth operations.
 SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.
 Pre-size data and log files well ahead of time and plan for the growth.
 Do not rely on AUTOGROW, instead manage the growth of these files manually. You may leave
AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data
files.
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. MSSQL SERVER ADMINISTRATION IN HINDI