check Index fragmentation and rebuild them accordingly:
exec sp_MSforeachdb 'use [?]; exec dbo.sp_FilteredIndexRebuild @logicalfragmentation=30,@physicalfragmentation=60,@recordcount=100'
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'