Thursday, November 8, 2012

SQL Server Interview Question and answers: - How does index affect insert, updates and deletes?

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 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: