January 3, 2006 at 2:53 pm
I'm looking for a DBCC INDEXDEFRAG script that will defrag the top 10 tables (in DB size and worst defrafgmented). Main issue with running this on a large database is that the Transaction log files build to huge amounts.
I'd like to do the 1st 10 worst, then run later to do the next 10 worst.
Appreciate replies!
January 4, 2006 at 3:27 am
Hi Peter,
following procedure defrags indexes ordered by size desc, fragmentation desc. Stops after a given time, but you can change that to stop after 10 Indexes.
Needs some tables in a db called management, that needs changing too...
regards karl
/***************************************************************************************************************
Written By :
Date :
Purpose :
Input Params :
Output Params :
Updates :
***************************************************************************************************************/
CREATE PROCEDURE sp_index_defrag
@Duration int
AS
DECLARE @msg VARCHAR(999)
/* Run in the database in which the indexes are to be defragmented.*/
SELECT @msg = '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'
RAISERROR(@msg,10,1) WITH NOWAIT
SELECT @msg = convert(char(25),getdate(),113)+': sp_index_defrag: Begin of Procedure... '
RAISERROR(@msg,10,1) WITH NOWAIT
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
, @execstr VARCHAR (255)
, @objectid INT
, @indexid INT
, @frag DECIMAL
, @Dens DECIMAL
, @maxfrag DECIMAL
, @maxDens DECIMAL
, @MaxStart DATETIME
, @Nochmal int
-- Decide on the maximum fragmentation to allow
/* defragmenting only if Fragmentation > 20 %
-> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx */
SELECT @maxfrag = 10.0
SELECT @maxDens = 90.0
SELECT @Nochmal = 1
-- Set the max time do start dbcc indexdefrag for another index
SELECT @MaxStart = DATEADD( mi, @Duration, GETDATE())
SELECT @msg = convert(char(25),getdate(),113)+': sp_index_defrag: Max Startdate for dbcc: ' + convert(char(25), @MaxStart, 113) + '.'
RAISERROR(@msg,10,1) WITH NOWAIT
/* If not exists, create the tmp table */
IF (OBJECT_ID('management.dbo.fraglist')) IS NULL
-- Create the table
CREATE TABLE management.dbo.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
)
/* If not exists, creates the tmp table */
IF (OBJECT_ID('management.dbo.IndexDepthList')) IS NULL
CREATE TABLE [IndexDepthList] (
[ObjectName] [char] (255) COLLATE Latin1_General_CI_AS NULL ,
[ObjectId] [int] NULL ,
[IndexName] [char] (255) COLLATE Latin1_General_CI_AS NULL ,
[Indexdepth] [int] NULL
) ON [PRIMARY]
TRUNCATE TABLE management.dbo.fraglist
TRUNCATE TABLE management.dbo.IndexDepthList
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Open the cursor
OPEN tables
SELECT @msg = convert(char(25),getdate(),113)+': sp_index_defrag: Get properties of all Indexes... '
RAISERROR(@msg,10,1) WITH NOWAIT
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO management.dbo.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
UPDATE management.dbo.fraglist set LogicalFrag = 0 where LogicalFrag = 100
INSERT INTO management.dbo.IndexDepthList
SELECT ObjectName, ObjectId, IndexName, INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') FROM management.dbo.fraglist
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT a.ObjectName, a.ObjectId, a.IndexId, a.LogicalFrag, a.ScanDensity
FROM management.dbo.fraglist a INNER JOIN management.dbo.IndexDepthList b
ON a.Objectname = b.Objectname and
a.ObjectId = b.ObjectId and
a.Indexname = b .Indexname
WHERE (a.LogicalFrag >= @maxfrag or a.ScanDensity 0
AND a.CountPages > 999
-- AND a.CountPages > 9999
/* defragmenting only if > 10 000 Pages
-> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx */
order by a.CountPages desc, a.LogicalFrag desc
-- Open the cursor
OPEN indexes
SELECT @msg = convert(char(25),getdate(),113)+': sp_index_defrag: Defragmenting now... '
RAISERROR(@msg,10,1) WITH NOWAIT
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @Dens
IF @@FETCH_STATUS 0 BEGIN
SELECT @msg = convert(char(25),getdate(),113)+': sp_index_defrag: No Index needs defragmenting... '
RAISERROR(@msg,10,1) WITH NOWAIT
END
WHILE @@FETCH_STATUS = 0 AND @Nochmal = 1
BEGIN
SELECT @msg = convert(char(25),getdate(),113)+': Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%, scan density currently ' + RTRIM(CONVERT(varchar(15),@Dens)) + '%.'
RAISERROR(@msg,10,1) WITH NOWAIT
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ') '--WITH NO_INFOMSGS'
IF @MaxStart > GETDATE() EXEC (@execstr)
ELSE BEGIN
SELECT @msg = convert(char(25),getdate(),113)+': This Index will not be defragmented, time is out... '
RAISERROR(@msg,10,1) WITH NOWAIT
END
-- update the fraglist: delete the old values
DELETE from management.dbo.fraglist WHERE ObjectName = @tablename
-- Do the showcontig of all indexes of the table
INSERT INTO management.dbo.fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
IF GETDATE() > @MaxStart BEGIN
SELECT @Nochmal = 0
SELECT @msg = convert(char(25),getdate(),113)+': sp_index_defrag: Time for Defragmenting ist out!'
RAISERROR(@msg,10,1) WITH NOWAIT
EXEC master.dbo.xp_smtp_sendmail
@FROM= N'SQLAgent.sql1-server@xxx.de',
@FROM_NAME= N'Automagically SQL',
@TO = N'DBAdmin@xxx.de',
@replyto = N'ORGIT@xxx.de',
@cc= N'',
@BCC= N'',
@priority= N'HIGH',
@subject= N'Index-Optimisation done, but reached time limit',
@message= N'Index-Optimisation done, but reached time limit, therefore not all Indexes defragmented!!!',
@messagefile= N'',
@type= N'text/plain',
@attachment= N'',
@attachments= N'',
@codepage= 0,
@server = N'mail-cluster01.xxx.de'
END
SELECT @msg = convert(char(25),getdate(),113)+': @Nochmal: ' + LTRIM(STR(@Nochmal)) + '. '
RAISERROR(@msg,10,1) WITH NOWAIT
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @Dens
END
IF @@FETCH_STATUS 0 BEGIN
SELECT @msg = convert(char(25),getdate(),113)+': sp_index_defrag: No more Indexes to defragment... '
RAISERROR(@msg,10,1) WITH NOWAIT
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
--DROP TABLE management.dbo.fraglist
SELECT @msg = convert(char(25),getdate(),113)+': sp_index_defrag: End of Procedure... '
RAISERROR(@msg,10,1) WITH NOWAIT
SELECT @msg = '####################################################################################
'
RAISERROR(@msg,10,1) WITH NOWAIT
GO
Best regards
karl
January 4, 2006 at 3:29 am
Try script from BOL, which can be also found on msdn http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_46cn.asp
E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply