First let me give a short answer so that you can please the interviewer and get your job. If you are inserting heavy data on table which have clustered indexes that will lead to page split. Due to heavy page splits the performance can degrade. So a proper balance of indexes should be maintained for tables which are heavy in transactions and have clustered indexes created.
Now let’s go for a big explanation of what exactly is a page split.
Indexes are organized in B-Tree structure divided in to root nodes, intermediate nodes and leaf nodes. The leaf node of the B-tree actually contains data. The leaf index node is of 8 KB size i.e. 8192 bytes. So if data exceed over 8 KB size it has to create new 8 KB pages to fit in data. This creation of new page for accommodating new data is termed as page split.
Let me explain you page split in more depth. Let’s consider you have a simple table with two fields “Id” and MyData” with data type as “int” and “char(2000)” respectively as shown in the below figure. “Id” column is clustered indexed.
That means each row is of size 2008 bytes (2000 bytes for “MyData” and 8 bytes for “Id”).
So if one more new record is added there is no place left to accommodate the new record and the index page is forced to go for an index page split.
So the more we have page splits, the more the performance will be hit as the processor has to make extrefforts to make those page split’s, allocate space etc.
Now let’s go for a big explanation of what exactly is a page split.
Indexes are organized in B-Tree structure divided in to root nodes, intermediate nodes and leaf nodes. The leaf node of the B-tree actually contains data. The leaf index node is of 8 KB size i.e. 8192 bytes. So if data exceed over 8 KB size it has to create new 8 KB pages to fit in data. This creation of new page for accommodating new data is termed as page split.
Let me explain you page split in more depth. Let’s consider you have a simple table with two fields “Id” and MyData” with data type as “int” and “char(2000)” respectively as shown in the below figure. “Id” column is clustered indexed.
That means each row is of size 2008 bytes (2000 bytes for “MyData” and 8 bytes for “Id”).
So if we have four records the total size will be 8032 bytes
(2008 * 4) that leaves 160 bytes free.Do looat the above image for visual representation.
So if one more new record is added there is no place left to accommodate the new record and the index page is forced to go for an index page split.
So the more we have page splits, the more the performance will be hit as the processor has to make extrefforts to make those page split’s, allocate space etc.
Here’s a awesome video on one more tough SQL Server
interview question: -What are CTE (Common table expression) ? , you watch the
same from by clicking on
Taken from the best selling SQL Server interview question book,
you can see more about the book by clicking on SQLServer interview questions book Dotnet interview questions and answers book
See more stuffs on SQL server interview questions
Regards,
Click here to view more SQL server interview questions and answers .
No comments:
Post a Comment