Published on

September 24, 2007

Understanding SQL Server Fragmentation

As a System Manager, one of the common problems you may face is system performance degradation over time. Eventually, end-users start complaining that the system is slow. This can be frustrating because there is currently no way to anticipate this issue. However, the direct cause of this performance degradation is often the state of fragmentation on the disk.

Fragmentation of data is inherent to the manner in which it is stored on a disk. When data is deleted, the space previously occupied by it returns to the general pool of free space on the disk. When new data is written to the disk, it fills up the first available space, regardless of the ultimate size of the data file. If the data exceeds the available space, it flows over to the next available slot, resulting in fragmentation.

This fragmentation can occur with files written directly to the disk, known as External Fragmentation, as well as with data handled internally by a database system, which may appear as a single data file, known as Internal Fragmentation. Both types of fragmentation can lead to performance degradation in computers, as the system spends more resources searching for fragmented data.

To address fragmentation and improve system performance, there are a few options available, with the most popular being disk defragmentation. However, defragmentation is a time-consuming process and is often postponed until the situation becomes acute. System Managers prefer to endure performance degradation rather than decrease system availability or performance for users.

Similarly, in the case of Internal Fragmentation within a database, the process of defragmentation is referred to as reorganization. The consequence of reorganization is the same as with disk defragmentation – improved performance.

However, the challenge lies in timing and scheduling this lengthy and cumbersome process. It is crucial to measure and quantify the level of fragmentation in a given database to alert the System Manager before end-users start experiencing any malfunctions. By managing IT resources and scheduling the reorganization process ahead of time, it becomes possible to prevent performance degradation and user complaints.

One tool that can help with this management task is called “Lacelevel.” This tool measures the physical disorder and bulk of the data, calculating how far it is from the ideal data structure with no fragmentation. It provides a numeric factor based on the distribution of free space and data fragments on the disk, reflecting the delay caused by data disorder and the overall system performance.

Lacelevel can be adjusted for any environment and is based on a patent-pending algorithm. It can be used to measure both External Fragmentation and Internal Fragmentation. In a hard disk environment, the tool analyzes files and their physical location and length to calculate the Fragmentation Factor of the disk. In a database environment, where there is a single internally fragmented file, each data and index portion of the table is considered a separate file for measuring the Internal Fragmentation level of the database.

By utilizing tools like Lacelevel, System Managers can proactively manage fragmentation and optimize system performance. This not only improves user satisfaction but also extends the life of the computer/server, reducing the need for frequent hardware upgrades.

We are currently adapting Lacelevel to work with SQL Server and hope to present a demo of its functionality in the near future to the SQLServerCentral.com community.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.