Pages

Wednesday 12 September 2012

SQL Server: Understanding GAM and SGAM Pages

We know that SQL server stores the data in 8 KB pages. An extent is made up of 8 physically contiguous pages.When we create a database, the data files will be logically divided into the pages and extents.Later, when user objects are created, the pages are allocated to them to store the data.GAM (Global Allocation Map) and SGAM (Shared Global Allocation Map) pages are used to track the space allocation in SQL Server. In this post, let us discuss about space allocation in SQL server and how GAM and SGAM helps in the space allocation.

In SQL server there are two types of extents: 

Uniform Extent: These are the extents owned by single user objects. All 8 pages of these extents can be used by a single object, the owning object.

Mixed Extent:These are the extents owned by multiple user objects. There is a  possibility of each page in this  extent, that might be allocated to 8 different user objects. Each of the eight pages in the extent can be owned by  different objects.

To make space allocation more optimize, SQL server will not allocate pages from uniform extent to a table or index if its size is less than 8 pages. Let us try a sample .

USE Mydb
GO
CREATE TABLE TestSpaceAllocation(
  
Name CHAR(8000)
)

GOINSERT INTO TestSpaceAllocation VALUES('John')
GO 26 --Insert 26 records 
DBCC IND('MyDb','TestSpaceAllocation',1)

For the usage of DBCC IND refer the earlier post

The output will looks like as given below:

























From the output, it is clear that, the first 8 pages are not from single extent . There is a gap between page number 187 and 211, remaining 8 pages are physically contiguous (8 page number are in sequential order). While looking into the fragmentation level in your environment, you might have noticed small tables with higher level of fragmentation. This higher fragmentation will not reduce even if you rebuild the index.The reason behind this is due to the allocation of first eight pages from the mixed extent.Refer the post Measuring Fragmentation to learn about Fragmentation 

SQL sever allocates pages for new table or indexes from mixed extents.Once the tables grow beyond 8 pages, SQL server has to allocate page from uniform extent. When a table or index need more space to accommodate the new or modified data, SQL server has to allocate page for the table or index. If the size of the table or index is less than 8 pages, SQL server has to locate a page from mixed extent to allocate. If the size is more than 8 pages, SQL server has to locate the page from uniform extent. SQL server uses two types of pages to optimize this allocation process.

GAM(Global Allocation Map): GAM pages records what extents have been allocated for any use. GAM has bit for every extent. If the bit is 1, the corresponding extent is free, if the bit is 0, the corresponding extent is in use as uniform or mixed extent.A GAM page can hold information of around 64000 extents. That is, a GAM page can hold information of (64000X8X8)/1024 = 4000 MB approximately. In short,  a data file of size 7 GB will have two GAM pages.

SGAM (Shares Global Allocation Map): SGAM pages record what extents are currently being used as mixed extent and also have at least one unused page. SGAM has bit for every extent. If the bit is 1, the corresponding extent is used as a mixed extent and has at least one page free to allocate. If the bit is 0, the extent is either not used as a mixed extent or it is mixed extent and with all its pages being used. A SGAM page can hold information of 64000 extents. That is, a SGAM page can hold information of (64000X8X8)/1024 = 4000 MB. In short, a data file of size 7 GB will have two SGAM page.










GAM and SGAM pages helps the database engine in extent management. To allocate an extent, the database engine searches the GAM page for a bit 1 and set the bit to 0. If that extent is allocating as mixed extent, it sets  the corresponding extent's bit in SGAM page to 1. If that extent is allocating as uniform extent, there is no need to change the corresponding SGAM bit. To find a mixed extent with free pages, the database engine searches the SGAM page for a bit 1. If there is no free extent, the data file is full. To deallocate an extent, the database engine sets the corresponding GAM bit set to 1 and SGAM bit to 0.

In any data file, the third page(page no 2) is GAM and fourth page (page no 3) is  SGAM page. The first page (page no 0) is file header and second page (page no 1) is PFS (Page Free Space) page.  We can see the GAM and SGAM pages using DBCC page command. Refer earlier post for the usage of DBCC page 

DBCC TRACEON(3604)
GO
DBCC page('adventureworks2008',1,2,3)

The last part of the out put is :













First line says that, all extents between the extent starts at  page no 0 and  22400 are allocated .That means page numbers from 0 to 22407 are part of the allocated extents.Second line says that, all extents between the extent start at  page number 22408 and  2416 are not allocated .That means page number from  22408  to 22423 are part of extents which are not allocated. Third line says that, extent start at page no 22424 is allocated. That means page number from   22424   to  22431 are part of the allocated extent. Let us do DBCC page for one allocated page(22400) and one not allocated page (22408)

