3rd week Area _sql Server The basic unit of management space

Original: 3rd week area _sql Server The basic unit of management space

Wow, SQL Server Performance Tuning training has entered the 3rd week! At the same time you have a good understanding of the SQL Server kernel operating mechanism. Today I'll talk about District management in SQL Server because this is a very important topic and we'll explore TempDbin the 23rd week. At the highest level, the area is a set of 8 8k-sized pages. So a zone is always a block of 64k (Continuous page). SQL Server Internal execution 2 class zone:

• Mixed zone
• Unified Zone

mixed zone and unified zone

8 pages in a mixed area can belong to different database objects like tables and indexes. This also says that the mixed area can point to different database objects. On the other hand, all 8 pages in the unified zone belong to the same database object . the question now is, why does SQL Server make such a difference? This is basically a matter of history. Let's explain.

Storage in the last century is very, very expensive. The goal is to use storage as efficiently as possible. Because the 1th 8 page of the new table and index is always allocated in the mixed area. This also means that your table or index is always in the 8k block (mixed area) when it is first created. In this way, the small table keeps a small storage footprint. You are using storage as efficiently as possible. When your database object needs to be assigned a 9th page, SQL Server allocates the entire unified zone to that database object. Its size from 8kb to 72kb, to the 17th page its growth from 72kb to 136kb, and then continue to grow by this pattern. Now you will be shaking your head about that fact, but in the last century it was a very important design choice. The picture below shows what a zone looks like (in a very simplified way).

District Management

Now the question is, how does SQL Server manage these zones? Imagine you have a 1TB size database, which will give you a huge number of districts. SQL Server is managed here using 2 special pages, which are also 8kb in size.

• Global Allocation Mapping Table (Gam:global Allocation map Pages)
• Shared Global Allocation Mapping table (sgam:shared global Allocation map Pages)

The unified zone is always managed by the GAM page. SQL Server uses 8000 bytes's GAM page, which gives you 64,000 bits. (8000 * 8). In that huge mask bitmap, each bit represents a uniform area. If the bit is marked 1, the uniform area is empty, and if marked as 0, the unified area is already in use. This also means that you can only have one GAM page in your 4G range of data (64000 * 64/1024/1024). Therefore, every 4G data file will have a GAM page. These are also right for SGAM pages. A Sgam page can only manage 4GB of data, because you only have 64,000 bits available.

When you insert a new record in the table, SQL Server inserts the data by using the Sgam page to find at least one page of free mixed extents. If your table/index is larger than 64kb,sql server, you can insert data by using the GAM page to find the free unified extents directly. It's simple, isn't it?

In the 23rd week, when we explore tempdb, this can cause serious performance problems when we try to create a huge amount of temporary data in tempdb at the same time. We will talk about overcoming this problem by modifying some of the settings of the tempdb data.

Summary

In this week's performance tuning training, we explored district and district management in SQL Server. It should now be understood that there is a good and strong internal SQL Server construct.

If you want to learn more about the area, I recommend the following SQL Server quickies:

• SQL Server Quickie #2: Extents
• SQL Server Quickie #3: Allocation Units
• SQL Server Quickie #4: IAM Pages

In the next training, this knowledge will be a prerequisite for performance tuning and troubleshooting. I hope you enjoy the training today, and when I come back next week, we will learn some of the limitations of the data pages and how we can fight them. Keep your eye on it!

3rd week Area _sql Server The basic unit of management space