Monday, 2 September 2013

SQL Server: Data File Structure

In our earlier posts , we have discussed about various types pages in details. In this post let us discuss about the organization of the data file.In other words how these pages are organized in the data file.

As you know, SQL server divides the data file into 8 KB pages and page is the smallest unit of any IO operation. SQL server refer the first page in the data file as page number 0.In all the data file first 9  pages (till page number 8) are in same order as shown below.In the primary data file, the 10th page (Page number 9) will be the boot page which store the metadata about the database.

The first page,Page number 0 the file header (page type 15). It hold the file header information. There will be only one header page for each data file and that reside in the 0th position. The header page store the file level information like file size ,minimum size,max size and file growth.

The second page , Page number 1 is the first PFS page (Page type 11). PFS page is the second page (Page Number 1) in the data file followed by file header (Page Number 0). GAM and SGAM are used to track the extent allocation status where as PFS pages are used to track page level allocation. While allocating pages , database engine identify the extent with free pages using the GAM and SGAM. Once the database engine found extent with free page, it use the PFS page to identify the unallocated page in that extent and amount of free space in the page. Free space is only tracked for pages storing LOB values (ie text/image, varchar(max),nvarchar(max),varbinary(max) ,row overflow data) and heap data pages. By default LOB data are stored in a separate page by keeping only a pointer along with record stored in the data pages. These are the only pages where data can be stored in the available free pages.For index pages, the data should be stored in the order of index and there is no options in the insertion point of the records.So it will be unnecessary overhead of tracking free space in the index pages. PFS page repeat after every 8088 pages. That means page number 1,8088,16176,24264...are PFS page in every data file.Read more about PFS page

The third page, Page Number 2 is the first GAM(Global Allocation Map) page (Page type 8).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.Read more about GAM pages

The fourth page, Page Number 3 is the first SGAM(Shared Global Allocation Map) page (Page type 8).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.Read more about SGAM page

The fifth and sixth page, page number 4 and 5 are not used in the current architecture of the SQL server. The page type of these pages is 0. DBCC page for these pages will print the header part and it will end with invalid page type error.

The seventh page, Page Number 6 is the first DCM (Differential Change Map) page (Page type 16).SQL Server uses DCM pages  to track extent modified after the last full backup operation. DCM pages track the extents modified after the full backup.DCM has a bit for every extent it tracks. If the bit is 1, the corresponding extent is modified after the last full backup, if the bit is 0, there is no change in the corresponding extent after the last full backup.A DCM page can hold information of around 64000 extents. Precisely DCM pages repeat after every 511232 pages .One DCM page can track the changes of exactly 63904 extents.The second DCM page will be 511238th page. Read more about DCM pages.

The eight page, Page Number 7 is the first BCM (Bulk Change Map) page (Page type 17). SQL Server uses BCM pages to track extent modified by bulk logged operation since last backup log operation. BCM has a bit for every extent it tracks. If the bit is 1, the corresponding extent is modified after the last log backup due to bulk logged operation, if the bit is 0, there is no change in the corresponding extent due to bulk logged operation after the last log backup.A BCM page can hold information of around 64000 extents. Precisely BCM pages repeat after every 511232 pages .One BCM page can track the changes of exactly 63904 extents.The second BCM page will be 511239th page.Read more about BCM pages

The ninth page, Page Number 8 is the first IAM(Index Allocation Map) page (Page type 10). An IAM page track the pages/extents allocation in GAM interval of a partition for specific allocation unit of a table. Read More about IAM pages

The tenth page, Page Number 9 is the boot page Page type 13). Boot page is available as 9th page only in the primary data file. Boot page will not be available in the secondary data file.We can see the content of this page using the DBCC Page command and some of the values stores in this page are self explanatory.If this page is corrupted for some reason, it is not possible to recover the database using DBCC CheckDb. The page restore also will not help in this situation. The only possible way to recover the database is restore from last good backup.

From the 11th page on wards, you can see mix different types pages like data page,index pages,IAM pages,row-overflow pages and LOB pages.Page type of Data pages and index pages is 1 and 2 respectively.Page type of  Row-overflow and LOB pages is 3. You can read more about the data page structure from this post. Data pages and index pages are stored almost in the same structure.

