Page split


Page split

When you think of page splits, you normally only think of clustered indexes. This is because clustered indexes enforce the physical order of the index, and page splitting can be a problem if the clustered index is based on a non-incrementing column. But what has this to do with non-clustered indexes? While non-clustered indexes use a clustered index (assuming the table is not a heap) as their key, most people don’t realize that non-clustered indexes can suffer from page splitting, and because of this, need to have an appropriate fillfactor and pad_index set for them.

Here’s an example of how non-clustered indexes can experience page splits. Let’s say you have a table that has a clustered index on it, such as customer number. Let’s also say that you have a non-clustered index on the zip code column. As you can quite well imagine, the data in the zip code column will have no relation to the customer number and will be more or less random, and data will have to be inserted into the zip code index randomly. Like clustered index pages, non-clustered index pages can experience page splitting.

So just as with clustered indexes, non-clustered indexes need to have an appropriate fillfactor and pad_index, and also be rebuild on a periodic basis. 

From  Khan SQL DBA – MCITP