Defragmenting the field indexes

I came across an odd problem the other day when one of our 32GB enterprise databases started showing some odd behavior and slowing to a halt. After running my usual checks to make sure the indexes where intact and the transaction logs weren't chocka I was left scratching my head. I tried routing in the execution plans to see where the bottleneck was and it was all pointing to an index issue although I couldn't find anything wrong until I checked the fragmentation levels. They basically showed up as 100% on the primary index of the main table. After some research I found the commands needed to defrag the database and created a routine to do the hard work for me on the rest of the fields in the database. So without further ado .....

CREATE PROCEDURE [IndexDefrag] AS
BEGIN
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR(128)
DECLARE @execstr VARCHAR(255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @IdxName varchar(128)

-- Set the maximum fragmentation level, if the current frag level is below this it will be ignored.
SELECT @maxfrag = 10.0

-- Get a list of all the user tables in the database
DECLARE tables CURSOR FOR SELECT CAST(TABLE_SCHEMA AS VARCHAR(100)) + '.' + CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

-- Create a table to store information on the tables index list
CREATE TABLE #fraglist (ObjectName CHAR(255), ObjectId INT, IndexName CHAR(255), IndexId INT, Lvl 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)

-- Open the cursor, then loop through the list of tables in the recordset
OPEN tables
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0 
BEGIN
  -- Do the showcontig to get a list of all the indexes in the table
  INSERT INTO #fraglist 
  EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
  FETCH NEXT FROM tables INTO @tablename
END

-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor.
OPEN indexes

-- Loop through the indexes and run the ALTER INDEX command to pull the fragmentation down
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IdxName 
WHILE @@FETCH_STATUS = 0 
BEGIN
  PRINT 'Now executing ALTER INDEX [' + RTRIM(@IdxName) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE WITH ( LOB_COMPACTION = ON ) - Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
  SELECT @execstr = 'ALTER INDEX [' + RTRIM(@IdxName) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE WITH ( LOB_COMPACTION = ON )'
  EXEC (@execstr)
  FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IdxName
END

-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
 
-- Delete the temporary table.
DROP TABLE #fraglist
END