Technical Article

Rebuild index selectively

,

check Index fragmentation and rebuild them accordingly:

exec sp_MSforeachdb 'use [?]; exec dbo.sp_FilteredIndexRebuild @logicalfragmentation=30,@physicalfragmentation=60,@recordcount=100'

-- Example here is to create SP in master and you can call it for each database.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[FilteredIndexRebuild]    Script Date: 10/5/2015 10:32:24 AM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_FilteredIndexRebuild]
@logicalFragmentation FLOAT,
@PhysicalFragmentation FLOAT,
@RecordCount BIGINT
AS

SELECT ps.object_id, object_name(ps.object_id) AS Tablename, ps.index_id, idx.name AS idxname
INTO #idx
FROM sys.dm_db_index_physical_stats(db_ID(), NULL, NULL, NULL, 'detailed') ps
JOIN sys.indexes idx
ON ps.object_id=idx.object_id AND ps.index_id=idx.index_id
WHERE index_level=0
AND ps.index_id BETWEEN 1 AND 255
AND avg_fragmentation_in_percent>@logicalFragmentation
AND avg_page_space_used_in_percent<@PhysicalFragmentation
AND record_count>@RecordCount

DECLARE @tablename VARCHAR(255)
DECLARE @idxname VARCHAR(255)
DECLARE @table_schema VARCHAR(255)
DECLARE @cmd NVARCHAR(500)

DECLARE IndexListCursor cursor fast_forward FOR
SELECT i.Tablename, i.idxname, s.TABLE_SCHEMA FROM
#idx i JOIN INFORMATION_SCHEMA.TABLES s
ON i.Tablename=s.TABLE_NAME

OPEN IndexListCursor

FETCH NEXT FROM IndexListCursor INTO @tablename, @idxname, @table_schema
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'ALTER INDEX ' + @idxname + ' on '+@table_schema+'.'+@tablename + ' rebuild'
PRINT @cmd
EXEC (@cmd)

FETCH NEXT FROM IndexListCursor INTO @tablename, @idxname, @table_schema
END
CLOSE IndexListCursor
DEALLOCATE IndexListCursor

DROP TABLE #idx

-- example: to rebuild index if logicalFragmentation is greater than 30, physicalFragmentation is greater than 60 and recordrow count is greater than 100 (rows)

exec sp_MSforeachdb 'use [?]; exec dbo.sp_FilteredIndexRebuild @logicalfragmentation=30,@physicalfragmentation=60,@recordcount=100'

Rate

2.33 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

2.33 (3)

You rated this post out of 5. Change rating