YADS -- Yet Another Defrag Script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

YADS -- Yet Another Defrag Script

Author

Topic

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-02 : 14:56:31

This script was based on the original posted here: [url]http://sql-server-performance.com/Community/forums/p/20584/114940.aspx#114940[/url]

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...

[CODE]
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

[/CODE]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-02 : 15:02:19

Won't it error when the edition is Standard?

Are you taking into account the special considerations of databases with LOB data and multiple partitions?

Why are you updating the statistics in the same code?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-02 : 15:19:14

quote:
Originally posted by tkizer

Won't it error when the edition is Standard?

Are you taking into account the special considerations of databases with LOB data and multiple partitions?

Why are you updating the statistics in the same code?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

1: Nope. REORGANIZE runs in an ONLINE mode by default, regardless of edition. At least according to BOL it does, I can't find an edition on site that's not either Dev or Ent. I will do what I can to verify this though, as it defenitly is important.

Edit: It does run on Standard edition without issue. I had to sack a developers workstation that had Std. on it and tested it just now.

2a: Not yet. The REORGANIZE operation can handle LOB datatypes much easier (it appears) than REBUILD does.

2b: Not yet, but I have the groundwork in place to handle partitions in the future with little re-coding. However, we are not dealing with them yet in our enviroment.

3: REORGANIZE does not update stats as REBUILD does, it's neccessary to do it after the fact.

Thanks for the reply! :)

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 16:08:29

Good effort!

I'm not keen on the HardWired [Admin] database references. Won;t that be the "current" database when the SProc is called, such that it can be made generic?

Kristen

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-02 : 16:26:42

The procedure makes use of Dynamic SQL in order to resolve object_name(object_id,dbid) properly. Hence the big block of dynamic sql for the #_DefragDriver table.

I try to keep my administrative code and objects as contained as possible in thier own database. The original version of this script was not able to be called from the Admin database and operate in userland databases. It would have needed to be compiled in every database, or possibly master and then marked as a system object.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-02 : 16:31:09

Check out my defrag sproc for how to get around this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-02 : 16:33:14

quote:
Originally posted by tkizer

Check out my defrag sproc for how to get around this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

We both use the same dynamic sql methods to populate the ALTER INDEX statement(s)...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-02 : 16:39:49

I'm confused why you are hard-coding database names then as per your post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-02 : 16:47:17

quote:
Originally posted by tkizer

I'm confused why you are hard-coding database names then as per your post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

The [Admin] references are inserts into tables in the [Admin] database for logging/reporting of the procedure. Didn't mean to confuse....

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 16:52:53

Nah, you didn't confuse! its just that limiting the script to having a "suitable" database called "Admin" will reduce the scritp's appeal.

I appreciate that it works just-fine for you, but the "I'll just try this" brigade are probably going to wind up short of middle-stump!

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-02 : 16:54:21

Agreed. However, I assume that most folks who would use this, already have a database of thier own they use. I also find that it helps to be as explicit as possible when coding. I also try to provide a complete-as-possible solution when giving out code. All that is neccessary for the 'try it crowd' is to change the name from [Admin] to whatever they wish to use, fairly simple change...

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 17:05:19

Which is all perfectly fair-enough.

I lean completely the other way : the scripts I write for public consumption are as generic as possible, so that any Tom+Dick+Harry can run them. Which is why I so rarely publish any because the time taken to make them fully generic takes forever

I mean, look at the MS SQL 2000 Maintenance Wizard Scripts, for example ...

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-02 : 17:07:26

quote:
Originally posted by Kristen

I lean completely the other way : the scripts I write for public consumption are as generic as possible, so that any Tom+Dick+Harry can run them. Which is why I so rarely publish any because the time taken to make them fully generic takes forever

I mean, look at the MS SQL 2000 Maintenance Wizard Scripts, for example ...

I understand completely. I'm looking more for feedback from peers when putting code out these days. But considering it's for public consumption at that point, I tend to lean towards completeness over generic ability.

Maybe one day, I'll share my "Maintenance Plan" style backup routines....

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 17:10:57

"But considering it's for public consumption at that point, I tend to lean towards completeness over generic ability"

Well, I respectfully disagree on that point.

IMHO if you adopt that stance the script won't be used, because the majority will try it, find it doesn't work ("Database [Admin] not found") and give up ...

The cognoscenti will be well-able to change [Admin] to [MyAdminDatabase], but they will probably have already written their own Main Routines, and are just looking for a few Nuggets they haven't thought of, and are happy to steal.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-02 : 17:27:29

quote:
Originally posted by Kristen

"But considering it's for public consumption at that point, I tend to lean towards completeness over generic ability"

Well, I respectfully disagree on that point.

IMHO if you adopt that stance the script won't be used, because the majority will try it, find it doesn't work ("Database [Admin] not found") and give up ...

The cognoscenti will be well-able to change [Admin] to [MyAdminDatabase], but they will probably have already written their own Main Routines, and are just looking for a few Nuggets they haven't thought of, and are happy to steal.

Completely agree with you on that as well.

To be fair, I did write this code for my system(s) and my management, not anyone elses. So if some people can use it, or just bits of it, that's fine. If someone can't use it, because they don't understand it, that's a reality I'm willing to face. ;)

When I put code out these days, it's generally for peer review, not recognition or wanting to help the general populous of DBA's. Don't get me wrong, I'm not looking to become some elitist knob and I do like to help my fellow DBA's; but I'm searching for people I consider smarter or more experienced than myself to help me progress my skills. I don't find a lot of stringent guidlines for this type of work and have no formal education regarding it so peer review is becoming more important.

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 17:34:54

Well, speaking for myself, if I can't use it pretty much as-it-stands then you are only likely to get comments based on things I spot at a first speed-read. Which may miss quite a lot, knowing my speed reading!

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-02 : 20:56:47

quote:
Originally posted by Kristen

Well, speaking for myself, if I can't use it pretty much as-it-stands then you are only likely to get comments based on things I spot at a first speed-read.

That's fine, fire away!

In particular, I think you have some good comments to add to these types of discussions. I saw a thread the other day where you were talking about developers manipulating the column vs. parameter, which is an incorrect method.

To me, it's always seemed proper to manipulate the parameter to fit the column, but I never _knew_ it, until you brought it up.

Feel free to nitpick those little things, that's what I'm looking for.

Kristen
Test

22859 Posts

Posted - 2007-10-03 : 01:00:26

I've done the speed read, and commented from that. Without actually being able to use it you've had all I can offer I'm afraid.

Haywood
Posting Yak Master

221 Posts

Posted - 2007-10-03 : 09:13:14

Well, yeah. I think we've beaten this topic into the ground. But for the future.... ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources