SQL Server is a product of Microsoft and it is a relational
database Management system. It is developed and published in many versions such
as Express, Developer, Standard, Enterprise, Azure and Mobile.
Data storage and architecture
The data internally stored in extents and each extent is divided into 8
pages, each page size is 8 KB in SQL Server.
Page has its header and data content area. Page header is the area where
SQL Server will utilize for CHECKSUM and TORNPAGE detection operation. The
remaining area out of 8KB size is utilized for data storage purpose.
A page size is 8 KB, 8 pages will constitute an extent and its size will
be 64 KB.
Types of Pages
There are certain types of pages and they are:Data Page
Where Data is stored entered by user of different data types like int,
char, varchar(n), float and many other.
Index Page
Index page where Index data is stored.Text Page
Where Text pages are stored whose data type is BLOB. (Binary Large
Object data type).
Image Page
Image pages where BLOB data types are stored.
GAM and SGAM
Global Allocation Map and Secondary Global Allocation Map which will
have information related to extents. GAM will search for the extent which is
free for data to be written/read and next page will be search by SGAM and it
goes on in the same way.
PFS
Page Free Space will track the information related to page free space
and page allocations.
IAM
Index Allocation Map will track the extents/pages which are used by the
table
DCM
Differential Allocation Map will track the data changes in the extents
which are modified after the full backup of the respective database.
BCM
Bulk Changed Map will track the data in the extents which are change
after any bulk operation such as Select into, BCP, Bulkinsert etc.
The above all will help SQL Server to track the data when it is read from the database means reading the data from Pages. Now the question is whenever we request for a data, will it come from page or from Memory?
The above all will help SQL Server to track the data when it is read from the database means reading the data from Pages. Now the question is whenever we request for a data, will it come from page or from Memory?
The answer
is Memory
VAS
(Virtual Address Space) will be created depending upon the relation with
the RAM (Random Access Memory) and Type of OS (64-bit or 32-bit), and its size
of will be decided by RAM & OS.
Whenever data read/write is requested VAS will come into picture. SQL Server will search the data in VAS (which is mostly occupied by Buffer Cache) and will return the output to the system. If data doesn't found in cache then DISK I/O operation will happen and SQL Server will go and read from extent/page level.
After reading from extent/page level, it will not directly return the data, YES it will load the data into Buffer Cache (space in Virtual address space) and then reload the data from there produce the result.
So, each read is happens from Buffer Cache.
Now we can understand that why huge RAM system is having good performance because of huge Buffer cache which doesn't make the system to go for more DISK I/O.
There is limitation to the size of VAS.
VAS limit for 32 bit system is 4 GB
VAS limit for 64 bit system is 8 TB
Whenever data read/write is requested VAS will come into picture. SQL Server will search the data in VAS (which is mostly occupied by Buffer Cache) and will return the output to the system. If data doesn't found in cache then DISK I/O operation will happen and SQL Server will go and read from extent/page level.
After reading from extent/page level, it will not directly return the data, YES it will load the data into Buffer Cache (space in Virtual address space) and then reload the data from there produce the result.
So, each read is happens from Buffer Cache.
Now we can understand that why huge RAM system is having good performance because of huge Buffer cache which doesn't make the system to go for more DISK I/O.
There is limitation to the size of VAS.
VAS limit for 32 bit system is 4 GB
VAS limit for 64 bit system is 8 TB
No comments:
Post a Comment