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