DBCC SHOWCONTIG was replaced by sys.dm_db_index_physical_stats

DBCC SHOWCONTIG was replaced by sys.dm_db_index_physical_stats

DBCC SHOWCONTIG is used to return table/index fragmentation information.  It is a deprecated feature, but it hasn't been discontinued yet.  You can still run it in v2012, but it is recommended to use the replacement DMV, sys.dm_db_index_physical_stats.  In this post I am going to show you a sample use of both features.

We'll do SHOWCONTIG first.  You can create the 'Fragmentation' table as a permanent table, or #temporary table, but I have used a perm table in my sample.

#1 -- DBCC SHOWCONTIG

/* To store your table results. */
CREATE TABLE [dbo].[Fragmentation](
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Level INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL
 ) ON [YourDatabase_Data]
GO

/* Run the SHOWCONTIG */  
SET NOCOUNT ON;

DECLARE
  @tablename VARCHAR (128),
  @tableschema VARCHAR (128),
  @execstr VARCHAR (255),
  @objectid INT,
  @IndexName VARCHAR (255),
  @frag DECIMAL,
  @maxfrag DECIMAL
  
  /* Declare your cursor */
  DECLARE tables CURSOR
  FAST_FORWARD
  FOR
     SELECT TABLE_NAME, TABLE_SCHEMA
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_TYPE = 'BASE TABLE'

  /* Open your cursor */
  OPEN tables

  /* Loop through all the tables in your database */
  FETCH NEXT
  FROM tables
  INTO @tablename, @tableschema

  WHILE @@FETCH_STATUS = 0
  BEGIN
     /* Perform DBCC SHOWCONTIG for all indices of the table. */
     SELECT @tablename = @tableschema + '.' + @tablename
     INSERT INTO YourDatabase.dbo.Fragmentation --- Writing to your Fragmentation table
     EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
     FETCH NEXT
     FROM tables
     INTO @tablename, @tableschema
  END

  /* Close and deallocate your cursor */
  CLOSE tables
  DEALLOCATE tables

  /* Review the index statistics */  
SELECT * FROM YourDatabase.dbo.Fragmentation

SET NOCOUNT OFF;


#2 -- sys.dm_db_index_physical_stats

Now we'll use the DMV.  We'll return the index statistics for ALL user tables.  You will see in our WHERE clause we are looking for fragmentation greater than 30%, and we are excluding the HEAPs.

 /* Use sys.dm_db_index_physical_stats */
SELECT 
     SCHEMA_NAME(t.schema_id) [SchemaName], 
     OBJECT_NAME(ps.object_id) [TableName], 
     i.name [IndexName], 
     ps.Index_type_desc [IndexType], 
     CONVERT(TINYINT,ps.avg_fragmentation_in_percent) [AvgFrag%], 
     CONVERT(TINYINT,ps.avg_page_space_used_in_percent) [AvgSpaceUsed%], 
     ps.record_count [RecordCount], 
     ps.fragment_count [FragmentCount] 
FROM   
    sys.dm_db_index_physical_stats(db_id(db_name()),NULL,NULL,NULL,'DETAILED') ps INNER JOIN sys.indexes i 
      ON ps.object_id = i.object_id 
      AND ps.index_id = i.index_id INNER JOIN sys.tables t  
        ON ps.object_id = t.object_id 
WHERE
    avg_fragmentation_in_percent > 30.0
    AND ps.index_id > 0
ORDER BY 
     [SchemaName],
     [TableName],
     [IndexName]
 

IMPORTANT NOTE:  You may use SAMPLED, LIMITED or DETAILED in the call to sys.dm_db_index_physical_stats.  This is referred to as the 'scanning mode', and it dictates the level of scanning that is performed, AND how much time the process will take.  See here for more information:     http://technet.microsoft.com/en-us/library/ms188917.aspx