info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Rebuild Indexes based on Fragmentation Percent
by BF (Principal Consultant; Architecture; Engineering)
2016-04-13







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