The DBCC SHOWCONTIG command can be a useful tool to help the
SQL DBA identify inefficient table and index structures. The
command analyzes the internal (page-level) and external
(extent-level) fragmentation. However, the command is very
cumbersome to execute because you must provide the object id for
the table from the sysobjects table and, optionally, the index id
from the sysindexes table. The other problem with DBCC SHOWCONTIG
is that the command can only be run on one table at a time
(Microsoft, are you listening?). The stored procedure below will
run DBCC SHOWCONTIG on all tables, clustered indexes and
non-clustered indexes within a database without having to look up
the object id and index id parameters. This will give the DBA a
snapshot which can be printed out and analyzed. This stored
procedure works only with SQL 7.0!
What is internal fragmentation?
Internal fragmentation occurs when page density is low. Page
density refers to how full, or dense, a page is. Lower page
density equates to more I/O’s when performing a SELECT
statement. In SQL 7.0, a page is 8K. The maximum amount of data
which can be contained is a single row is 8060 bytes, not
including text, ntext and image data. Let’s say that you
have a SQL 7.0 table with a row size of 4,040 bytes. Only one row
will fit on a page in this scenario. However, if you were able to
reduce the row size to 4,030 bytes, then two rows will fit on one
page. This would result in half the number of I/O’s per
SELECT statement, making a much more efficient table design.
Internal fragmentation can be evaluated by looking at the
"Avg. Page Density (full)" line on DBCC SHOWCONTIG
output. As a general rule, it should be greater than 90%.
What is external fragmentation?
External fragmentation occurs when extents are not contiguous.
Space is allocated to tables and indexes in extents. An extent is
8 pages. So, in SQL 7.0, an extent is 64K. When extents are out
of order on the disk, this will result in less than optimal data
access. It’s basically the same philosophy as disk
fragmentation. External fragmentation can be evaluated by looking
at the "Scan Density [Best Count:Actual Count]”
line on DBCC SHOWCONTIG output. This value should be 100.00%.
How do you fix fragmentation?
As mentioned above, internal fragmentation can sometimes be
fixed by changing the row size. However, page splits can also
cause internal fragmentation. Page splits occur index when an
insert occurs on a table with a clustered index and there is not
room left on the page to store the new row. SQL will split the
page in half to make room on the page for the new row. This
condition can be corrected by dropping and recreating the
clustered index, which will reallocate the pages in an efficient
manner. External fragmentation can also be corrected by dropping
and recreating the clustered index.
Well… here’s the stored procedure! Just create a new
stored procedure in the database you want to run this in, and
copy and paste this into it.
