Solution:
User Configurable Variables:
@Debug int = 1 will only show the Indexes with a Fragmentation % of greater than @FragPercent value and not
execute the Alter Index command.
@Debug int = 0 will execute Alter Index command(s) and thus defrag the Index(s)
@FragPercent int = 25 - alter index if fragmentation is above 25%
@PageCount int = 10000 - alter index if Index Page count is above 10000
T-SQL to include in a SQL Agent Job:
Set NoCount On
Declare @Debug int = 0, @FragPercent int = 25, @PageCount int = 10000
Declare @sql nvarchar(1000), @tblName nvarchar(100), @IndName nvarchar(250)
Create Table #TMP (TableName varchar(100), [PageCount] int, IndexName varchar(250), FragmentationPercent int)
--Log All (used for determining ideal frequency of alter index running - analyze this data over time)
Insert into [OPS].[dbo].[LOG_ALTER_INDEX] (TableName, PageCount, IndexName, FragmentationPercent, InsertedDate)
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
indexstats.page_count,
ind.name AS IndexName,
--indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent--, *
, getdate()
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE OBJECT_NAME(ind.OBJECT_ID)
like 'SEARCH%'
ORDER BY indexstats.avg_fragmentation_in_percent DESC
Insert into #TMP
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
indexstats.page_count,
ind.name AS IndexName,
--indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent--, *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > @FragPercent --Fragmentation %
and indexstats.page_count > @PageCount --PageCount
and OBJECT_NAME(ind.OBJECT_ID)
like 'SEARCH%'
ORDER BY indexstats.avg_fragmentation_in_percent DESC
If @Debug = 1 Begin Select * from #TMP; Drop Table #TMP; Return End
While Exists (Select top 1 * from #TMP)
Begin
Select top 1 @tblName = tablename, @IndName = IndexName from #TMP
--Print 'Started: ' + @tblName + ' - ' + @IndName
Set @SQL = 'ALTER INDEX [' + @IndName + '] ON [dbo].[' + @tblName + ']
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)'
--Print 'Completed: ' + @tblName + ' - ' + @IndName
Delete from #TMP where TableName = @tblName and IndexName = @IndName
EXEC sp_executesql @SQL
End
Drop Table #TMP;
Image #1:
Logging Table:
CREATE TABLE [dbo].[LOG_ALTER_INDEX](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](100) NULL,
[PageCount] [int] NULL,
[IndexName] [varchar](250) NULL,
[FragmentationPercent] [int] NULL,
[InsertedDate] [datetime] NULL,
CONSTRAINT [PK_LOG_ALTER_INDEX] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
References:
Why, when and how to rebuild and reorganize SQL Server indexes
|
|
|
|
|
|