What is Fragmentation?
Fragmentation is unused
disk space
When does Fragmentation
occur?
Fragmentation is likely
to occur with the following
Read/write databases that users are constantly
updating with data
Databases that execute calculations around
the clock
Databases that frequently update and
recalculate dense members
Data loads that are poorly design
Databases that contain a significant
number of Dynamic Calc and Store members
Databases that use an isolation level of
uncommitted access with commit block set to zero
How can you measure
fragmentation?
You can measure
fragmentation using the average clustering ratio or average fragmentation
Quotient.
Using the average
fragmentation quotient
Any quotient above the
high end of the range indicates that reducing fragmentation may help
performance, with the
following qualifications:
The reported value of the Fragmentation Quotient is
more accurate when there are no other write
transactions running on the database.
For databases less than 50 MB using the
Direct I/O access mode, the fragmentation quotient tends to
be high. A high
fragmentation quotient does not necessarily indicate a need to
reduce
fragmentation, because the free space is created in 8 MB chunks and all of it
might not
get used right away.
Database Size
|
Fragmentation
Quotient Threshold
|
Small (up to 200 MB)
|
60% or higher
|
Medium (up to 2 GB)
|
40% or higher
|
Large (greater than 2
GB)
|
30% or higher
|
Using the average
clustering ratio:
The average clustering
ratio database statistic indicates the fragmentation
level of the data (.pag)
files. The maximum value, 1, indicates no fragmentation.
How do you can prevent and remove fragmentation?
You can prevent and remove fragmentation:
To prevent fragmentation, optimize data loads by sorting
load records based upon
sparse dimension members. For a comprehensive discussion of
optimizing data load by grouping sparse members.
To remove fragmentation, perform
an export of the database, delete all data in the database
with CLEARDATA, and reload the
export file.
To remove fragmentation, force a dense restructure of the
database.
No comments:
Post a Comment