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:
- WhiteSands Technology
Here are quick points that might apply to our environment:
- 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
- Non-Clustered Indexes where the columns are not increasing – Please note that not making a case for ever increasing Non-Clustered Index
- 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
- Data deletion that leave holes in the original layout; especially when those holes can not easily be retrofitted for other means
- 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.