||[Feb. 9th, 2006|11:01 am]
Snipets from SQL Server Magazine: February 2006
Article: Winning the Fragmentation Battle
Hard Disks (and their role)
Drives dedicated to holding SQL Server's databases will never have fewer than 8196 bytes per transfer, but what you want is a consistent 65,536 (or more) bytes per transfer (65,536 bytes, or 64K, equal one extent). If you see a value that's less than 65,536 as an average, fragmentation is likely to be a problem.
If Performance Monitor shows excessive I/Os but FileMon shows at least 65,536 bytes per I/O it means the database file itself is fragmented on the physical disk. Excessive use of DBCC SHRINKDB can cause this.
(Partial Solution: Use Disk Defrag or a similar tool)
(Complete Solution: Backup the dataase, drop the database, defrag the disk and restore the database from the backup)
(Preemptive Solution: allocate enough space to allow for growth when you first create the database. This will ensure that the database file is consistent across the physical disk.)
Because the clustered index orders the data physically on the disk according to the index columns, fragmentation usually occurs when data has been deleted from a table but no data has been inserted that would fill the empty space. A common reason for this type of fragmentation is having a clustered index on a monotonically increasing primary key, for example an identity column.
Another common way for both clustered and nonclustered indexes to become fragmented is through allocations of extents over time. In an online transaction processing (OLTP) environment, typically new rows are added a few at a time, not in large blocks,; there fore, all the tables in a file group will be allocating extents one or two at a time resulting in scattered extents throughout the file.
DBCC SHOWCONTIG - analysis can slow performance, so only use off hours if performance is already an issue
DBCC SHOWCONTIG FAST - less invasive but can still slow performance
DBCC SHOWCONTIG WITH TABLE - Most detailed
NOTE: This entry will be updated with useful tidbits as I read further.