If you want to become a better and well-rounded DBA for Teamcenter, understanding DBCC (Database Console Commands) is an important step. In my previous posts in this series, I discussed the basics of DBCC and three commands: DBCC SQLPERF, DBCC HELP, and DBCC CHECKALLOC. This post is about the DBCC SHOWCONTIG command.
The DBCC SHOWCONTIG command displays fragmentation information for the data and indexes of the table that you specify. Here is how to use DBCC SHOWCONFIG:
DBCC SHOWCONTIG('Production.Product')
The output looks like this:
DBCC SHOWCONTIG scanning 'Product' table... Table: 'Product' (1429580131); index ID: 1, database ID: 15 TABLE level scan performed. - Pages Scanned................................: 13 - Extents Scanned..............................: 3 - Extent Switches..............................: 2 - Avg. Pages per Extent........................: 4.3 - Scan Density [Best Count:Actual Count].......: 66.67% [2:3] - Logical Scan Fragmentation ..................: 7.69% - Extent Scan Fragmentation ...................: 66.67% - Avg. Bytes Free per Page.....................: 582.8 - Avg. Page Density (full).....................: 92.80% DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
That's a lot of information. Let's break it down and examine it.
To create the sample report I show above and scan all the pages of the table in order, DBCC SHOWCONTIG scanned 13 pages in three extents. 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. This sample report tells us that the table is averaging 4.3 pages per extent.
In this report, the server had to switch extent locations (Extent Switches) twice. That means that the server hopped from one extent to the next. DBCC SHOWCONTIG walks the pages at the leaf node level, so there will be more extent switches than extents if the extents are fragmented.
In other words, to walk the pages in order, DBCC SHOWCONTIG had to switch between extents. The number of extent switches can never be less than the number of extents; you have to switch to all the extents to walk all the pages. The best case for performance is to have the number of extent switches in the report equal the number of extents. This would mean that a page scan of the table required no extent switching.
The scan density is the ratio of extent switches to extents. The best scenario here would be 100 percent, an indication that all the pages are in order with the extent. In other words, this is a digestion of the extent and extent switches counts as a percentage.
The average number of pages per extent (Avg. Pages per Extent) in the sample report is 4.3. Pages are 8 KB, and an extent is 64 KB. The maximum number of pages in an extent is 8. Since we only have 4.3 pages per extent, there is some room to add additional pages. This additional room will be filled when data is inserted, maintaining the order of the pages.
Because of the way we envision a page (like paper), sometimes we think of SQL Server pages as two dimensional. In reality, they are a linear allocation of data. When these linear pages are allocated in a contiguous linear order in the extents there is no logical scan fragmentation.
However, if the pages are allocated out of order in the extent, there is logical scan fragmentation. The logical scan fragmentation percentage reports how many of the linear pages are allocated out of order as indicated by their linked next page pointer and their position in the extent. The closer the percentage is to zero, the better the performance.
Extent scan fragmentation exists when extents are out of order on the physical disk. This is different from logical scan fragmentation, which indicates fragmentation of pages in the extent. In other words, the next extent pointer points to an extent that is not at the next physical data location in storage. The closer the percentage is to zero, the better the performance.
In the sample report above, this is 66.67 percent, probably because I made the mistake of defragmenting the logical disk, which moved the extents around on the disk.
The average number of free bytes on the pages (Avg. Bytes Free per Page) is an indication of the room that is available for data expansion. Not every page allocated on the extent is completely full. The higher the number, the less full the pages are. Lower numbers are better; full pages make better use of the storage space. This number is also affected by row size; a large row size can result in a higher number. This number will never be above 8 KB (the maximum size of a page).
The average page density (Avg. Page Density (full)) takes into account row size and returns a percentage. It is a more accurate indication of how full your pages are. The higher the percentage, the better.
DBCC commands provide enormous benefits to the well-informed DBA. I will continue this series of posts with information about the additional DBCC commands that you should know. My next post will be about the DBCC DBREINDEX command, which rewrites your indexes and gives you ideal numbers in the DBCC SHOWCONTIG reports.
Wayne Berry (6230289B-5BEE-409e-932A-2F01FA407A92) is a computer programmer and serial entrepreneur with a passion for dynamic Web sites that run on SQL Server. When he is not blogging for MSDN, you can find him blogging for Project 31-A. http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com