Pages

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



94 comments:

  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


    ReplyDelete
    Replies
    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

      Delete
    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

      Delete
  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

    ReplyDelete
  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.

    http://www.dbrnd.com/2016/04/sql-server-the-internal-storage-architecture-of-the-database-files/

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  6. Packers and movers in bangalore@
    http://www.verified5.co.in/packers-and-movers-bangalore/
    Packers and movers in delhi@
    http://www.verified5.co.in/packers-and-movers-delhi/
    Packers and movers in mumbai@
    http://www.verified5.co.in/packers-and-movers-mumbai/
    Packers and movers in gurgaon@
    http://www.verified5.co.in/packers-and-movers-gurgaon/

    ReplyDelete
  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.
    Regards,
    SAP Training in Chennai with placement | java training in chennai with placement

    ReplyDelete

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

    ReplyDelete




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

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

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

    ReplyDelete
  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

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

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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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


    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete

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

    ReplyDelete

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

    ReplyDelete

  24. your site is genuine to view sir
    thank you for your valluable information sir
    regards
    http://www.erptree.com/course/oracle-fusion-procurement-online-training-in-kolkata/

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  27. ShiftingSolutions.in offers Free Quotes of Registered Movers and Packers Delhi Compare to save money and select the best.

    For packers and Movers enquiry and help, plz visit-
    Packers and Movers in Delhi
    Packers and MOvers Delhi
    Packer and MOver Delhi
    Movers and Packers in Delhi
    Delhi Packers and MOvers

    ReplyDelete
  28. ShiftingSolutions.in offers Free Quotes of Registered Movers and Packers Pune. Compare to save money and select the best.
    http://bit.ly/2vDWaRE

    Packers and movers Pune
    Packers and movers in Pune
    Packers and movers Wakad
    Packers and movers Magarpatta

    ReplyDelete
  29. https://www.transhomepackers.com/packers-and-movers-bangalore
    https://www.transhomepackers.com/packers-and-movers-koramangala
    https://www.transhomepackers.com/packers-and-movers-whitefield
    https://www.transhomepackers.com/packers-and-movers-marathahalli
    https://www.transhomepackers.com/packers-and-movers-indiranagar
    https://www.transhomepackers.com/packers-and-movers-ramamurthy-nagar

    ReplyDelete
  30. This is first time I have come across your site. which is really useful for me. Great effort.
    DBA training

    ReplyDelete
  31. The future of software testing is on positive note. It offers huge career prospects for talented professionals to be skilled software testers.
    Regards,
    Software testing training in Chennai| Best software testing training institute in Chennai

    ReplyDelete
  32. Testing makes your customer to get full satisfaction on your service since it found out all the bugs and errors and rectify it. There are best tool available to test web based applications. Thank you for your information.
    Regards:
    Software testing training | Software training

    ReplyDelete
  33. a pride for me to be able to discuss on a quality website because I just learned to make an article on
    cara menggugurkan kandungan

    ReplyDelete
  34. LoadRunner is the best Performance testing tool to ensure the maximum stress of the system. QTP also a plays a vital role in functional testing, these both simulation is the major testing tool in the software industry.
    Regards:
    Loadrunner Training in Chennai | QTP Training

    ReplyDelete
  35. Really useful information. we are providing best data science online training from industry experts.

    ReplyDelete
  36. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work for you in the future as well
    Send flowers to Pakistan .

    ReplyDelete
  37. I believe there are many more pleasurable opportunities ahead for individuals that looked at your site. php training in chennai

    ReplyDelete
  38. It has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted.

    Data Science Training in Bangalore

    ReplyDelete
  39. Appreciating the persistence you put into your blog and detailed information you provide.
    hadoop training in bangalore

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

    ReplyDelete
  41. Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts, have a nice weekend!
    big data training in bangalore

    ReplyDelete
  42. Great post.Thanks for one marvelous posting! I enjoyed reading it;The information was very useful.Keep the good work going on!!

    ETL Testing training in chennai| SAP MM training in chennai | Informatica training in chennai

    ReplyDelete
  43. Thanks for one marvelous posting! I enjoyed reading it;Great post.The information was very useful.Keep the good work goin on!!
    Hadoop training in chennai | Mainframe training in chennai | SAP SD training in chennai

    ReplyDelete
  44. Congrats for your work. Thanks a lot for providing this with us. Keep on updating this with us regularly..
    Final Year Project Center in Chennai | Final Year Project Center in Velachery

    ReplyDelete
  45. And indeed, I’m just always astounded concerning the remarkable things served by you. Some four facts on this page are undeniably the most effective I’ve had.
    data science training in chennai

    ReplyDelete
  46. It was so good to read and useful to improve my knowledge as updated one.Thanks to Sharing.

    Hadoop online Training|Informatica Online Training|ETL Testing Online Training

    ReplyDelete
  47. The information that you shared with us is very useful and thanks for sharing. Also, many thanks for permitting me to comment!

    Quicken Support Number

    Quicken Online Support Number

    Quicken tech Support Number

    Quicken Activation Licence Key

    HP Support Number

    ReplyDelete
  48. Thanks for the useful information of software testing courses, give more updates on software testing development, First time I visit your blog really nice, I bookmark your blog here after a daily visit. Learn software testing course in Chennai

    ReplyDelete
  49. Nice information about test automation tools my sincere thanks for sharing post Please continue to share this post.

    Weblogic Admin Training

    ReplyDelete
  50. Apple packers and movers Ahmedabad
    http://applepackersgroup.com/index.html
    Apple packers and movers ahmedabad is the best services provided in relocation, sifting by our great experience and eligible employees. Our mane Aims is take your product in relocation carefully. We are provided door to door service. You have any problems about the relocation so you can call 0 769 889 8822, 0 769 889 8833, 0 762 202 2008, Apple packers and movers always comes at your services.

    ReplyDelete
  51. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Data Science Training in Hyderabad

    ReplyDelete
  52. Impressive blog with lovely information. really very useful article for us thanks for sharing such a wonderful blog... 
    Summer Courses in Perungudi | Summer Courses in OMR | Summer Courses in Velachery

    ReplyDelete
  53. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information, this is useful to me…
    Software Testing Summer Courses in Adyar | Summer Courses in Velachery | Java Summer Courses in Perungudi

    ReplyDelete
  54. Thanks for this grateful information. all this information is very important to all the users and can be used good at all this process.
    Selenium Summer Courses in Velachery | Web designing summer Classes in Adyar | Android summer Courses in OMR

    ReplyDelete

  55. Thanks for this grateful information. all this information is very important to all the users and can be used good at all this process.
    ISTQB Certification Training in Chennai | Java Exam Center in Chennai | Microsoft Dot net Certification in Chennai

    ReplyDelete
  56. This Blog Provides Very Useful and Important Information Digital Transformation Consulting Thanks for sharing.

    ReplyDelete
  57. Thanks for giving a great information about data-file-structure DevOps Good Explination nice Article
    anyone want to learn advance devops tools or devops online training visit:
    DevOps Online Training contact Us: 9704455959

    ReplyDelete

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

    ReplyDelete
  59. Loading and unloading comes as a part and parcel of our moving services. In case you are particular about use of machinery for loading and unloading, you can get your option through one of our providers. For very professional and fully managed relocations, opt for our VManage Premium package.
    packers and movers coimbatore
    packers and movers in nashik
    packers and movers in panchkula
    packers and movers in zirakpur
    packers and movers allahabad
    packers and movers in amritsar
    packers and movers mangalore
    packers and movers bilaspur
    packers and movers in ajmer
    packers and movers pondicherry

    ReplyDelete
  60. In the fast paced business world there are always ups and downs. Office moves have become a reality of our lives. Moving India provides comprehensive office relocation services across India.
    Packers and Movers in Bangalore
    Packers and Movers in Pune
    Packers and Movers in Mumbai
    Packers and Movers in Hyderabad
    Packers and Movers in Delhi
    packers and movers in panchkula
    Packers and Movers in Kolkata
    Packers and Movers in Chennai

    ReplyDelete