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



119 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
  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. 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
  5. 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




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

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

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

    ReplyDelete
  9. 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
  10. Hi,
    this is very informative post. thanks for sharing such a nice post.
    oracle fusion SCM online training


    ReplyDelete
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. This is first time I have come across your site. which is really useful for me. Great effort.
    DBA training

    ReplyDelete
  17. 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
  18. 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
  19. 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
  20. 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
  21. Appreciating the persistence you put into your blog and detailed information you provide.
    hadoop training in bangalore

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

    ReplyDelete
  23. 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
  24. 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
  25. 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
  26. 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
  27. Nice information about test automation tools my sincere thanks for sharing post Please continue to share this post.

    Weblogic Admin Training

    ReplyDelete
  28. 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
  29. This Blog Provides Very Useful and Important Information Digital Transformation Consulting Thanks for sharing.

    ReplyDelete
  30. 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
  31. Amazing post!!! This post is very comprehensive and i learn more ideas. Thanks for your post, i like more updates from your blog...
    PHP Training in Bangalore
    PHP Course in Bangalore
    PHP Course in Perambur
    PHP Classes near me
    PHP Training in Karappakkam

    ReplyDelete
  32. Nice blog..! I really loved reading through this article. Thanks for sharing such a
    amazing post with us and keep blogging... iot training in chennai | iot training in chennai quora | iot training and placement in chennai | iot training center in chennai | best iot training centre in chennai

    ReplyDelete
  33. nice and really helpful article to everyone... thanks for sharing

    Tableau Training in Chennai
    AWS Training in Chennai

    ReplyDelete
  34. Physiotherapy is believed to lead to simple movement of muscles and joints to alleviate pain or in case of issue in mobility, however, medicos say that it has numerous other miraculous advantages like the improvement of respiratory organ functions, speedy tissue healing and is useful in healing burns additionally. Studies say that physiotherapy does not solely impact body’s function, however additionally improves the understanding of integration between systems.
    massage therapy in calgary
    best physiotherapist calgary

    ReplyDelete
  35. The objective of Blooming Lotus(Day Care Centre) is to produce a child who is beginning to feel confident about self, enjoys creative tasks, can take simple decisions easily, enjoy the process of learning and achieve wholeness.
    Playgroup in Anna Nagar
    Kidz Pre School in Anna Nagar
    Best Pre School in Anna Nagar
    Day Care Centre in Anna Nagar
    Nursery school in Chennai

    ReplyDelete
  36. The mission of Nishtha is to promote that knowledge base along with ethics and good conduct in professionals, educate and orient them in the field of project management and develop skills in them that would then bring immense value in Organizations for management of their projects in a superior and successful manner.
    pmp training centers in chennai
    ms project training in chennai
    pmp certification online

    ReplyDelete
  37. This is a great article thanks for sharing this informative information. I will visit your blog regularly for some latest post. I will visit your blog regularly for Some latest post. odzyskiwanie danych Warszawa

    ReplyDelete
  38. You might comment on the order system of the blog. You should chat it's splendid. Your blog audit would swell up your visitors. I was very pleased to find this site.I wanted to thank you for this great read!!
    digital marketing courses in pune

    ReplyDelete
  39. One of the best TV repair and service company in Chennai, with experience in repairing all brands and sizes of televisions (LG, Samsung). Our engineers are skilled to repair all
    makes and models of TV. Including : big screen TVs, LCD, LED, UHD, OLED.
    samsung tv service center Besant Nagar
    samsung tv service center in Besant Nagar
    lg tv service center Besant Nagar
    lg tv service center in Besant Nagar
    lg tv service center in chennai

    ReplyDelete
  40. Such an informative and helpful, Thank you for sharing this wonderful post.


    Data Science Courses Bangalore

    ReplyDelete
  41. Thanks for this grateful information. all this information is very important to all the users and can be used good at all this process.


    Best Web Designing Training Academy in Kanchipuram

    ReplyDelete
  42. I really enjoyed reading this post, big fan. Keep up the good work andplease tell me when can you publish more articles or where can I read more on the subject?





    BIG DATA COURSE MALAYSIA

    ReplyDelete
  43. Kunci sukses untuk menang dalam taruhan judi poker adalah paham dengan susunan kartu dan membaca teknik main lawan
    asikqq
    http://dewaqqq.club/
    http://sumoqq.today/
    interqq
    pionpoker
    bandar ceme
    freebet tanpa deposit
    paito warna terlengkap
    syair sgp

    ReplyDelete
  44. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.Data Science Courses

    ReplyDelete
  45. Thank you for your post, I look for such article along time, today i find it finally. this post give me lots of advise it is very useful for me.
    It should be noted that whilst ordering papers for sale at paper writing service, you can get unkind attitude. In case you feel that the bureau is trying to cheat you, don't buy term paper from it.www.technewworld.in

    ReplyDelete

  46. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!.www.technewworld.in

    ReplyDelete


  47. Great post i must say and thanks for the information. Education is definitely a sticky subject. it is still among the leading topics of our time. I appreciate your post and looking for more.www.technewworld.in

    ReplyDelete
  48. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!.
    , love it , thank u.

    ReplyDelete
  49. Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man,Keep it up.
    How to write best comment that approve fast

    ReplyDelete
  50. I will do niche blog comment Just in 5$ .All comment relevant with your niche and UNIQUE .This off-page seo will increase your traffic and promote your business. data sgp

    ReplyDelete
  51. Your info is really amazing with impressive content..Excellent blog with informative concept. Really I feel happy to see this useful blog, Thanks for sharing such a nice blog..
    If you are looking for any Big data Hadoop Related information please visit our website Big Data Training In Bangalore page!

    ReplyDelete
  52. Wow! Such an amazing and helpful post this is. I really really love it... this is a very useful website Sarkari Job If you are interested in landing a Sarkari Job or a Sarkari Naukri for you, just refer to this site...

    ReplyDelete
  53. In This Summers get the best designer umbrellas for you or for your family members we allso deals in wedding umbrellas and in advertising umbrellas For more info visit links given bellow good jobs
    Ai & Artificial Intelligence Course in Chennai
    PHP Training in Chennai
    Ethical Hacking Course in Chennai Blue Prism Training in Chennai
    UiPath Training in Chennai

    ReplyDelete
  54. You have composed an exceptionally educational article with incredible quality substance and all around spread out focuses. I concur with you on a large number of your perspectives and you have me thinking.


    SEO services in kolkata
    Best SEO services in kolkata
    SEO company in kolkata
    Best SEO company in kolkata
    Top SEO company in kolkata
    Top SEO services in kolkata
    SEO services in India
    SEO copmany in India

    ReplyDelete
  55. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end. Software Testing Training in Chennai | Software Testing Training in Anna Nagar | Software Testing Training in OMR | Software Testing Training in Porur | Software Testing Training in Tambaram | Software Testing Training in Velachery

    ReplyDelete
  56. Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Financials . Actually I was looking for the same information on internet for Oracle Fusion Financials and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more aboutOracle Fusion Financials . By attending Oracle Fusion FinancialsTraining .

    ReplyDelete
  57. Thanks for sharing valuable information.

    ReplyDelete
  58. This field of study deals with many disciplines such as chemistry, physics, physiology, biology, hygiene, economics, rural development, child development, sociology and family relations, community living, art, food, nutrition, clothing, textiles and home management data science course in india

    ReplyDelete
  59. Thanks for Sharing a Very Informative Post & I read Your Article & I must say that is very helpful post for us.
    Python Classes in Pune

    ReplyDelete
  60. افضل واقوى الشركات التي تقدم خدمات مكافحة حشرات المنزل البق والصراصير والنمل والعته تلك التي تستخدم مبيدات آمنة في ارخص شركة مكافحة حشرات بجدة تلك التي تتعامل في مكافحة الحشرات المنزلية وتستخدم مبيدات آمنة ومضمونة كما هو الوضع او النظام في مثل افضل شركة مكافحة حشرات بمكة المكرمة بحيث تتعامل مع الحشرات في المنازل وترى اننا في مثل تلك الحالات اقوى شركة مكافحة حشرات بالرياض وهي تستخدم مبيدات آمنة ومضمونة وفعالة في التخلص من الحشرات في ارخص شركة مكافحة الحشرات بالمدينة المنورة واجود في تقديم الخدمات الخاصة باعمال افضل شركة رش مبيدات بجدة وهي تستخدم مبيدات آمنة وفعالة ومضمونة

    ReplyDelete
  61. This is a very nice one and gives in-depth information. I am really happy with the quality and presentation of the article. I’d really like to appreciate the efforts.
    AWS training in Bangalore

    ReplyDelete
  62. https://bangaloredigitalmarketing.com/seo-company-in-bangalore/
    https://bangaloredigitalmarketing.com/seo-training-in-bangalore/
    https://bangaloredigitalmarketing.com/ppc-services-in-bangalore/
    https://bangaloredigitalmarketing.com/social-media-marketing-agency-in-bangalore/
    https://bangaloredigitalmarketing.com/content-writers-in-bangalore/
    https://bangaloredigitalmarketing.com/web-design-company-in-bangalore/
    https://bangaloredigitalmarketing.com/brochure-designers-in-bangalore/
    https://bangaloredigitalmarketing.com/logo-designers-in-bangalore/
    https://bangaloredigitalmarketing.com/local-seo-services-in-bangalore/
    https://bangaloredigitalmarketing.com/digital-marketing-course-in-bangalore/

    ReplyDelete
  63. Sutton Mini Cabs Airport Transfers Link Airport Cars 24/7 hours Sutton taxi service to and from all London Airports. Our drivers are PCO licensed. Book Now
    Sutton Airport Cars
    Heathrow Airport Transfer
    Gatwick Airport Transfer

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

    ReplyDelete
  65. Excellent post. I was looking for this certain information for a very long time. If you are a students and looking for assignment on API Development then you can visit: API Development Assignment Help

    ReplyDelete
  66. Thank you for creating one of the most excellent essays I've ever read. If you are looking for best email advertising marketing services then you can visit: email advertising marketing services

    ReplyDelete
  67. This is very well written and your blog points are great! If you are looking for jobs in Java Software Development as a freelancer then this platform will help you, just visit: Find Java Software Development Jobs Freelancer

    ReplyDelete
  68. Your post is an excellent example of why I keep coming back to read your amazing and high quality content. If you are looking for Furnishing & Decor items then MyFirsToys provides a variety of furnishing and decor items that are perfect for creating a playful and inviting atmosphere in children's spaces.

    ReplyDelete