Database Management



Database Fragmentation==

One very critical maintenance step that can easily be (and often is) overlooked is the testing and fixing of fragmentation on database servers. Keeping your servers defragmented is important to reduce wasted space and increase search speed on your Database Servers.

There are two main types of fragmentation: Internal Fragmentation and External Fragmentation.

Internal Fragmentation is when records are being stored in a non-contiguous manner. These problems are often caused by data modifications such as deleting, updating and inserting records into the database. These modifications can often cause unused space between the records of a page.

External Fragmentation is when data that are stored on hard disks are not contiguous. If the extents of a table are not stored together, it causes the hard disk to do unnecessary work to find the files it is looking for. Since hard disks are often the slowest components in servers and computers alike, this causes a drastically increased time to retrieve data or perform queries. Another item to keep in mind is that harder a hard disk works, the shorter its theoretical life-span will be.

In an SQL server, the two places that benefit the most from defragmentation are in a Heap and in an Index.


This shows how defragmentation works.

To reduce fragmentation in a Heap, creating a clustered index on the table helps to rearrange the records into their proper order to ensure contiguity. To reduce index fragmentation there are several theories of thought, they are: Alter Index Reorganize, Alter Index Rebuild and Re-creating the index. Several of these have received poor reputations, especially the latter of the list, so exercising due care when maintaining your database is a must. Each database is unique, and its fragmentation problems will be different from that of another company's. Due to this, there is no quick fix for total manual defragmentation. Database server defragmentation software does exist, and would be a worthy investment, especially if your company does not have large DBA time resources.

There are many routes one can take to reduce overall fragmentation of your Database, and hands-down, the most effective way to stop excessive fragmentation is to spend ample time in the planning and coding of your Database. Stopping fragmentation before it happens is much more effective than fixing it in the future. If you exercise care in the creation of your project, you can strengthen the integrity of your database, closing loopholes, and saving time, effort and money down the road.

Source 1 Source 2 Image Source Source 3