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




44 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. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune

    ReplyDelete
  8. Well explained , really liked it

    ReplyDelete
  9. http://khairyayman.eklablog.com/http-emc-mee-com-transfer-furniture-almadina-almonawara-html-a126376958 شركة نقل عفش بالمدينة المنورة
    http://khairyayman.eklablog.com/http-emc-mee-com-transfer-furniture-jeddah-html-a126377054 شركة نقل عفش بجدة
    http://khairyayman.eklablog.com/http-emc-mee-com-movers-in-riyadh-company-html-a126376966 شركة نقل عفش بالرياض
    http://khairyayman.eklablog.com/http-www-east-eldmam-com-a126377148 شركة نقل عفش بالدمام
    https://forums.ipa.edu.sa/vb4/member.php?202-%D9%85%D8%AD%D9%85%D8%AF-%D8%AE%D9%8A%D8%B1%D9%8A

    ReplyDelete
  10. https://community.ebay.com/t5/Shipping-Returns/%D8%B4%D8%B1%D9%83%D8%A7%D8%AA-%D9%86%D9%82%D9%84-%D8%A7%D8%AB%D8%A7%D8%AB-%D8%A8%D8%A7%D9%84%D8%B1%D9%8A%D8%A7%D8%B6-%D8%A7%D9%84%D9%85%D8%AA%D8%AD%D8%AF%D8%A9/qaq-p/26773546#M143585
    https://community.ebay.com/t5/Shipping-Returns/%D8%B4%D8%B1%D9%83%D8%A7%D8%AA-%D9%86%D9%82%D9%84-%D8%B9%D9%81%D8%B4-%D9%88%D8%A7%D8%AB%D8%A7%D8%AB-%D8%A8%D8%A7%D9%84%D9%85%D9%85%D9%84%D9%83%D8%A9-%D8%A7%D9%84%D9%85%D8%AA%D8%AD%D8%AF%D8%A9/qaq-p/26768794#M143546
    http://jumperads.unblog.fr/2017/03/22/%d8%a7%d9%81%d8%b6%d9%84-%d8%b4%d8%b1%d9%83%d8%a9-%d9%86%d9%82%d9%84-%d8%b9%d9%81%d8%b4-%d8%a7%d9%84%d9%82%d8%b5%d9%8a%d9%85/
    http://jumperads.unblog.fr/2017/03/20/%d8%a7%d9%87%d9%85-%d8%b4%d8%b1%d9%83%d8%a7%d8%aa-%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%b9%d9%81%d8%b4-%d8%a8%d8%ae%d9%85%d9%8a%d8%b3-%d9%85%d8%b4%d9%8a%d8%b7/
    http://jumperads.unblog.fr/2017/03/20/%d8%b4%d8%b1%d9%83%d8%a9-%d8%a7%d9%84%d9%85%d8%aa%d8%ad%d8%af%d8%a9-%d9%85%d9%86-%d8%a7%d9%87%d9%85-%d8%b4%d8%b1%d9%83%d8%a7%d8%aa-%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%b9%d9%81%d8%b4-%d8%a8%d9%85%d9%83/

    ReplyDelete
  11. http://jumperads.unblog.fr/2017/03/20/%d8%b4%d8%b1%d9%83%d8%a7%d8%aa-%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%a7%d8%ab%d8%a7%d8%ab-%d8%a8%d8%a7%d9%84%d8%b1%d9%8a%d8%a7%d8%b6-%d9%85%d9%88%d9%82%d8%b9-%d8%a7%d9%84%d9%85%d8%aa%d8%ad%d8%af%d8%a9/
    http://jumperads.unblog.fr/2017/03/23/%d8%b4%d8%b1%d9%83%d8%a7%d8%aa-%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%b9%d9%81%d8%b4-%d8%a8%d8%ad%d8%a7%d8%a6%d9%84-2017/
    http://jumperads.unblog.fr/2017/03/23/%d8%b4%d8%b1%d9%83%d8%a9-%d8%aa%d9%86%d8%b8%d9%8a%d9%81-%d9%85%d8%aa%d8%ae%d8%b5%d8%b5%d8%a9-%d9%85%d8%af%d9%8a%d9%86%d8%a9-%d8%a7%d9%84%d9%82%d8%b7%d9%8a%d9%81/
    http://jumperads.unblog.fr/2017/03/23/%d9%86%d9%82%d9%84-%d8%a7%d9%84%d8%b9%d9%81%d8%b4-%d8%a8%d9%8a%d9%86%d8%a8%d8%b9/
    http://jumperads.unblog.fr/

    ReplyDelete
  12. Interesting post! This is really helpful for me. I like it! Thanks for sharing!

    Webseiten Gestaltung Lüdenscheid

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

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

    ReplyDelete
  15. شركة نقل الاثاث بالقاهرة
    شركة نقل موبيليا بمصر
    شركة نقل اثاث بالقاهرة
    اقوي شركة نقل اثاث بالقاهرة بأرخص الاسعار ومميزات كبيرة شركة نقل اثاث بالقاهرة مع كثرة شركات النقل نعد في مؤسساتنا بفضل الله ثم بثقة عملائنا أحسن وارخص شركة نقل اثاث بالقاهرة لأننا نقدم الخدمات في نقل البضائع إلي إي مكان في مصر ونقل الأثاث مهما كان عدده ومن اي مكان أيضا فنحن نستلم الأثاث ونسلمه إلي المكان الجديد مع فريق عمل خبراء وفنيين في نقل العفش بالإضافة إلى إصلاح إي عيوب .
    هل تريد الانتقال وتخشي علي الاثاث من التكسير او الخدوش ؟
    الان يمكنك الانتقال الي أي مكان بكل سهولة ويسر مع شركة نقل اثاث بمصر توفر لك افضل وسائل نقل الاثاث واحدث المعدات وعماله مدربة بالإضافة لتغليف الاثاث حتي لا يتعرض للخدوش ويتم نقله بأحدث وسائل النقل المجهزة خصيصا لنقل العفش بكل يسر لا تقلق بعد الان نحن ارخص شركة نقل موبيليا والافضل علي الاطلاق بين شركات نقل الاثاث بالقاهرة لدينا عروض مميزة فقط تواصل معنا من أي مكان فنحن متواجدون في كل مدن ومحافظات مصر ولدينا فروع متنوعه ومنتشرة بجميع انحاء الجمهورية .
    شركة نقل موبيليا بمصر توفر عليك الكثير من الأعمال الشاقة التي يجب أن تقوم بها قبل وبعد عملية نقل الموبيليا ، يتعرض الكثير منا إلى تغيير مكان معيشته حيث ينتقل إلى منزل أفضل أو يغير مكان عمله فيؤدي ذلك إلى تغيير مسكنه لذا عليه أن يقوم سريعًا بالقيام بنقل عفشه وذلك يتطلب العديد من الأعمال الشاقة التي يجب القيام بها لمحاولة الحفاظ على العفش ومقتنيات الشقة، فيقوم أفراد الأسرة بفك وتغليف قطع الموبيليا المختلفة ويستأجرون عمالة لتنزيل العفش وسيارات لنقل العفش كل هذا وأكثر لكي تحافظ على مقتنياتك ولكن هذا غير مجدي، لكن معنا شركة نقل موبيليا بمصر نقوم بتوفير جميع أسباب الراحة لعملائنا بحيث نقدم العمالة المدربة والفنيين المتخصصين في أعمال الصيانة والفك والتغليف كما توفر كافة انواع السيارات المخصصة في نقل الاثاث للمحافظة عليه من الخدش والفقد.
    شركة نقل عفش بمصر
    إذا كنتم ترغبون في نقل عفش في يوم واحد فقط يمكنكم على الفور التواصل مع شركة نقل عفش بمصر , وهذا لأن شركة نقل عفش بمصر واحدة من أسرع الشركات التي يمكنكم الاعتماد عليها في نقل العفش الخاص بكم من وإلى أي مكان داخل الجمهورية , تؤكد إدارة شركة نقل عفش بمصر أن الشركة رائدة في مجال نقل العفش إلى أي مكان وبكل سهولة يمكنكم التواصل مع الشركة وعلى الفور سوف تلقون المساعدة السريعة من فريق العمل الخاص بالشركة
    https://www.egypt-moving.com

    ReplyDelete
  16. Nice to be visiting your blog again, it has been months for me. Well this article that i’ve been waited for so long. I need this article to complete my assignment in the college, and it has same topic with your article. Thanks, great share

    ReplyDelete
  17. 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
  18. 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
  19. The information you shared was useful. You have brought up a very wonderful points , regards for the post.
    HP printer support | Canon Printer Support| brother printer support | Lexmark Printer Support

    ReplyDelete
  20. Depending on your needs, choosing male enhancement products can sometimes be overwhelming. If you're looking to improve your size and performance then you've got a couple of choices to make.
    http://belespas.com/huge-male-secret-supplement-review/
    http://www.renewmyhealth.org/huge-male-secret-review/

    http://belespas.com/huge-male-secret-supplement-review/

    http://www.renewmyhealth.org/huge-male-secret-review/

    ReplyDelete
  21. When you feel that you are no longer able to control your appetite and all you want to
    do is keep eating then it’s time for you to start using Leptitox Diet Pills.
    <a href="http://www.renewmyhealth.org/leptitox-real-review/">Leptitox Real
    Reviews</a>

    ReplyDelete
  22. Massive Male Plus Supplement Reviews

    There are a lot of fast solutions that one can buy nowadays. But most of the male enhancement
    pills readily available work just for one or two sessions and after that wane. One can sure get a
    hit from them yet they do not function in the long run. If you are trying to find structure from
    durability from the within then Massive Male Plus is one supplement that you could go for.
    http://www.renewmyhealth.org/massive-male-plus-review/

    ReplyDelete
  23. Does male enhancement supplement really work?
    This is why the Huge Male Secret Supplement is made up of two amazing ingredients that will truly make your sex life different from what it was in the past. You will have a bigger penis in length in the perimeter
    huge male secret supplement reviews

    ReplyDelete
  24. Are you wondering why you are not able to achieve results with Law of Attraction? Everyone heard about Law of Attraction, but the problem is most people have incomplete information that leads to misunderstandings. Many books on Law of Attraction contain scanty information that fails to provide valid information on how to implement it. However, recently one top guide is gaining popularity due to its unique approach and explanation about Law of Attraction. This book is called Ultra Manifestation.
    We decided to purchase this Manifestation Hack book to analyze its Manifestation Formula to find out if it works or not. This way we think we are in better position to explain everything about the book to help you in making a better-informed decision.
    For beginners who don’t know, Ultra Manifestation by David Sanderson is a self-development book that helps you learn how you can improve your skills to achieve anything you want in your life. Although this book doesn’t show you how to make money, it teaches you a simple 10-minute audio that you need to earn money and achieve your goals. It shows you a better way to implement Law of Attraction in your life and achieve your goals.
    Manifestation Hack Reviews

    ReplyDelete
  25. Most everyone is looking for a quick, simple answer to “how to start loosing weight”. And as much as a reasoning similar to, “having a healthier body”, should give us the motivation for change, often that’s just not the case. We want to, “look sexier”, “fit into old, cute clothes”, “have our significant other desire our body”, etc. Determining your core motivation is a critical aspect prior to initiating effective diet & exercise plans. The driving factor or reason for your desired BioHarmony Advanced Drops weight loss should be written out, and kept in a secret spot near a fit picture of you or someone who’s figure you hope to resemble upon reaching your weight loss goal.

    After you have defined your inspiration for becoming healthier & achieving weight loss, onto step 2. Taking before pictures & measurements can be very beneficial throughout weight loss & after reaching your goal as a preventive measure. Front, back, & side pictures taken against a white wall with as little clothing as possible is best (underwear, bra, etc).
    BioHarmony Advanced Drops Reviews

    ReplyDelete
  26. The End of Gout works. The science behind it is solid and accepted the world over. Thousands of people like me – happy, ex-sufferers – will confirm that repairing unhealthy bacteria is the way to remedy conditions like gout. Shelly Manning’s program basically hand-held me to health. It walked me from A to Z. Which, to be honest, is pretty much what I needed it to do.
    The End Of Gout Reviews

    ReplyDelete
  27. This is a website where natural health & fitness products are scrutinized with our unbiased reviews for the benefit of the customers out there. Here, natural products for health care are put to the test to see if they really stand up to their expectations and do what they claim they can do.Many people stick to one brand because they know their effectiveness through experience. They are afraid to use other products because they do not know if it will actually work at all.
    Natural Health program & supplement Reviews

    ReplyDelete
  28. You will find reviews of different types of supplements & health programs. Nowadays, we realize that getting best products review are very difficult, but we promise you is always giving the honest benefits & side effects with an easy-to-understand.

    Supplement Reviews

    ReplyDelete
  29. Bioptimizers CogniBiotics contains every strain you need to powerfully support your second brain, feel awesome, and be at your best. And this has prevented you from maximizing your mood, happiness, mental performance... as well as every other part of your health.
    Bioptimizers Cognibiotics Reviews

    ReplyDelete