Script: SP To Run DBCC SHOWCONTIG On All Tables
May 14, 2001
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.