SQL2K5 -- Fragmentation Report

I'm putting this out here mostly to find out if I'm doing things 'right' in SQL2K5.

This script is based on Kalen Delaneys` article from SQLMagazine last month (Instant DocID: 47667). There is also a MS Webcast that Kimberly Tripp goes over the same topic.

It's reporting only at the moment, since we don't have a complete (yet) understanding of sys.dm_db_index_physical_stats.

Anybody see anything I should be doing differently? Does SQL2K5 have an alternative to cursors and EXEC statements I should be using? Dynamic SQL is required for this script in order to associate the appropriate object_id with object_name(object_id).



/****** BEGIN SCRIPT ******/

USE Admin
GO

IF EXISTS (SELECT [name] FROM sys.objects WHERE [name] = 'usp_DB_Frag_Detailed' AND TYPE = 'P')
DROP PROCEDURE usp_DB_Frag_Detailed
GO

CREATE PROCEDURE usp_DB_Frag_Detailed (@Percentage INT = NULL)
-- Admin.dbo.usp_DB_Frag_Detailed 5

AS
/******************************************************************************
** ¼ Raylev Database Support & Consulting, 2005.
** This script is provided as is, and it's effects are not liable
**for any impacts/damage to your systems.
*******************************************************************************
**
**Name: usp_DB_Frag_Detailed.sql
**
**Description: Report table/index fragmentation (detailed) on all db's.
**
**
**
**Return values: 0 - Success
**-1 - Error
**
**Author: G. Rayburn
**
**Date: 11/07/2005
**
**Depends on:
**
**
**
*******************************************************************************
**Modification History
*******************************************************************************
**
**Initial Creation: 11/07/2005 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON

IF @Percentage IS NULL
SET @Percentage = 10

DECLARE @DBName VARCHAR(50)
, @DBID VARCHAR(3)
, @DynSQL VARCHAR(1000)
, @Msg VARCHAR(300)
, @MsgRep VARCHAR(300)

-- Header formatting...
SELECT @Msg = 'Detailed fragmentation report for server: ' + @@SERVERNAME + ' on ' + CONVERT(CHAR(19),getdate(),100) + '.'
PRINT @Msg
SELECT @MsgRep=REPLICATE('=',LEN(@Msg))
PRINT @MsgRep
PRINT ''
PRINT ''
PRINT ''


DECLARE curDBFrag CURSOR
FOR SELECT [name]
FROM master.sys.databases
WHERE [name] NOT IN ('model', 'tempdb')
ORDER BY [name]

OPEN curDBFrag

FETCH NEXT FROM curDBFrag INTO @DBName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @DBID = (SELECT database_id FROM master.sys.databases WHERE [name] = @DBName)

SET @DynSQL = '

USE [' + @DBName + ']

PRINT ''' + @DBName + ':''

PRINT ''''

SELECT ISNULL(LEFT(object_name(object_id),40),object_id) AS [ObjName]
, index_id
, LEFT(index_type_desc,25) AS [Index Type]
, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent)) AS [Avg. Frag %]
, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragment_size_in_pages)) AS [Avg. frag size in pages]
, fragment_count
, page_count
, record_count

FROM sys.dm_db_index_physical_stats (' + @DBID + ', NULL, NULL, NULL, ''DETAILED'')
WHERE avg_fragmentation_in_percent > ' + CONVERT(CHAR(2),@Percentage) + '

ORDER BY CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent) DESC

PRINT ''''
PRINT ''''
PRINT ''''

'

EXEC (@DynSQL);

END
FETCH NEXT FROM curDBFrag INTO @DBName
END

CLOSE curDBFrag
DEALLOCATE curDBFrag
--
--sp_help 'sys.dm_db_index_physical_stats'
--
GO


/****** END SCRIPT ******/

FWIW, it would be REALLY nice if formatting could be maintained in this forum.

And here is the associated defrag script (which is subject to change)...

Apologies for the formatting.

Edit 12/28/2005: Added schema_owner to object qualifiers.


/******************************************************************************
** ¼ Raylev Database Support & Consulting, 2005.
** This script is provided as is, and it's effects are not liable
**for any impacts/damage to your systems.
*******************************************************************************
**
**Name: usp_DB_DeFrag.sql
**
**
**Description: Defragment tables/indexes based on output.
**Based on usp_DB_Frag_Detailed procedure.
**
**This script can either be run direct and defragment, or it can be run
** and the output used as a defrag script. Simply change EXEC (@DynDeFragList)
** to PRINT (@DynDeFragList) for script output.
**
** Added DATABASE DDL TRIGGER disable/enable because the developers are just
** dying to make my job harder...<sigh>
**
**
**Author: G. Rayburn
**Date: 12/27/2005
**
*******************************************************************************
**Modification History
*******************************************************************************
**
**Initial Creation: 12/27/2005 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON
GO

IF EXISTS (SELECT [name] FROM tempdb.sys.objects WHERE [name] LIKE '#_FragList%')
DROP TABLE #_FragList
GO

-- Disable ddl database triggers.
DISABLE TRIGGER ALL ON DATABASE
GO

DECLARE @Percentage int
, @DynFragList varchar(1000)
-- Cursor objects:
, @SchemaName varchar(100)
, @ObjectName varchar(100)
, @IndexName varchar(100)
, @IndexType varchar(25)
, @AvgFrag int

SET @Percentage = 10


-- Temp table for later iteration against.
CREATE TABLE #_FragList
(
ObjectName varchar(100)
,Index_id int
,IndexType varchar(60)
,avg_fragmentation_in_percent float
,avg_fragment_size_in_pages float
,avg_page_space_used_in_percent float
,fragment_count bigint
,page_count bigint
,record_count bigint
,forwarded_record_count bigint
)

INSERT INTO #_FragList
SELECT
LEFT(object_name(object_id),100)
, index_id
, LEFT(index_type_desc,25)
, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent))
, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragment_size_in_pages))
, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_page_space_used_in_percent))
, fragment_count
, page_count
, record_count
, forwarded_record_count

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, 'DETAILED')
WHERE avg_fragmentation_in_percent > CONVERT(CHAR(2),@Percentage)
ORDER BY CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent) DESC;


-- Do the defrag.
DECLARE curDBFrag CURSOR
FOR

SELECT schema_name(so.schema_id) AS [Schema]
,fl.[ObjectName]
,si.[name]
,fl.IndexType
,fl.avg_fragmentation_in_percent
FROM #_FragList fl
, sys.indexes si
, sys.objects so

WHERE object_id(fl.ObjectName) = si.object_id
AND fl.index_id = si.index_id
AND object_id(fl.objectname) = so.object_id
AND fl.Index_ID <> 0
AND si.is_disabled = 0


GROUP BY so.schema_id
,fl.[ObjectName]
,si.[name]
,fl.IndexType
,fl.avg_fragmentation_in_percent

ORDER BY fl.avg_fragmentation_in_percent DESC

OPEN curDBFrag

FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @DynFragList = '
PRINT ''-- _.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._''
PRINT ''Table: ' + @ObjectName + '
Index: ' + @IndexName + ' of type: ' + @IndexType + ' is ' + CONVERT(varchar(5),@AvgFrag) + '% fragmented.''

ALTER INDEX ' + @IndexName + '
ON ' + @SchemaName + '.' + @ObjectName + ' REORGANIZE;

PRINT ''''

DBCC UPDATEUSAGE (''' + db_name() + ''', ''' + @SchemaName + '.' + @ObjectName + ''', ''' + @IndexName + '''); -- WITH NO_INFOMSGS;

PRINT ''''

UPDATE STATISTICS ' + @SchemaName + '.' + @ObjectName + ' ' + @IndexName + ';

PRINT ''''
PRINT ''''


'
PRINT (@DynFragList);

END
FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag
END

CLOSE curDBFrag
DEALLOCATE curDBFrag;
GO

-- Enable ddl database triggers.
ENABLE TRIGGER ALL ON DATABASE;
GO

Thanks for the formatting again Satya. <img src='/community/emoticons/emotion-1.gif' alt='' />