DBCC page('adventureworks2008',1,22400,1)













After the page header, in the allocation status section, it has mentioned the GAM page, to which the page belongs  to and the status of the extent as  ALLOCATED . For the  page 22408  it will be same GAM page but status will be NOT ALLOCATED.

Let us see the SGAM page
DBCC page('adventureworks2008',1,3,3)

The last part of the output will look like as given below.









It says that extents between extent starts at page numbers 0 and  11752  are not allocated, which means these extents are not allocated at all or are uniform extents or mixed extents with no free pages. The second lines says, the extent start at page number 11760 is a mixed extent and has at least one free page.

If you liked this post, do like my page on FaceBook 

References:
  •  Microsoft® SQL Server® 2008 Internals by Kalen Delaney, Paul S. Randal, Kimberly L. Tripp , Conor Cunningham , Adam Machanic. ISBN :0-7356-2624-3
  • MSDN  http://msdn.microsoft.com/en-us/library/ms175195(v=sql.100).aspx




45 comments:

  1. Replies
    1. Well explained , really liked it

      Delete
  2. great explanation! very appreciated - keep up the good work!

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Valuable piece of information...
    We people live in abstract world of sql server where we concern only about firing commands/queries for table creation,Insertion, update and deletion.Feels great after knowing these internal concepts.....


    Thank u so much sir

    ReplyDelete
  5. One of the finest article related to pages

    ReplyDelete

  6. Nice Article !

    Really this will help to people of SQL Server Community.
    I have also prepared small note on this, Internal architecture of SQL Server Extent

    http://www.dbrnd.com/2016/04/sql-server-understanding-the-role-of-extent/

    ReplyDelete
  7. Well explained , really liked it

    ReplyDelete
  8. Your website content nice nice and interesting to observe.
    Internet Marketing Dienstleistungen

    ReplyDelete
  9. Thanks please share this information if you are face any problem in Toshiba laptop. We will help you for more detail visit here
    Cara Menghilangkan Benjolan di Payudara
    Cara Menghilangkan Benjolan di Kepala
    Cara Menyembuhkan Thalasemia

    ReplyDelete
  10. Take a second and imagine what it’ll be like when your doctor tells you you’re free and you don’t need diabetes medication anymore using diabetes freedom program

    https://ebookanalysis.com/diabetes-freedom-review/

    ReplyDelete
  11. This is a website where natural health supplements & programs are scrutinized with our unbiased reviews for the benefit of the consumers out there.
    http://belespas.com/

    ReplyDelete
  12. CryptoMojo.com is the one-stop destination for Crypto News basics, trading tips for bitcoin and other cryptocurrencies, price prediction, and Crypto Reviews

    ReplyDelete
  13. The Health Products Reviews is a site run by a health team. Over a period of time, this health team has realized the problems of the people around the world, people who are constantly being convinced by false claims and deceptive lies of many advertisements and commercials.
    All this has made this health team realize how important it is for the general public to know the truth about the products they are buying. Because of this, we have set up one goal that we plan to achieve in the future.
    Health Program Reviews

    ReplyDelete
  14. In Vegas, we met a doctor who after a few too many drinks, blurted out that he would tell younger men that their ED was psychological to get rid of them.

    And the good news is the Hard Wood Tonic System includes methods that put you in a better state of mind, including one visualization trick which helps you overcome performance anxiety. Other methods reduce stress and anxiety and can boost your mood, all helpful for erections.

    Hard Wood Tonic System Reviews

    ReplyDelete
  15. HairFortin is a three-part system that allows hair to grow thick and healthy again. The first product to use is the HairFortin pills, which is massaged into the scalp to minimize sebum production and unclog the hair follicles and encourage growth.
    HairFortin Reviews

    ReplyDelete
  16. LeptoConnect is a supplement that is mainly concerned in burning unwanted fats so as to give you faster weight-loss results. The stored fats are its main target and its makers were applauded because of this good objective. The best way to fight weight-gain is to prevent the growth of unwanted substances that contributes great in gaining more pounds.
    LeptoConnect Reviews

    ReplyDelete
  17. A completely natural formula that increases the size of your manhood by more than 4.3 inches in less than a few weeks, no matter the current length and girth of your manhood, and even if you’re 18 or 80 years old.
    LiberatorX2 Reviews

    ReplyDelete
  18. A completely natural formula that increases the size of your manhood by more than 4.3 inches in less than a few weeks, no matter the current length and girth of your manhood, and even if you’re 18 or 80 years old.
    Libeartor X2

    ReplyDelete
  19. Effective prostate products will treat this inflammation, shrinking enlarged prostate tissue and relieving this pressure and pain and restoring normal, healthy sexual and urinary function. The VitalFlow Prostate formula appears to include those ingredients proven to affect these changes.
    VitalFlow Reviews

    ReplyDelete
  20. The Parkinson’s Disease Protocol has delivered thousands of similar stories – all from ordinary men and women who decided to hit that disease hard – before it had a chance to ruin their lives.
    Parkinsons Protocol Reviews

    ReplyDelete
  21. GlucoFlow Review: An Effective Treatment Method For Type 2 Diabetes!
    GlucoFlow was developed by Jonathan Garner, who is an Endocrinologist and as a Molecular Biophysics and Biochemistry Researcher. He focused on blood sugar research and tested the efficacy of several treatment methods on individuals with type 2 diabetes. After taking copious notes and testing the efficiency, he finally came up with GlucoFlow, a powerful formula consisting of powerful vitamins and plants. We have included all the features, pros, cons, price, and other details in this detailed GlucoFlow Review.

    ReplyDelete
  22. It has worked wonders for men and women in their 30s, 40, 50s and even 70s. Because it was engineered based on teachings from the longest living doctor in the world, formula is very gentle yet very powerful at the same time.
    Synapse XT Reviews

    ReplyDelete
  23. Is it possible that every single man and women has far more incredible fat burning potential than they ever realized? And trigger your metabolism tapping in to your body true fat burning.
    Meticore Reviews

    ReplyDelete
  24. Toenail fungus Supplements is a unique blend of topical and oral natural, homeopathic medicines that combine to get rid of your nail fungus fast!
    Myco Nuker Review

    ReplyDelete
  25. The ReVision supplement is designed to improve brain functions and vision health. Unlike other dietary supplements, this product is not based on myths, vague or unrealistic combinations of ingredients.

    Used as recommended by the manufacturer, this product not only improves your brain function and vision but also enhances your overall cellular health and immune system. It delivers mental clarity, improved focus, enhanced memory retention, a calmer mind, and sharp eyesight.

    ReplyDelete
  26. https://harmonyevans.com/acidaburn-reviews/

    ReplyDelete
  27. Check out the best Christmas T-shirt Online from our fandom shop. Free Delivery within the UK

    ReplyDelete
  28. Get these unique London gifts and souvenirs that will remind you of your trip in years to come. Log on to British Souvenirs for such London gifts. Buy your souvenirs from the London gift shop now for the best price and offer!

    ReplyDelete
  29. London is full of amazing places, delicious food, and quirky places, and while all the obvious places are a must-see, some of London's most exciting "gems" aren't as well-known as you might expect.
    Get your London Gifts now from the best souvenir store in the Uk.

    ReplyDelete
  30. New Zealand is becoming one of the most popular preferences among Indian students looking to study abroad. study in New Zealand has an excellent education system with many research opportunities, an affordable fee structure, an excellent support system, and a better cost of living found nowhere else in the world.
    Unibays educational consultant is a leading global provider of international educational services. We help Indian students to study abroad. Our success is based on connecting students to the right course at the right university or institution and in the right country.

    ReplyDelete
  31. Studying abroad can be one of the most memorable experiences for a college student. Studying abroad gives students the opportunity to study in a foreign country and experience the fascination and culture of the new country. Contact Unibays educational consultan to know more about studying abroad in 2022.

    ReplyDelete
  32. Telah hadir semakin banyaknya pasaran-pasaran asing yang mengambil nama toto macau. Namun hanya pasaran togel macau tercepat berasal dari website sah totomacaupools.asia atau bisa juga melalui youtube toto macau. Dan hanya situs terpercaya yaang mengambil result dari 2 tempat ini.

    ReplyDelete
  33. One of a college student's most treasured experiences can be studying abroad. Students who study abroad have the chance to do so and experience firsthand the wonder and culture of the host nation.

    ReplyDelete
  34. Your blog is always a valuable resource for SQL Server professionals. This post is no exception - great job breaking down the technical details in an easy-to-understand way! best seo services in gwalior

    ReplyDelete