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. In the case of a database, we start out with the data looking more like a phone book, and end with the data looking more like a history book. Therefore, we need to occasionally resort the data in an effort to recreate the phone book order. Below, you will see a graphical presentation of how SQL Server lays out the data so that we can discuss the actual findings more clearly.
A Quick SQL Server Internals Discussion
We are most familiar with the data row. The row size is set only by the definition of the table that holds it (e.g. A table of addresses require more data per row then a table of class names). In SQL Server, a table may define a row as storing as little as 4 bytes to as much as 8060.This limit is set by the size of the data page, which stores up to 8,192 bytes (8 KB). The remaining 132 bytes are used by SQL Server to track other information under the covers. Although SQL Server is designed around 8 KB pages, the smallest unit of data that SQL Server can allocate is 64 KB. This is called an extent.
To store the data in a sorted order, as in a phone book, SQL Server uses something called a clustered index. When a typical database is created, clustered indexes exist on nearly all tables. However, just because the data exists in sorted order within the page does not mean that it exists as such within an extent. The reason for this derives from situations in which there is no more room on a given page in which it can insert a row. SQL Server then removes approximately half the page and moves it to another page, which is called a Page Split (Page Splits will not occur with clustered indexes on IDENTITY based columns, but hotspotting may). In some cases, it may move that data to another extent altogether, possibly even allocating a new extent to do so. So, while we start off with names beginning with A and ending with H on one page, and names beginning with I and ending with Z on the next page, through usage, we may see that names A through C are now located on one page in one extent, D through E on another extent and S through Z back on the fifth page of the first extent, etc. It is because of the page split that there are times in which we may prefer to use tables with no clustered indexes at all. However, these tables are usually scratch tables which are highly volatile. In those situations, we desire the quicker write times at the cost of slower reads.
Calling DBCC SHOWCONTIG
Using Query Analyzer, connect to the database you wish to view. Next, you will need to get the object id of the table(s) you wish to examine. I have simplified this task to retrieve the top 10 tables by size using the following script.
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
Execute this script in the database that you wish to check, and you will get an output resembling (repeated 10 times, once for each of the 10 largest tables):
DBCC SHOWCONTIG(123456789)
PRINT ”
Copy and paste the complete resultset into your query window and execute it.