SQLServerCentral Article

Performance Monitoring by Internal Fragmentation Measurement


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


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



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating