I am writing this Article to understand the architecture of
MS SQL Server .we will start with understanding the architecture with the help
of a diagram.
So major components of SQL Server are.
1)
Relational Engine
2)
Storage Engine
3)
SQL OS
Now we will discuss and understand each of them.
1)
Relational Engine: - It is also called the query
processor. It includes the components of SQL Server that determine exactly what
your query 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.
Different Tasks of Relational Engine
a.
Query Processing
b.
Memory Management
c.
Thread and task Management
d.
Buffer Management.
e.
Distributed Query Management.
2)
Storage Engine: - Storage Engine is responsible
for storage and retrieval of the data on to the storage system (Disk, SAN
etc.). But to understand more lets focus on the following diagram.
|
Sales
|
|
Data Files
|
|
Log Files
|
When we talk
about any database in sql server there are 2 types of files which are created
at the disk level, Data file and log file. Data file physically store the data
in data pages and log file which is used to store transaction performed on the
database also known as write ahead log. Now we will discuss first about data
file and then log file.
Data File:- let’s see first the
structure of the data file and then discuss details about them.
It stores data
in the form of Data page (8KB) and these data pages are logically organized in
extents.
Extents : They
Are logical unit in the database and is the combination of 8 data pages I.e. 64
KB form an extent .Now extents can be of two types Mixed and Uniform. Mixed
extents hold different type of pages like index, System, Object data. Uniform
extent is dedicated to one type .
Pages: We
should know that what type of data pages can store in SQL Server. So here are
some of them.
·
Data Page :- It hold the data entered by the
user but not the data which of type text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data,
·
Index :-
Stores the index entries.
·
Text/Image :-
It stores LOB ( Large Object data) like text , ntext, image, nvarchar(max),
varchar(max), varbinary(max),
and xml data
·
GAM & SGAM(Global
Allocation Map & Shared Global Allocation Map):- They are used to store
information about whether extents are allocated or not.
·
PFS (Page
Free Space) :- Information about page allocation and free space available on
pages.
·
IAM (Index
allocation Map):- Information about extents used by a table or index per
allocation unit
·
BCM (Bulk
Changed Map):- Keeps information about the extents changed in a Bulk Operation.
·
DCM (Differential
Change Map):- Information about extents that have changed since the last BACKUP
DATABASE statement per allocation unit.
Log File:- it
also known as write ahead log. Which stores modification to the database (DML
and DDL). First let’s see the internal Structure
of the file.
•
Sufficient information is logged to be able to:
§
Roll back transactions if requested
§
Recover the database in case of failure
•
Write Ahead Logging is used to create log
entries
§
Transaction logs are written in chronological
order in a circular way
§
Truncation policy for logs is based on the
recovery model
Default
behavior of the log file is when checkpoint process occur also known as lazy
writer process it truncate Virtual log of only committed transactions since
last checkpoint.
SQL OS: - Which lies between Host
machine (Windows OS) and SQL Server. Entire thinks that you are preforming on
database engine is taken care by SQLOS. It is highly configurable operating system
with powerful API (application programming interface), enabling automatic
locality and advanced parallelism. SQLOS provides operating system services
such as memory management deals with buffer pool, log buffer, deadlock
detection using the blocking and locking structure, exception handling, hosting
for external components such as Common Language Runtime, CLR and other
services.
