 Hello, welcome to SSuritech, so we'll decide and this is continuation of SQL Server Performance Tuning. So today we gonna discuss about SQL Page Splits. In the last video of this video series, we have discussed about SQL pages. If you haven't watched, so I would strongly require to watch that video where you can understand about the SQL pages. So let's start with SQL Page Splits. So what is Page Split? So Page Split occurs on SQL pages when a row is required to insert or update into certain page and page doesn't have enough space for this operation. When it happens, Page splits into two pages and some data from page 1 will move to page 2. Next is, this involves the normal IO of inserting a row but the same time updating indexes as well hence it takes a lot of IO. It is certain amount of page splits is normal and expected but too many page splits cause the performance issue. So how we can avoid these page splits, we will discuss in this video. So as we can see, we have two pages, page 1 and page 2. This is our initial page and page 1 contains 1, 2, 3, 5 and 6 and this page 1 is full and page 2 would contain 7, 8, 9, 10 and 11. And we want to insert 4 that is new data into the table. Then this should go in page 1 according to the index. And as we can see, page 1 doesn't have the space. So page 1 will be split into two parts, page 1.1 and page 1.2. So page 1.1 will have 1, 2, 3 and 4 that is our new data and 5 and 6 will move to second page that is page 1.2 and page 2 will have the same. So this is the basic idea about the page split. So here we have few key points. First is trace on 3604. So what is this? So it is used to send the output to the log and this statement is used to redirect the output to the client executing the command. So we will see in the action in this video. Next is dbcc-pace and dbcc-ind. So these two commands are used to find out if page split occurs. For example as we can see dbcc-pace, so here we need to specify database name, then file number, then page number, then print option. So in print option we can specify 0, 1, 2 and 3 according to our requirement. So here as we can see print option 0. So it print just the page header as we have discussed, page would have 3 sections, page header, data row and offset. Next is if we will specify 1 then it will display page header and per page hexadecimal dump. Then 2 page header plus whole page hexadecimal dump. Next is 3 that is page header and detail per row interpretation. So go to on sequence of management studio where you can see how page split occurs. As we can see this is the script and here I just want to check if table exists or not. If table exists then I just want to drop the table. So let me drop the table, command executed successfully. Now I just want to create this table. So as we can see this table has two columns name and team and name would have 500 bytes and team would have 500 bytes. So it would have total 1000 bytes for each row. So let me execute this query to create the table. So table created successfully. Now let me insert a report on this table. So we can insert one row affected. Now we can check yes data inserted successfully. Now this is the command where we can find the information about the page, how many pages we have. We can also check the page type over here. So let me execute this query. So as we can see this is the partition number partition id this is the page type. So this is 10 and 1. As we have discussed in previous video page 10 for imf page and page type 1 for data page. So this is for data page and page id is 2488. So let me copy this and go to on this query. And here as we can see trace on here we need to specify the page number. So this is our page number. Now once we want to execute this we have to use the trace on. Otherwise let me execute this it will not going to return any output. So first of all we need to dvcc trace on then I just want to execute this. So here we can get the information about the page. So go down. So this is the page header as we have discussed section 1 go down and this is the data page as we have discussed section 2 and it would have the information. So as we can see Virat Kohli as we have inserted one record on this. So this is Virat Kohli. Now go down second column is India. So we are getting the India. Now we can go down. And this is offset of the table. So we have three sections and by using this dvcc command we can check the three sections. But here we have only a single page. Now if I want to insert some record on this. So let me insert few record on this table because initially we have only one record on this table and it would have only 1000 bytes. So let me execute this query data should be inserted in this table and we can check the data. So now we have 9 records it means 9000 bytes. So 9000 bytes data shouldn't contain in a single page. So page split should be there. Now we can check by using dvcc command. So this is the command we need to execute. And here as we can see this is page 1 and this is page 2. So for this page we can check the page type is 1 and page type is 1. It means both are data pages. So now go to on dvcc page command and execute the query. Now here we can check it will have the Virat Kohli that is fine. India that is also fine. Second record is Emersoni that is good. India that is good. Third is Juraj that is also good. Then India that is fine. Glenn Maxwell Australia that is also fine. And here we have James Michael Anderson that is also fine. Then England that is good. Alastair Cook that is also good. Then England then under Russell Weston Diz. So we have all these data. Now let me execute our selected statement and here we have data up to under Russell. So here so these 7 records are available in this page. So in second page it should have the Chris Gale and 5 duplicates. Go to on the dvcc command and this time I just want to change the page number that is 2489. Let me execute this query and here go to on the data pages. Inside this data we can see Chris Gale that is fine. Next should be duplicates. So go here 5 duplicates. So these 2 records are moved into second page. So as we can see page splits occurred and few data from page 1 is moved to page 2. So here as we can check for dvcc page here we can specify print option that is 0. In this dvcc page for 0 let me execute this query. So this should have only page header. Now it's fall down and we can see this is page header and it shouldn't contain any other information for 0. As we have checked for 1 and we can also check for 2. So it will contain the hexadecimal dump. So this is our page and page number go down page header that is also fine. Inside this data as we can see we have some hexadecimal dump. So this is the hexadecimal dump with the data. Now we can check for 3 and let me execute and this is our page then go down page header that is fine. In the data page we have Chris Gale but here as we can see it has few more information. This is our page number and it has the information like jam allocated and primary records, memory dump and many more information. So this will be used if required. So I hope you have understand how SQL pages are splits into 2 pages. So if you have still any doubt you can comment on this video so I will respond for the same. Thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.