Can you explain the concept of SQL Server 8 KB page ?(SQL Server interview questions)

Can you explain the concept of SQL Server 8 KB page ?(SQL Server interview questions)

In this video we will try to understand how SQL Server stores and manages data internally. In order to understand how sql server stores data internally I have created a very simple database here called as test 123 and it has a very simple table here called as tblcustomer which has just one column called as customer name which is having a char datatype which is of length 1000 Whenever we insert update or delete data into the tblcustomer table or into any table of SQL Server At the end of the day all of these data is stored into the MDF file which is our physical file for our SQL Server If you go to your MS SQL data folder in the C program Microsoft SQL Sever You will find this MDF file which is located in the data folder There are two kinds of file one is the MDF file which is the primary data file and the other one is a log file which is the transaction log file This process is not so simple in other words it is not that if you just write a record or if you just read a record from sql server He just goes to the MDF file and starts reading it straight forward from the MDF file no Whenever you read a record or whenever you write a record he actually writes it on a 8 KB chunk size called as page. In other words whenever you insert a row he will actually send it to that 8 KB chunk size which is called as a page Whenever you say you want to read a row he will actually locate that page you know where your data is stored and read it from there whatever insert, update or delete you do on a SQL Server table at the end of the day they get stored into a 8 KB page Let me insert a very simple record into this tblcustomer table let me edit this tblcustomer table here First let’s insert only one record and let’s try to locate this record into that 8 KB page I am going to insert one simple record called as Record1 and I would like to go and locate this Record1 into that page and let me look at how that page looks like and where is this record1 stored Before I move ahead and I show this demonstration of how to locate this record inside a page I would like to talk more about SQL server data here When we say SQL Server data you will have different kinds of data The one which is on your screen is a user data the data which is actually having business sense And then you can have other data like for example SQL server configuration data probably you would like to have data which is telling you know where exactly your pages are stored You can classify SQL server data into two parts or into two types I will say One which is the user data and the other one which is more of a configuration data or the meta data For different kind of data types there are different kinds of SQL server pages For use a define data for example this Record1 data will get stored into something called as a data page Where the actual data of your business get stored When you talk about Index Structure it will get stored into something called as Index Pages When you talk about let’s say where are your pages located, how much memory has been allocated this kind of system information get stored into something called as IAM pages So in other words different kind of data will have different kind of SQL server pages There are like 14 different kind of SQL server pages For this experiment I don’t want to go into those 14 pages and explain you because I want to keep this complete demonstration as simple as possible In order to understand this demonstration we still need to know two different kind of pages the first page is called as a data page This data page is a page where actually your SQL server use a data get stored And the second page is the IAM page IAM stands for Index Allocation Map The IAM pages have the information where all the pages of your SQL server is stored They know that this is the page on this memory allocation is stored This much percentage it is full,this many rows it has and etc We at least need to know two pages for this demonstration one is the data page where actually your data Record1 will get stored and second one is the IAM page which will help us to locate that data page Now in order to know that for this tblcustomer what kind of pages are used we need to use the data base console commands So you can see here I am saying here DBCC DBCC stands for Data Base Console Command please use this command and get me all the Indexes Including the pages that’s why you can see the -1 here The -1 indicates not only the indexes but also the normal page like Data page IAM page etc get me those values also and get me for this data base i.e test 123 and specifically for this table i.e. tblcustomer So let me just go and say execute here He has pulled up two records here. In other words he says that For this table there are two pages has been allocated and you can see the type of the page One is a page type 1 and the other one is of page type 10 This page type 10 is nothing but it’s a IAM page and the page type 1 is a data page IAM pages it has records which tells you that where exactly your data pages are And this 1 indicates the data page When anybody goes and say’s that get me this tblcustomer or insert a record into tblcustomer The first thing you will go and query this IAM page here and from this IAM page we will go and get that data page where he will go and stored the record This IAM page value is 282 and this 282 is link to this 281 and this 281 actually indicates your data page This Record 1 is stored into this data page here called as 281 So we would like to now go and see data page and we would like to see this value record 1 inside it To see the internals of the page we need to use a DBCC commands DBCC please on the Trace On the trace means we would like to see all the details of the page on the sql server management studio itself The next command is DBCC page so you want to see the details of the page which is located in this database test123 The page is located in file number 1 This IAMFID the value is 1 and the page Id number is 281 so 1,281 and the last parameter 1 says that you know it actually tells you that till what detail you want to see the page details If he say 1 you get the overall view if you say 3 you will get a more detail view But for now one will do and let me go ahead and fire this statement let me click on execute If you see the output of the DBCC page is nothing but the full page details And the complete page is divided into 3 sections The first one is the page header the page header actually tells you what kind of page it is for example you can see the type is 1 so 1 means it is a data page which is the previous page and which is the next page in case it’s a index page The first section in a SQL server page in a SQL server page is nothing but the page header The next section which follows is the actual record or the actual data In our tblcustomer we had one record Record 1 we had this 1 row here called as a record 1 so you can see now this is the Record 1 and it also specify that this is a primary record Primary record means its a actual data And after the data there is a row offset This row offset actually tells you that where exactly this record is lying for example this record 1 you know which has which actually lying in this 060 memory There is one record which is in 060 A SQL server page is divided into 3 sections first one is the page header second one followed by the actual data and the final one is nothing but the row offset which basically tells where exactly every row is lying Now that we know that a page is divided into 3 sections. Let’s try to analyse you know each sections get how much bytes So the first thing is let me pull up this excel sheet here and then let me explain you you know where exactly how much bytes goes in The total page size is 8 KB So this Total Page size is KB here so i.e. 8 KB okay Now 1 KB is 1024 bytes so the total bytes of a page is 8 *1024 so i.e. 8192 bytes And currently we have only one record So if you remember our tblcustomer just has one record and this mouse so and this record is of size 1000 bytes why because this char right 1 char=1bytes Basically we have one record and every record is of size 1000 bytes so let me again go back to my excel here we have record number of record so number of record is 1 and a per record size is 1000 bytes the total record size becomes number of records* whatever is the per record size so i.e. 1000 for now I would also like to emphasize The total bytes is char of 1000 The record size is actually of 1007 bytes We have 7 bytes extra to identify that the column is null or not I would also like to also go and add 7 bytes extra here which is nothing but the overhead our total page is divided into 3 sections The first one is the page header The second one is the actual data I will say this is data rows and the last one is the row off set Row offset means it has the address of every record The page header is of 96 bytes Irrespective whatever is the size of the number of rows the page header is always fix of 96 bytes So this page header has information regarding the page Data rows is whatever is your total record size so that means your number of rows into per record size that means this is as it is And the row offset is 2 bytes for one record I have one record here so it is 2 bytes for 1 Record row offset actually tells where exactly each row is located in the page The total bytes free at this moment is 8192 -sum of all these 7087 If I go back here currently I have only one record so if you see there is only one record I have so you can see here the total free bytes is 7087 here and you can see 7087 here in the page header section If I go and add one more record here Let me add one more record here Record 2 If I fire this command here I will also see the Record 2 over here So very quickly if I see here This is Record 1 this is Record 2 for both of these records we have two entries here which points to both of these records If I increase a number of records here The total bytes free is 6078 let me verify with the page header section here it is 6078 But now one of the question which will come to your mind is what happens if you exceed 8 KB page size what happens if this becomes like 6 records or let say 8 records If you see 8 records there are hardly any bytes left out If you look at 7 Records there hardly any bytes left out Let me add here must be 8 Records here I will edit this table and already this table open here 8 records we have When I say 8 records I just have 24 bytes left out If I add one more record let say Record9 Run this DBCC IND to see that what happens We have some extra pages now This 10 is nothing but the IAM page so i.e. 282 and this 282 is now pointing towards 2 data page 282 is pointing towards 281 and 282 is also pointing towards 283 In 281 it has Record1 it has Record2 it has Record3 7 Records After 7 Records what is happened is basically he has only 1033 bytes free In 1033 bytes he cannot put the 8th record He has created one more data page If you see in 283 it should start from Record8 and then Record9 Once your 8KB Page gets filled he will actually create more data page then again you know that data page is of the same type it has the page header then the rows and finally at the down below the offset Point no.1 SQL server stores data in pages every page is of 8 KB size third point SQL server divides the page into 3 sections the first one is the page header which is of 96 bytes followed by the row which is whatever row bytes it is and at the last we have the row offset which actually point towards where actually the row is located and the final point when a page gets filled he will create new pages to accommodate new rows I hope you enjoyed this video. In this video we trying to understand how a SQL server Row gets stored internally what is the page and the structure of the page Thank you so much

49 thoughts on “Can you explain the concept of SQL Server 8 KB page ?(SQL Server interview questions)

  1. I have a simple question.
    After 7 records, we still have 1033 bytes free in first data page and to accommodate 8th record it needs only 1007 bytes even if we take overheads. Why a new page is created for 8th record. Even i tried it on my local DB and getting same result. Must be my fault. pls clear

  2. After 7 records, we still have 1033 bytes free in first data page and to accommodate 8th record it needs only 1007 + 2 for row off set total 1009 bytes.Why a new page is created for 8th record.

  3. Hi Questpond, thank you for these informative videos, I appreciate the hands on examples and the questions at the end! I think the questions are especially useful to check understanding; however, they are only showed in the video which makes them hard to copy/paste, can you put the questions in the video description? Thank you!

  4. Well Done Sir.. All my doubts are cleared now on SQL server page topic …..I have liked your video and subscribed your channel.

  5. Actually, there is a 3rd type of sql server file. The ndf file is a secondary database file for isolating data into a separate file and a filegroup.

Leave a Reply

Your email address will not be published. Required fields are marked *