SQL Server Architecture:
SQL Server is a Relational database Management system, a product of Microsoft. Developed and published in many versions. (Express,Developer,Standard,Enterprise,Datacenter and Mobile)
Azure is cloud edition in SQL Server.
Data storage and architecture:
Data is stored in extents and each extent is divided into 8 pages.
Each page size is 8 KB. Page has its header and data content area. Page header is the area where SQL Server will utilize for CHECKSUM and TORNPAGE detection operation. And the remaining area out of the 8kb size is utilized for data storage purpose.
As page size is 8kb, 8 pages will constitute an extent and its size will be 64KB.
There are many types of Pages:
Data Page: Where Data is stored entered by user of different data types like int, char, varchar(n), float and mnay 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.
bulk operation eg: 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 when ever we request for a data, will it come from page or from Memory ?
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).
Size of VAS will be decided by the above mention two factors.
When ever 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 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 to output system.
So, we can say that every read is happening from Buffer Cache.
Now we should have understand why huge RAM system is having good performance, that is because of huge Buffer cache which inturn 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
---- Hope the above information help
