Sunday, 12 August 2012

SQL Server: Understanding the Data Page Structure

We all know very well that SQL server stores data in 8 KB pages and it is the basic unit of IO for SQL server operation. There are different types of pages like data , GAM,SGAM etc. In this post let us try to understand the structure of data pages.
SQL server use  different types of pages to store different types of data like data, index data,BLOB etc.SQL servers stores the data records in data pages.Data records are rows in heap or in the leaf level of the clustered index.

A data page consist of three sections. Page Header ,actual data and row offset array. A schematic diagram of data pages looks like as below.

Before going into details let us see how this looks  internally in SQL server. Let us create a table and insert some records into it.

FirstName CHAR(200),
LastName  CHAR(300),
Email     CHAR(200),
DOB       DATE,

INSERT INTO Customer VALUES('William','James','','1982-01-20')
INSERT INTO Customer VALUES('Jade','Victor','','1985-08-12')
INSERT INTO Customer VALUES('Jonas','hector','','1980-10-02')
INSERT INTO  Customer VALUES('William1','James','','1982-01-20')
INSERT INTO Customer VALUES('Jade1','Victor','','1985-08-12')
INSERT INTO Customer VALUES('Jonas1','hector','','1980-10-02')
INSERT INTO Customer VALUES('William2','James','','1982-01-20')
INSERT INTO Customer VALUES('Jade2','Victor','','1985-08-12')
INSERT INTO Customer VALUES('Jonas2','hector','','1980-10-02')
INSERT INTO Customer VALUES('William3','James','','1982-01-20')


Now we need to find out the pages allocated to this table. For that we have to use an undocumented command DBCC IND.
The syntax of DBCC IND is given below:

DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 | -2 });
nonclustered indid = non-clustered Index ID
1 = Clustered Index ID
0 = Displays information in-row data pages and in-row IAM pages (from Heap)
-1 = Displays information for all pages of all indexes including LOB (Large object binary) pages and row-overflow pages
-2 = Displays information for all IAM pages

Run the below command from SSMS

DBCC IND('mydb','customer',-1)
The output will looks like as in below picture:

You can see two records, one with page type 10 and other one with 1. Page type 10 is an IAM page and we will talk about different page types in a different post.Page type 1 is data page  and its page id is 114.

Now to see the row data stored in that page , we have to use the DBCC PAGE command. The syntax of DBCC PAGE :
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]);Printopt:
0 - print just the page header
1 - page header plus per-row hex dumps and a dump of the page slot array 
2 - page header plus whole page hex dump
3 - page header plus detailed per-row interpretation

By default the output of dbcc page is sent to error log. To get the output in the current connection , we have to enable the trace flag 3604.You can also use with tableresults along with dbcc page to get the output in table format. Run the below command to get the row data stored in the data page.

DBCC page('mydb',1,114,3)
This will have four section in output.The first section is BUFFER which talk about in memory allocation and we are not interested in that section. The next section is page header which is fixed 96 bytes in size.The size of page header will be same for all pages. Page header section will looks like as below picture.

To know more about these field
The next section is slots where the actual data is stored. I have removed some hex dumps to make it more clear . Each records are stored in a slot. Slot 0 will have the first records in the page and slot 1 will have second records and so on ,but it is not mandatory that these slots should be in the physical order of the data.You can see from the below image that the size of the record is 710 bytes. Out of this 703 bytes are fixed length data and 7 bytes are row overhead.We will discuss about the record structure and row overhead in different post.

The last section of a page  is row offset table and we should run dbcc page with option 1 to get the row offset table at the end.

DBCC page('mydb',1,114,1)

The row offset table will looks like below picture and this should read from the bottom to top.Each slot entry is just a two-bytes pointer into the page slot offset.In our example we have ten records and in the offset table we have ten entries. The first record pointing to the 96th bytes,just after the page header. It is not mandatory to have the first record at 96th bytes.This offset table will helps to manage the records in a page.Each records need 2 bytes of storage in the page for offset array.Consider a non-clustered index over a heap. Each non-clustered index row contains a physical pointer back to the heap row it maps too. This physical pointer is in form of [file:page:slot] - so the matching heap row can be found be reading the page, going to the slot number in the slot array to find the record's offset in the page and then reading the record at that offset.If we need to save a record in between, it is not mandatory to restructure the entire page. it can be easily possible by restructuring only the offset table.