Row overflow pages are used to store the row data that did not fit into the data page.LOB pages are used to store the large objects which are not stored as part of row data.

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


  1. Wonderful article with useful and clear explanation. I really enjoyed by reading your blog post. Thanks a lot for sharing this amazing content with us. I gained extreme amount of knowledge by reading this post

    Dotnet Training in Chennai

    1. Java Training Institutes Java Training Institutes
      Java Spring Hibernate Training Institutes in Chennai J2EE Training Institutes in Chennai J2EE Training Institutes in Chennai Core Java Training Institutes in Chennai Core Java Training Institutes in Chennai

      Hibernate Online Training Hibernate Online Training Hibernate Training in Chennai Hibernate Training in Chennai Java Online Training Java Online Training Hibernate Training Institutes in ChennaiHibernate Training Institutes in Chennai

    2. I have read your blog its very attractive and impressive. I like it your blog.

      Java Training in Chennai Core Java Training in Chennai Core Java Training in Chennai

      Java Online Training Java Online Training Core Java 8 Training in Chennai Java 8 Training in Chennai JavaEE Training in Chennai Java EE Training in Chennai

  2. thanks for sharing wonderful blog with us. its really more helpful to our institute candidates to get aware some useful knowledge keep sharing more information.
    Dot Net training in Chennai

  3. Nice Article !

    Really this will help to people of SQL Server Community.
    I have also prepared small note on this, Basic storage file structure of the SQL Server.

  4. Great post....such a brilliant and mindful post. this packers and Movers is one the well known and reliable packing and moving company that serves best experience.
    Packers And Movers in Bangalore

  5. Thanks for sharing such an informative post. Suppose if anyone interested to learn evergreen technologies through online then landed at Gangboard. SQL Server DBA Training

  6. Packers and movers in bangalore@
    Packers and movers in delhi@
    Packers and movers in mumbai@
    Packers and movers in gurgaon@

  7. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    SAP Training in Chennai with placement | java training in chennai with placement


  8. Thanks for posting this useful content, Good to know about new things here, Let me share this, . LINUX training in pune


  9. I loved the way you discuss the topic great work thanks for the share, Let me share this, vmware training in pune

  10. Best offer quick and easy extensive variety of cars models and brands accessible at Autocarbazar.
    Used Cars in Dwarka

  11. Excellent sharing. Testing is a wonderful field for IT peoples. Want to learn Selenium Training reach GangBoard. Hadoop Online Training

  12. Hi, I am really happy to found such a helpful and fascinating post that is written in well manner. Thanks for sharing such an informative post. keep update your blog. R Programming Online Training

  13. Excellent Sharing. You have done great job. I gathered lots of new information. . Devops Online Training | Data Science Online Training

  14. thanks a lot for sharing information about data file stricture, I think it is important to know!

  15. We are the leading oracle fusion financials online training institute. we have a policy that is regarding
    student development which we care more about understanding of oracle concepts. we would launch a new course
    when technological changes occur in oracle.
    thank regards
    Oracle Fusion HCM Training

  16. hi welcome to this blog. really you have post an informative blog. it will be really helpful to many peoples. thank you for sharing this blog.
    android training in chennai

  17. Erp education institute is the main organization providing oracle fusion procurement education internationally for extra records
    Thanks regard
    oracle fusion procurement online training
    oracle fusion procurement training

  18. Hi,
    this is very informative post. thanks for sharing such a nice post.
    oracle fusion SCM online training

  19. Hi admin.your blog was useful for me.L learnt software related importance.Thanks for sharing.Learn software related courses... Dot Net Training in Chennai
    Android Training in Chennai
    Selenium Training in Chennai
    Hadoop Training in Chennai

  20. Iam very happy to read your blog you will return in well manner. python is scripting language which emphasizes code readability. want to learn python through Online. Its free

    Python Online Training

  21. hi admin.i would like that SQL Server: Data File Structure.It was amazing.Thank you so much for sharing that information... Software Testing Training in Bangalore |
    Software Testing Training Institute in Bangalore |
    Best Software Testing Training in Bangalore


  22. شركة نقل عفش
    اهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
    شركة مكافحة حشرات بالدمام
    شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
    شركة تنظيف خزانات بجدة
    شركة كشف تسربات المياه بالدمام
    شركة نقل عفش واثاث


  23. شركة نقل عفش بالرياض وجدة والدمام والخبر والجبيل اولقطيف والاحساء والرياض وجدة ومكة المدينة المنورة والخرج والطائف وخميس مشيط وبجدة افضل شركة نقل عفش بجدة نعرضها مجموعة الفا لنقل العفش بمكة والخرج والقصيم والطائف وتبوك وخميس مشيط ونجران وجيزان وبريدة والمدينة المنورة وينبع افضل شركات نقل الاثاث بالجبيل والطائف وخميس مشيط وبريدة وعنيزو وابها ونجران المدينة وينبع تبوك والقصيم الخرج حفر الباطن والظهران
    شركة نقل عفش بجدة
    شركة نقل عفش بالمدينة المنورة
    شركة نقل اثاث بالرياض
    شركة نقل عفش بالدمام
    شركة نقل عفش بالطائف


  24. your site is genuine to view sir
    thank you for your valluable information sir

  25. Thank you so much friend this is very good content and important for the many people. packers and movers Hyderabad in madhapur packers and movers Bangalore in marathahalli

  26. Your blog provided us with valuable information to work with. I found so many interesting stuff in your blog especially it's discussion, Each & every tips of your post are awesome. I Really like this post, thanks for sharing.
    Bigcommerce Product Upload