Defrag Indexes

Fragmented Indexes, while not a common thought among DBAs and developers, can be one bottleneck in your DB performance. MS SQL Server includes three DBCC functions to help the DBA find these fragmented Indexes, and fix them:
DBCC SHOWCONTIG
DBCC INDEXDEGRAG
DBCC REINDEX
The following script takes advantage of these to reindex tables in the current DB.
Much of this code is rewritten from a few similar T-SQL statements found on other sites (I will add links to them later when I find them again) with a few modifications/additions of my own.
The one update I would make (and will make the next time I work with this code) would be to convert this to a Stored Procedure where the parameters are passed in instead of hard-coded.
The following lines (which are found in the T-SQL below) are the only lines you would want to modify for your own scenario:
SELECT @maxfrag = 10.0
    (Allowing more fragmentation is generally better for massive updates, less fragmentation is better for more reads. You’ll want to test to find the fragmentation level that works best for your own environment)
SET @Server = ‘TESTSERVER’
    (I use this for reporting purposes. Generally I am defragmenting many DBs and posting the final results into Excel. you could just as easily post the results to a table to see how quickly your indexes fragment over time)
SET @MaxCount = 10000000
    (In a production environment, I generally find that defragmenting a table of more than 10mil records takes too long and causes too much blocking, so I save those for off peak hours)
SET @MinCount = 10000
    (Fragmentation on tables of less than 10,000 records in my experience has little to no impact on performance, so I generally ignore those tables)
SELECT @execstr = ‘DBCC INDEXDEFRAG (0, ‘ + RTRIM(@tablename) + ‘,’ + RTRIM(@IndexName) + ‘)’
    (In this T-SQL this line is commented out. Using DBCC INDEXDEFRAG does a better job but causes VERY heavy transactions, which if left unchecked, can quite easily grow out of control.)
SELECT @execstr = ‘DBCC DBREINDEX (‘ + RTRIM(@tablename) + ‘,’ + RTRIM(@IndexName) + ‘, ‘ + CONVERT(varchar(15),100-@maxfrag) + ‘)’
    (In this T-SQL this line is used. Using DBCC DBReindex does not do as good a job but does not create massive transactions, which is better in many live production environments.)
The code:

SET NOCOUNT ON
DECLARE @Owner VARCHAR (128)
DECLARE @tablename VARCHAR (128)
DECLARE @IndexName VARCHAR (255)
DECLARE @RecordCount INT
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @Server VARCHAR(10)
DECLARE @MaxCount INT
DECLARE @MinCount INT
— Decide on the maximum fragmentation to allow
SELECT @maxfrag = 10.0
SET @Server = ‘TESTSERVER’
SET @MaxCount = 10000000
SET @MinCount = 10000
— Create the table
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)
— Declare and open cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
OPEN tables
— 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 #fraglist
EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)
FETCH NEXT FROM tables INTO @tablename
END
CLOSE tables
DEALLOCATE tables
— Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM #fraglist
WHERE LogicalFrag >= (@maxfrag + 5)
AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0
— Open the cursor
OPEN indexes
— loop through the indexes
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RecordCount=max(i.rows)
FROM sysindexes i
WHERE object_name(i.id)=@tablename
If (@RecordCount BETWEEN @MinCount and @MaxCount)
BEGIN
— SELECT @execstr = ‘DBCC INDEXDEFRAG (0, ‘ + RTRIM(@tablename) + ‘,’ + RTRIM(@IndexName) + ‘)’
SELECT @execstr = ‘DBCC DBREINDEX (‘ + RTRIM(@tablename) + ‘,’ + RTRIM(@IndexName) + ‘, ‘ + CONVERT(varchar(15),100-@maxfrag) + ‘)’
PRINT @execstr + ‘: – fragmentation currently ‘ + RTRIM(CONVERT(varchar(15),@frag)) + ‘%’
EXEC (@execstr)
END
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IndexName
END
CLOSE indexes
DEALLOCATE indexes
— Delete the temporary table
SELECT ‘RecordCount’ = max(i.rows), ‘Server’=@Server, t.ObjectName, ‘Frag’=avg(t.LogicalFrag), RTRIM(t.IndexName)
FROM sysindexes i, #fraglist t
WHERE(t.ObjectName = object_name(i.id)) AND (t.LogicalFrag >= @maxfrag)
GROUP BY t.ObjectName, t.IndexName
HAVING (max(i.rows)>@MinCount)
ORDER BY t.ObjectName
DROP TABLE #fraglist
GO

Advertisements

About Rodibidably

Jeff Randall is a frequent volunteer for free-thought organizations, including the Center For Inquiry – DC. Having been blogging since January 2008, he decided that a community of bloggers would be an interesting new experience (or at the very least a fun way to annoy his friends into reading his posts more frequently). Since finding out about about the existence of, and then joining, the atheist/skeptic community in 2007 he has been committed to community activism, critical thinking in all aspects of life, science, reason, and a fostering a secular society.
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s