In our case if you look into the page header, free space is 976 bytes, which is equal to
(8*1024)- 96-(10 * 703)-(10*7)-(10*2)
where 8*1024 =  Total number of bytes in the page
                  96 =  Size of Page Header
          10*703 =  Number of records * size of four columns in the table
              10*7 =  Number of records *  row overhead
              10*2 =  Number of records *  size in bytes to store the row offset table

Now we have seen the structure of the page. Let us summarize this . A page is 8KB size. That means 8192 bytes. Out of these, 96 bytes are used for page header which is in fixed size for all data pages. Below that, data records are stored in slots.The maximum length of data records is 8060 bytes. This 8060 include the 7 bytes row overhead also . So in a record you can have maximum of 8053 bytes. The below create table statement will fail.
id         CHAR(8000) NOT NULL,

id1        CHAR(54) NOT NULL

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'Maxsize' failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

The remaining 36 bytes are reserved for slot array entry and any possible forwarding row back pointer(10 bytes). This does not meant that page can hold only 18 (36/2) records. Slot array can grow from bottom to top based on the size of the records.If the size of records is small, more records can be accommodate in a page and offset table will take more space from bottom to top.

Reference:I have learned about the page structure from Paul Randal excellent post on this subject.

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


  1. Great article - must read. There is a little mistake it should be 114 not 144. Very well described and clear. Thank you. Keep posting.

  2. You've plagiarized much of this content directly from my post at

    Please remove this post.

    1. Respected Paul Randal,

      The people like me definitely learn either internals from your books blogs.We do not have any other source of information.This blog is only a bookmark for my learning and about header field I got it from your post only. Is it okie to keep this post by giving reference to your post ? Otherwise let me know , I will remove it immediately.

    2. It's weird to copy and paste an author's work without attribution, and then call it merely a "bookmark". Shame, shame, shame.

  3. Great article with great information. I was searching this kind of information for a long time.
    Thanks for this

    Vinay Kumar

    1. Thank you for reading the article. Do like my FB page to get an update of new article

  4. Any idea, why they came up with this 8kb thingy?
    Why not 16kb?
    Why not 4kb?
    Or is it because on early 32-bit systems, 1st-level-cache of most CPUs was 8kb?


  5. Great article, straight to the point... thnx

  6. Great Sir.....Based on my understanding i deduce that a record must fit within the page.It cannot span more than one page..Please correct me if I'm wrong......

    On Creation of table having size more than 8053 an error will be generated but if i do it for varchar(max) the scenario changed........

    Create table TestTable
    ID varchar(max)

    insert into TestTable values(Replicate('N',8054)

    The above insert statement should have been reported as an error by SQL Server......but it didn't....Moreover to my surprise it executed successfully what added fuel to the fire LEN(ID) from TestTable gave me 8000.....Is there a reason behind this.....And why microsoft has given a cap of 8000 for column size if 8053 is permissible......

    Please reply me this would be a great help and your article has been a great source of learning....Thanks a lot

  7. This is just a duplicate of

  8. Everyone please read this more details

  9. A really usefull article !! Great job !!

  10. A really usefull article !! Great job !!


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



  14. شركة نقل اثاث بالدمام التفاؤل شركة نقل اثاث بالخبر كما انها افضل شركة نقل اثاث بالجبيل نقل عفش واثاث بالجبيل والخبر والقطيف والدمام
    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالجبيل


  15. الرائد من افضل شركات الخدمات المنزلية في المملكة وخدماتها تغطى كل المنطقة العربية للمزيد قم بزيارة
    شركة تنظيف خزانات بمكة شركة غسيل خزانات بمكة
    افضل شركة تنظيف منازل بالمدينة المنورة افضل شركات تنظيف منازل بالمدينة المنورة

    شركة تنظيف مجالس بالمدينة المنورة افضل شركة تنظيف مجالس بالمدينة المنورة
    شركة تنظيف بالمدينة المنورة شركة نظافة بالمدينة المنورة

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

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

  18. النمل الأبيض هي حشره مؤذية بشكل كبيرٌ على المباني وعلى جميع أشكال الأساس فهي حشرة تتغذي على جميع أشكال الأخشاب فهي قادرة على النيل من العفش والأبواب والشبابيك، كما أنها تقوم تشييد جحورها بأحجام رهيب تحت البيوت فهي من الحشرات العاملة في جماعة وعلى شكل مستوطنات.
    شركة مكافحة النمل الابيض بالخرج
    شركة مكافحة حشرات بالخرج
    شركة رش مبيدات بالخرج
    ارخص شركة مكافحة حشرات


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

    شركة تنظيف خزانات بالرياض

    شركة تنظيف منازل بالرياض

    شركة عزل خزانات بالخرج

    شركة مكافحه حشرات بالخرج

    شركة تسليك مجاري بالخرج

    شركة عزل اسطح بالخرج

    شركة تنظيف خزانات بالخرج

  20. This is Rahul, with a degree in aeronautical building. My subject of intrigue was air dynamic information gathering and examination – ordinarily the amount of speed and flight is created and how we can improve it. data science course in pune

  21. I was blown out after viewing the article which you have shared over here. So I just wanted to express my opinion on Data Analytics, as this is best trending medium to promote or to circulate the updates, happenings, knowledge sharing.. Aspirants & professionals are keeping a close eye on Data analytics course in Mumbai to equip it as their primary skill.


  22. Always so interesting to visit your site.What a great info, thank you for sharing. this will help me so much in my learning.



  23. Actually I read it yesterday but I had some thoughts about it and today I wanted to read it again because it is very well written.

    Data science course in malaysia

  24. Attend The Data Science Training in Bangalore From ExcelR. Practical Data Science Training in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Courses in Bangalore.
    Data Science training in Bangalore

  25. Actually I read it yesterday but I had some thoughts about it and today I wanted to read it again because it is very well written.

    Data science course in malaysia

  26. 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!!
    r for Data Science

  27. Attend The Artificial Intelligence course From ExcelR. Practical Artificial Intelligence course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Artificial Intelligence course.
    ExcelR Artificial Intelligence course

  28. Such a very useful article. I have learn some new information.thanks for sharing.
    data scientist course in mumbai

  29. Well, The information which you posted here is very helpful & it is very useful for the needy like me.., Wonderful information you posted here. Thank you so much for helping me out to find the Data science course in mumbai
    Organisations and introducing reputed stalwarts in the industry dealing with data analyzing & assorting it in a structured and precise manner. Keep up the good work. Looking forward to view more from you.


  30. Excelr is providing emerging & trending technology training, such as for data science, Machine learning, Artificial Intelligence, AWS, Tableau, Digital Marketing. Excelr is standing as a leader in providing quality training on top demanding technologies in 2019. Excelr`s versatile training is making a huge difference all across the globe. Enable ​business analytics​ skills in you, and the trainers who were delivering training on these are industry stalwarts. Get certification on "data science hyderabad" and get trained with Excelr.

  31. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    machine learning institute in bangalore

  32. I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site.

    Data science course


  33. Excelr is providing emerging & trending technology training, such as for data science, Machine learning, Artificial Intelligence, AWS, Tableau, Digital Marketing. Excelr is standing as a leader in providing quality training on top demanding technologies in 2019. Excelr`s versatile training is making a huge difference all across the globe. Enable ?business analytics? skills in you, and the trainers who were delivering training on these are industry stalwarts. Get certification on "data science training institutes in hyderabad"and get trained with Excelr.

  34. I am overwhelmed by your post with such a nice topic. Usually I visit your blogs and get updated through the information you include but today’s blog would be the most appreciable. Well done!
    data scientist courses

  35. Nice Article...Very interesting to read this article. I have learn some new information.thanks for sharing.
    Click here

  36. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
    ExcelR data science

  37. It has fully emerged to crown Singapore's southern shores and undoubtedly placed her on the global map of residential landmarks. I still scored the more points than I ever have in a season for GS. I think you would be hard pressed to find somebody with the same consistency I have had over the years so I am happy with that.

    6digitmg best data science courses

  38. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    ExcelR Business Analytics Course

  39. 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. CLICK HERE

  40. Easily, the article is actually the best topic on this registry related issue. I fit in with your conclusions and will eagerly look forward to your next updates.

    IOT Training