Database Fragmentation (with emphasis on MS SQL Server)

Database Fragmentation (with emphasis on MS SQL Server)

Recently, I started reviewing a database and we suspect it might be a bit fragmented.

This got me to thinking what design / operational choices can lead to fragmentation.

I will liberally \ borrow from a couple of WhitePapers.  Here they are:

  1. WhiteSands Technology
    http://www.whitesands.com/Products/frag_white.pdf

Here are quick points that might apply to our environment:

  1. Page Splits from Insertions into the middle of Clustered Index.  This can happen when the key columns are not ever increasing.  Note that there is a side-effect to ever increasing Clustered Indexes – They tend to cause hot-spots at the bottom of the pages.  Well chosen Partition keys, that is different from the Clustered keys, may help
  2. Non-Clustered Indexes where the columns are not increasing – Please note that not making a case for ever increasing Non-Clustered Index
  3. Variable length VarChar and Nullable columns where the Application contains “redux-ed” data can also be a bit impacted.  As the data is continually updated, it might have to be moved along more so than strict length data
  4. Data deletion that leave holes in the original layout; especially when those holes can not easily be retrofitted for other means
  5. Bigger than usual record length definition that ends up spanning multiple rows or whose definition reduces the ability of system to fit multiple records o single pages

Got to sign off now.  I am really thankful for the good writing available on http://www.whitesands.com/Products/frag_white.pdf.

Though, the article is principally written for Sybase,  the core points translates seemlessly to MS SQL Server.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s