Drop_existing, Fill factor, and Page split

DROP_EXISTING

Compacting the index will speed up performance and reclaim disk space by removing fragmentation of the index. To compact an index, you recreate the index without actually modifying the columns. Nonclustered indexes are rebuilt every time the clustered index for a table is rebuilt. So, if you drop a clustered index and then re-create it, the existing nonclustered indexes are rebuilt twice: once from the drop and once from the creation.

DROP_EXISTING also allows an existing index to be rebuilt without explicitly dropping and re-creating the index. This is particularly useful for rebuilding primary key indexes as other tables may reference a primary key. By specifying the DROP_EXISTING clause, SQL Server will rebuild the index without affecting the primary key constraint.

Fill factor

              Setting the fill factor on a clustered index tells SQL Server to leave blank space at the end of each data page so it has room to insert new data. You specify the fill factor when you create the clustered index. If you specify a fill factor of 70, for example, the data page is filled with 70 percent data and 30 percent blank space. If you specify 100, the data page is filled to nearly 100 percent, with room for only one record at the bottom of the page

sp_configure 'fill factor', 90

GO

Page Split

              When you need to insert data into a page that has become completely full, SQL Server performs a page split. This means SQL Server takes approximately half the data from the full page and moves it to an empty page, thus creating two half-full pages. Remember that this clustered index is a doubly linked list, with each page having a link to the page before it and a link to the page after it. So, when SQL Server splits a page, it must also update the headers at the top of each page to reflect the new location of the data that has been moved. Because this new page can be anywhere in the database file, the links on the pages don’t necessarily point to the next physical page on the disk. A link may point to a different extent altogether, which can slow the system.

When inserting or updating rows, SQL Server might have to rearrange the data on the pages due to the pages being filled up. Page splits are awfully costly operations and can be terrible for performance, because after the page split, data won’t be located on successive physical pages. This condition is commonly known as fragmentation. Page splits occur in a normal system and are simply a part of adding data to your table. Understanding the effect that page splits can have on your data and indexes is important as you tune performance on tables that have large numbers of inserts or updates.