Introduction SQL Server Architecture

 

SQL Server Architecture

The SQL Server is Microsoft's relational database management system. It is a full-featured database design to Oracle Database and MySQL

The major components of SQL Server are

1. Relational Engine
2. Storage Engine
3. SQL OS.

1) Relational Engine

The Relational Engine called as the query processor. It 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 and request data from the processes

2) Storage Engine

Storage Engine handles storage and retrieval of the data onto the storage system
 The database in SQL server there is 2 types of files. That created at the disk level – Data file and Log file. Data file stores the data in data pages. Log files are called as write ahead logs.  It is for storing transactions performed on the database.

Data File:

Data File stores data in the form of Data Page and these data pages are organized in extents.

Extents:

Extents are logical units in the database. They are a combination of 8 data pages. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc. But, Uniform extents dedicate to only one type

Pages:

 As we should know what type of data pages can store in SQL Server.

• Data Page: It holds the data entered by the user.  The format of data type text, ntext, nvarchar (max), varchar (max), varbinary (max), and image and XML data.
• Index: It stores the index entries.
• Text/Image: It stores LOB (Large Object data). Like text, ntext, varchar(max), nvarchar(max),  varbinary(max), image and xml data.
•  GAM & SGAM (Global Allocation Map & Shared Global Allocation Map):  they used for saving information related to the allocation of extents.
• PFS (Page Free Space):  Information related to page allocation and unused space available on pages.
• IAM (Index Allocation Map): Information about extents to 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): This is the information of extents. They have modified since the last BACKUP DATABASE statement as per allocation unit.

Log File:

 It is called write ahead log. It stores modification to the database (DML and DDL).

3) SQL OS

 This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine take care of by SQL OS. It is a configurable operating system with powerful API. Enabling automatic locality and advanced parallelism. 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

• Tweet
• Share
• Share
• Share
• Share

Sign up here with your email