My photo
Auckland, New Zealand
Smurf sized geeky person with a penchant for IT, gaming, music and books. Half of industrial duo 'the craze jones'. Loves data, learning new things, teaching new things and being enthusiastic.

Thursday, 30 July 2009

Yay - It works! Rebuild/Reorganize all indexes in one go - woo hoo

/*Version 1 of my script to fix all fragged indexes on a database */

/*Must be logged in as [sa] for this to work, if not, you will get permissions errors*/

/*You should be able to copy and paste this script in its entirity to your SQL 2005 or higher query window and run it without altering anything other than the USE .*/

/*Perform a 'USE ' to select the database in which to run the script.*/

USE AdventureWorks
GO

-- Make sure temp table doesn't exist
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_fraglist]') AND type in (N'U'))
DROP TABLE [dbo].[tmp_fraglist]
GO

-- Declare variables
SET NOCOUNT ON
DECLARE @tablename NVARCHAR (255)
DECLARE @indexname NVARCHAR (255)
DECLARE @indextype NVARCHAR (255)
DECLARE @execstr NVARCHAR (255)
DECLARE @pagecounts INT
DECLARE @frag DECIMAL
DECLARE @reorgFragSize DECIMAL
DECLARE @rebldFragSize DECIMAL
DECLARE @numrecords INT
DECLARE @rowcount INT
DECLARE @errorMessage NVARCHAR (max)

-- Set the fragmentation variables for reorganize and rebuild
SELECT @reorgFragSize = 5.0
SELECT @rebldFragSize = 30.0

-- Create the temp table to store the fragged indexes
CREATE TABLE tmp_fraglist (
RowID INT IDENTITY(1,1),
ObjectName NVARCHAR (255),
IndexName NVARCHAR (255),
IndexType NVARCHAR (128),
PageCounts INT,
AvgPageFragmentation DECIMAL
)
-- Get a snapshot of current defrag state of affairs before we begin
BEGIN
SELECT '['+SCHEMA_NAME(TB.schema_id)+'].['+ TB.name +']' AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sys.indexes SI
ON DPS.OBJECT_ID = SI.OBJECT_ID AND DPS.INDEX_ID = SI.index_id
INNER JOIN sys.tables TB
ON DPS.object_id = TB.object_id
WHERE index_type_desc <> 'HEAP'
AND avg_fragmentation_in_percent >= @reorgFragSize
ORDER BY TableName
END


-- Populate the temp table with table fragmentation info
INSERT INTO tmp_fraglist (ObjectName, IndexName, IndexType, AvgPageFragmentation, PageCounts)
SELECT '['+SCHEMA_NAME(TB.schema_id)+'].['+ TB.name +']' AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sys.indexes SI
ON DPS.OBJECT_ID = SI.OBJECT_ID AND DPS.INDEX_ID = SI.index_id
INNER JOIN sys.tables TB
ON DPS.object_id = TB.object_id
WHERE index_type_desc <> 'HEAP'

AND avg_fragmentation_in_percent >= @reorgFragSize
SET @numrecords = @@ROWCOUNT
SET @rowcount = 1

PRINT '@numrecords = ' + convert(nvarchar,@numrecords)
-----------------------------------------------
-- loop through the records in the temp table
-- and reorganize or rebuild where necessary
-----------------------------------------------
WHILE @rowcount <= @numrecords
BEGIN
SELECT @tablename = ObjectName,
@indexname = IndexName,
@indextype = IndexType,
@frag = AvgPageFragmentation,
@pagecounts = PageCounts
FROM tmp_fraglist
WHERE RowID = @rowcount

------------------------------------------------------
-- Reorganize if fragmentation is between 5 & 30%
-- Rebuild if fragmentation is greater than 30%
------------------------------------------------------
BEGIN TRY
IF @frag >= @reorgFragSize AND @frag <= @rebldFragSize
BEGIN
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REORGANIZE'
PRINT @execstr
EXEC (@execstr)
PRINT 'REORGANIZED index ' + @indexname
END
ELSE IF @frag > @rebldFragSize
BEGIN
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REBUILD WITH (ONLINE=ON)'
PRINT @execstr
EXEC (@execstr)
PRINT 'REBUILT index ' + @indexname
END
END TRY
BEGIN CATCH
SET @errorMessage = (SELECT ERROR_MESSAGE() AS ErrorMessage)
PRINT 'Error number: ' + convert(nvarchar,@@ERROR)+ ' : ' + @errorMessage
END CATCH
SET @rowcount = @rowcount + 1
END

-------------------------------------------------------
-- snapshot of the defrag status after rebuilding.
-- gets all indexes regardless of AvgPageFragmentation
-- as they may be below 5% after rebuild/reorganize
-------------------------------------------------------
BEGIN
SELECT '['+SCHEMA_NAME(TB.schema_id)+'].['+ TB.name +']' AS TableName,
SI.NAME AS IndexName,
INDEX_TYPE_DESC AS IndexType,
AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') DPS
INNER JOIN sys.indexes SI
ON DPS.OBJECT_ID = SI.OBJECT_ID AND DPS.INDEX_ID = SI.index_id
INNER JOIN sys.tables TB
ON DPS.object_id = TB.object_id
WHERE index_type_desc <> 'HEAP'
ORDER BY TableName
END

-- Delete the temporary table
DROP TABLE tmp_fraglist
GO

4 comments:

Manda QoP said...

I will be tweaking, tidying, adding more features etc.. now that I've got the thing running sweetly.

Manda QoP said...

Oh, and the formatting should be fine once you copy and paste to SSMS.

Manda QoP said...

From BOL:

Requires the following permissions:

CONTROL permission on the specified object within the database.
VIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id=NULL.
VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL.
Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. Also, when the database wildcard @database_id=NULL is specified, the database is omitted.

Manda QoP said...

Changes coming:

1. As well as printing error messages on screen, send them to a log file.
2. Rebuild with online = on is apparently only available in enterprise edition - need to investigate this one. If this is the case, will need to use this script to create an index defrag script that a DBA can run when it is okay to take the DB offline.
3. Code that defrags indexes is being printed on screen, also print this to a file so that it's available to run at a later stage if required.