Fragmentation of Databases

Fragmentation is a little understood but highly impactful element of database server performance. If your databases have a lot of fragmentation, server performance will correspondingly be degraded.

Fragmentation exists in several ways:

  1. Physical fragmentation of your database data and log files.
  2. Logical and physical fragmentation of your database's clustered indexes.
  3. Logical and physical fragmentation of your database's non-clustered indexes.

To the degree that your server has these types of fragmentation, they are impediments to server performance. Why? Because they all produce increased search times and disk accesses. Disk access costs significant amounts of time.

Increased disk access, especially random access, costs time and increases wear and tear on your disk drives. Every time the disk heads have to move, your disks' servo motors are being used. Because a disk drive is an electro-mechanical device, it is destined to wear out and fail. All disk drives have a 100% failure rate. If your current production environment relentlessly uses your disk drives, their lifetimes will be shortened.

What can be done to minimize fragmentation?

Answers:

In Database Design:

Clustered indexes, non-clustered indexes, and primary keys need to be designed and created appropriately to minimize the causes of fragmentation.

Database tables need to be designed to consider database page packing density.

In Operations:

Database file growth must be configured appropriately by your DBA. The Microsoft SQL Server defaults are not recommended as a best practice.

Clustered and non-clustered indexes need to be built/rebuilt with appropriate fill factors to minimize fragmentation.

I am an expert on indexes and their suitability for efficiency and performance. I can take a less than optimum database implementation and find ways to improve overall server performance, in spite of the database's implementation deficiencies, by improving indexing.

I can also eliminate physical fragmentation of database data and log files for both new and existing databases.

If you need help with performance tuning your SQL Server,  contact me to schedule a free consultation.