SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Performance Monitoring by Internal Fragmentation Measurement

By Koby Biller,

One of the common problems that System Managers face is system performance degradation over time, and eventually the end-users complain that the system is slow. Frustratingly enough, the System Managers currently have no way of anticipating this. In most cases, the direct cause is the state of fragmentation on the disk. The ad hoc solution will always be either an overall resource cleanup and reorganization of the data, replacement of the entire disk, or even a full server upgrade.

Fragmentation of data is inherent to the manner of its storage 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 space first available to it, regardless of the ultimate size of the data file. Data in excess (in size) of the first slot of available space automatically flows over to the next available slot and so on until the data is fully recorded. Thus the data is fragmented upon the face of the disk, in a never ending process of deletion and writing of data.

This phenomenon occurs with files written directly to the disk, referred to as External Fragmentation. as well as with data handled internally by a data base system that externally may appear as one single data file, referred to as Internal Fragmentation. Among the latter one may refer to systems such as SQL, mail applications etc.

The problem of External Fragmentation is very familiar to members of the IT community. The never ending process of delete-write of information on the disk eventually results in an extremely high level of fragmentation. This is the main reason for performance degradation in computers: instead of just using the data, the computer spends most of its resources looking for it.

To rearrange the data on the disk so as to correct the state of fragmentation, there are only a few options, the most popular of which is the defragmentation of the disk. Defragmentation is an extremely long process, and therefore it is postponed and performed only when the situation is clearly acute. If it is clear that it is not possible to rearrange the data within a given time limit (i.e. the weekend...), System Managers prefer to endure heavy performance degradation rather than decrease the availability or performance of the system to the users.

Similarly in the case of Internal Fragmentation, the process of defragmentation is labeled reorganization, but the consequence as described above is identical.

An old proverb goes “If you can’t measure it – You can’t Manage it”. This is true also for Information Technology.

According to a research conducted by IDC, a leading consulting firm in the computers industry, the performance degradation caused by disk fragmentation alone, shortens the life of the computer/server by 30% on average. This translates into a direct increase of 30% in the yearly budget for hardware upgrades. It is possible to minimize this significant sum utilizing traditional Defrag and Reorganization utilities. The major setback remains the timing and scheduling of this extremely long and cumbersome process. We propose that this is a management task that can, and should, be undertaken.

Suppose that the level of chaos (i.e. fragmentation) of the data in a given Data Base can be calculated and quantified. Such a factor will alert the System Manager way before the end-users start experiencing any malfunction. One will be able to manage IT resources and schedule the reorganization process ahead of time, before it becomes impractical, and before the complaints start arriving.

Such a factor now exists in the world of Disk Management. It is called “Lacelevel” And it can be downloaded at http://www.disklace.com/. This novel tool measures the physical disorder and bulk of the data, and calculates how far it is from the ideal data structure in which there would be no fragmentation at all. It calculates a numeric factor that is based on the specific distribution of free space and data fragments on the disk. The factor reflects the delay caused by the data disorder and thus to the computer in whole, and indirectly indicates the users’ response time and general satisfaction.

This tool can be adjusted for any environment. It is based on a patent pending algorithm, and it could be used to measure External Fragmentation as well as Internal Fragmentation.

In Hard Disk environment, there are files, and the location and length of each file and its extents are physical determined. This information is sufficient to calculate the Fragmentation Factor of the disk. In Database environment there is one single file that is internally fragmented. Therefore in this environment we refer to each DATA and INDEX portion of the table as a separate file for measurement of the Internal Fragmentation level of the data base. This figure correlates to the level of satisfaction of your users, because it reflects the level of data disorder on your disk, and consequently the level of performance of your system.

We have started adapting this module to SQL, and we hope to present a demo of LaceLevel adapted to the database environment in the near future to the SQLServerCentral.com community.

Total article views: 8414 | Views in the last 30 days: 0
Related Articles

Index Fragmentation in SQL Server: Stairway to SQL Server Indexes Level 11

Internal and external fragmentation – Causes, cures, and when not to care


Performance issue due to index fragmentation.

Performance issue due to index fragmentation.


Performance Monitoring by Internal Fragmentation Measurement

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...


Vendor Management Systems

A vendor management system (VMS) is a web-based application that acts as a mechanism for businesses ...


DBMS vs File Management System

This is a high level article that compares the use of a DBMS with file management systems. Interesti...