Translation SQL Passion Week 3:sql Server Sector management

SQL Passion Week 3:sql Server Sector management

Mixed and unified sectors

Every 8 data pages in SQL Server as a sector. In a mixed sector, it contains 8 pages that can belong to a different database object; On the other hand, 8 pages in a unified sector belong to the same database object.

Why is there such a difference, this is mainly because of a historical legacy. Because storage is very expensive in the last century, people will make full use of storage space as efficiently as possible.

when a table and an index are initially created, they are always created on a mixed sector and grow in 8kb space first. This way, small tables only need to occupy a small amount of storage space. When your table grows to the 9th page, SQL Server assigns you a unified sector. Move the pages of a mixed sector to a uniform sector, and subsequent pages continue to be assigned to the mixed sector and then accumulate to page 17th, then assign a single uniform sector, move the pages in the mixed sector to the second consent sector, and so on. Put to today, we must shake our heads on this practice, But in that storage-expensive period, this design is very, very important.

　　

　　

　　Sector management

　　Now how does SQL Server manage these sectors, assuming you now have a 1TB database, which will contain a large number of sectors. SQL Server uses two special pages (8KB, of course):

• Global Allocation Map Pages (GAM)
• Shared Global Allocation Map Pages (SGAM)

　　GAM to manage the unified sector, on the GAM page, there is 8000bytes, equivalent to 64000bits. (8000 * 8) Each bit represents a unified sector. If a bit is a value, it means that the uniform sector is available, and conversely, that the uniform sector is occupied.

In other words, a GAM page can manage 4GB of data (64000 * 64kb/1024/1024).

Sgam is the same thing.

When we insert a piece of data into the table, SQL Server looks for a mixed sector that has at least one empty page available through Sgam. When the table/index exceeds 64KB, the GAM is used to find a usable uniform sector.

　　

[Translate]sql Passion Week 3:sql Server Sector management