Understanding DBCC SHOWCONTIG

DBCC SHOWCONTIG is a wonderful tool which helps you to understand
quite a bit more about your system than is obvious at first glance. And,
frankly, the documentation doesn't use terminology that makes it very
obvious either. So, this article will focus on a few of the big ideas behind
the tool using SQL Server, and how you can use it to better understand
what is going on inside your SQL Server box. Probably one of the most
significant performance problems found in databases is centered around
table data fragmentation. One situation that may be analogous to table
fragmentation might be an index at the end of a large book. A single index
entry in such a book might point to several pages scattered throughout
the book. You must then scan each page for the specific information you
require. This differs significantly from the index of the phone book which
stores its data in sorted order. A typical query for the name "Jones"
might span multiple consecutive pages, but are always held in a sorted
order.

SELECT TOP 10
'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')'
+ CHAR(10) +
'PRINT '' ''' + CHAR(10)
FROM
sysindexes
WHERE
indid = 1 or
indid = 0
ORDER BY rows DESC

DBCC SHOWCONTIG(123456789)
PRINT ''

Article by Dean Thompson, more