Oct 2, 2007
This script was based on the original posted here: http://sql-server-performance.com/Community/forums/p/20584/114940.aspx#114940
I use the REORGANIZE option of ALTER INDEX. It's default is ONLINE, no matter what the engine edition and makes for simpler code.
I also make heavy use of my own logging tables, which are included at the bottom of the sp.
Please, feel free to comment. I like getting feedback about my scripts and it seems rarely that happens...
USE Admin
GO
IF EXISTS (SELECT [name] FROM Admin.sys.objects WHERE [name] = 'usp_DB_DeFrag' AND TYPE = 'P')
DROP PROCEDURE dbo.usp_DB_DeFrag
GO
CREATE PROCEDURE dbo.usp_DB_DeFrag (@DBName sysname, @Percentage float = 10)
-- EXEC Admin.dbo.usp_DB_Defrag @DBName = 'FooDB', @Percentage = 10
AS
/******************************************************************************
**Name: Admin.dbo.usp_DB_DeFrag.sql
**
**Description: Defragment indexes using REORGANIZE for online operation.
**Record historical fragmentation information to a permanant table
**for trend/history analysis.
**
**Depends on: SQL2005 >= SP2 due to object_name() usage. See BOL for details.
**Admin.dbo.Process_Log - Table
**Admin.dbo.FragTracking - Table
**
** TODO: Open to suggestions...
**
**Author: G. Rayburn <grayburn@---.com>
**
**Date: 10/02/2007
**
*******************************************************************************
**Modification History
*******************************************************************************
**
**Initial Creation: 10/02/2007 G. Rayburn <grayburn@---.com>
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON;
DECLARE @DynFragList varchar(1024)
, @DynDBAlter varchar(256)
, @DynDefragDriver varchar(max)
, @DynUpdateStats varchar(1024)
, @OrigRecoveryModel nvarchar(128)
, @Process_Name varchar(150)
, @Message varchar(256)
, @Error int
-- Cursor objects:
, @SchemaName sysname
, @ObjectName sysname
, @IndexName sysname
, @IndexType nvarchar(60)
, @AvgFrag int
, @PageCount int
, @RecordCount int
--, @GhostRecordCnt bigint
--, @Partition int
;
-- DEBUG:
--SET @DBName = 'FooDB'
--SET @Percentage = 10;
SET @Process_Name = 'usp_DB_Defrag run on [' + @DBName + ']';
-- Ensure that @DBName is a valid db for db_id() usage.
IF (db_id(@DBName)) IS NULL
BEGIN
SET @Message = '[' + @DBName + '] is not a valid database on ' + @@SERVERNAME + ', please check your spelling and try again.'
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', 9999, @Message)
RETURN
END;
-- Record startup message:
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, '[START] - usp_DB_Defrag @DBName = [' + @DBName + '], @Percent = ' + CONVERT(varchar(3),@Percentage) + '.')
-- Check & alter recovery model if neccessary:
SET @OrigRecoveryModel = (SELECT CONVERT(varchar(55),DATABASEPROPERTYEX(@DBName, 'Recovery')))
IF @OrigRecoveryModel = 'FULL'
BEGIN
SET @DynDBAlter = 'ALTER DATABASE [' + @DBName + ']
SET RECOVERY BULK_LOGGED';
EXEC (@DynDBAlter);
SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), 'Successfully set database [' + @DBName + '] to BULK_LOGGED recovery model.')
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), 'Failed to set database [' + @DBName + '] to BULK_LOGGED recovery model.')
END;
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, 'Database [' + @DBName + '] is in ' + @OrigRecoveryModel + ' recovery model so no need to change it.')
END;
-- Temp table of initial DBCC results:
CREATE TABLE #_FragList
(
ObjectName varchar(100)
, [Object_ID] int
, Index_ID int
, Partition_Number int
, IndexType varchar(60)
, alloc_unit_type_desc nvarchar(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
, ghost_record_count bigint
);
INSERT INTO #_FragList
SELECT
LEFT(object_name([object_id], db_id(@DBName)),100)
, [object_id]
, index_id
, partition_number
, index_type_desc
, alloc_unit_type_desc
, avg_fragmentation_in_percent
, avg_fragment_size_in_pages
, avg_page_space_used_in_percent
, fragment_count
, page_count
, record_count
, forwarded_record_count
, ghost_record_count
FROM sys.dm_db_index_physical_stats (db_id(@DBName), NULL, NULL, NULL, 'DETAILED')
WHERE avg_fragmentation_in_percent >= @Percentage
AND index_id >= 1
AND page_count >= 1000
ORDER BY -- Ensure Clustered indexes are rebuilt first.
[object_id]
, index_id ASC;
CREATE INDEX IDX_ObjNameIndexID ON #_FragList (ObjectName, Index_id);
-- Historical tracking:
INSERT INTO Admin.dbo.FragTracking
SELECT @DBName
, ObjectName
, [Object_ID]
, Index_ID
, Partition_Number
, IndexType
, alloc_unit_type_desc
, avg_fragmentation_in_percent
, avg_fragment_size_in_pages
, avg_page_space_used_in_percent
, fragment_count
, page_count
, record_count
, forwarded_record_count
, ghost_record_count
, getdate()
FROM #_FragList
ORDER BY [Object_ID]
, Index_ID ASC;
-- Create & populate Temp table to drive defrag operations from.
CREATE TABLE #_DefragDriver
(
IdentID int IDENTITY(1,1)
, SchemaName sysname
, ObjectName sysname
, IndexName sysname
, IndexType varchar(60)
, avg_fragmentation_in_percent float
, page_count int
, record_count int
, ghost_record_count bigint
, partition_number int
);
SET @DynDefragDriver = '
USE [' + @DBName + ']
INSERT INTO #_DefragDriver
SELECT schema_name(so.schema_id)
, fl.[ObjectName]
, si.[name]
, fl.IndexType
, fl.avg_fragmentation_in_percent
, fl.page_count
, fl.record_count
, fl.ghost_record_count
, fl.partition_number
FROM #_FragList fl
, [' + @DBName + '].sys.indexes si
, [' + @DBName + '].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 si.is_disabled = 0
AND si.allow_page_locks = 1
GROUP BY so.schema_id
, fl.[ObjectName]
, fl.[object_id]
, fl.index_id
, si.[name]
, fl.IndexType
, fl.avg_fragmentation_in_percent
, fl.page_count
, fl.record_count
, fl.ghost_record_count
, fl.partition_number
ORDER BY fl.[object_id]
, fl.index_id ASC; '
EXEC (@DynDefragDriver);
-- Do the defrag.
DECLARE curDBFrag CURSOR
FOR
SELECT SchemaName
, ObjectName
, IndexName
, IndexType
, avg_fragmentation_in_percent
, page_count
, record_count
--, ghost_record_count
--, partition_number
FROM #_DefragDriver
ORDER BY IdentID ASC;
OPEN curDBFrag
FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag, @PageCount, @RecordCount --, @GhostRecordCnt, @Partition
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- ALTER INDEX operations:
SET @Message = 'Table: [' + @ObjectName + '] with record count: ' + CONVERT(varchar(15),@RecordCount) + ' and page count: ' + CONVERT(varchar(15),@PageCount) + '. Index: [' + @IndexName + '] of type: ' + @IndexType + ' is ' + CONVERT(varchar(5),@AvgFrag) + '% fragmented.';
SET @DynFragList = 'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @ObjectName + '] REORGANIZE;'
EXEC (@DynFragList);
SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), @Message)
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), @Message)
END;
-- UPDATE STATISTICS operations:
SET @Message = 'UPDATE STATISTICS [' + @SchemaName + '].[' + @ObjectName + '] [' + @Indexname + '];'
SET @DynUpdateStats = '
USE [' + @DBName + ']
UPDATE STATISTICS [' + @SchemaName + '].[' + @ObjectName + '] [' + @Indexname + ']; '
EXEC (@DynUpdateStats);
SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), @Message)
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), @Message)
END;
-- Friendly WAITFOR operation:
WAITFOR DELAY '00:00:05.000'
END;
FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag, @PageCount, @RecordCount --, @GhostRecordCnt, @Partition
END;
CLOSE curDBFrag
DEALLOCATE curDBFrag;
-- Reset FULL recovery model.
IF @OrigRecoveryModel = 'FULL'
BEGIN
SET @DynDBAlter = 'ALTER DATABASE [' + @DBName + ']
SET RECOVERY FULL';
EXEC (@DynDBAlter);
SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), 'Successfully reset database [' + @DBName + '] back to FULL recovery model.')
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), 'Failed to reset database [' + @DBName + '] back to FULL recovery model.')
END;
END;
-- Record complete message:
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, '[COMPLETE] - usp_DB_Defrag @DBName = [' + @DBName + '], @Percent = ' + CONVERT(varchar(3),@Percentage) + '.');
-- Cleanup:
DROP TABLE #_FragList;
DROP TABLE #_DefragDriver;
-- Dependancies:
----
----USE [Admin]
----GO
---- DROP TABLE Admin.dbo.FragTracking
----CREATE TABLE FragTracking
----( TrackID int IDENTITY(1,1) -- PRIMARY KEY CLUSTERED
----, DBName sysname
----, ObjectName sysname
----, Object_ID int
----, Index_ID int
----, Partition_Number int
----, IndexType varchar(60)
----, alloc_unit_type_desc nvarchar(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
----, ghost_record_count bigint
----, SnapDate datetime
----);
----
----USE [Admin]
----GO
---- DROP TABLE Admin.dbo.Process_Log
----CREATE TABLE [dbo].[Process_Log](
----[MessageID] [int] IDENTITY(1,1) NOT NULL,
----[Date] [datetime] NOT NULL,
----[Process_Name] [varchar](150) NULL,
----[Severity] [varchar](15) NULL,
----[ErrorCode] [int] NULL,
----[Message] [varchar](255) NULL,
---- CONSTRAINT [PK_Process_Log] PRIMARY KEY CLUSTERED
----(
----[Date] ASC,
----[MessageID] ASC
----)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Admin_Data]
----) ON [Admin_Data]
----GO
GO
View 18 Replies
View Related
