--© 2019 | ByrdNest Consulting
-- ensure a USE <databasename> statement has been executed first.
--USE <Database>
GO
/****************************************************************************************
Index Rebuild (defrag) script with logic from Jeff Moden and
additional logic to change fillfactor as needed
Designed to work with SS2012 and later, Enterprise Edition and
Developer Edition. If you are using Standard Edition, you
will need to modify the dynamic SQL and remove ONLINE = ON
This script was created to rebuild clustered and non-clustered
indexes with average fragmentation > 1.2%. It picks the top
15 (configurable) worse average fragmented indexes for an index
rebuild and it also varies each index fill factor (not heaps or
partitioned tables) to determine a "near optimum" value for
existing conditions. Once a fill factor value is determined, it
is fixed for each succeeding execution of this script. If the
fill factor value has not changed in last 90 days (configurable),
it is again put in the queue for finding the best fill factor
(rationale for this logic is that data skew and calling patterns
from applications may change over time).
If a table and its indexes are partitioned, this script rebuilds the
appropriate index partition with no adjustment to the fill factor.
This script should be executed from a SQL Agent job that runs daily
-- recommend time when server is least active. It also depends on
a table (created by this script first time run) to store index
parametrics for the fill factor determination.
You may alter this code for your own *non-commercial* purposes. You may
republish altered code as long as you include this copyright and give
due credit.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
Created By Comments
20190424 Mike Byrd Created
20190513 Mike Byrd Added additional data columns to
AgentIndexRebuilds table
20190604 Mike Byrd Added additional logic for setting FixFillFactor
20190616 Mike Byrd Revised FillFactor logic
20190718 Mike Byrd Added logic to get bad page splits (thanks to Jonathan Kehayias)
https://www.sqlskills.com/blogs/jonathan/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time/
****************************************************************************************/
SET QUOTED_IDENTIFIER OFF
GO
--get Database name
DECLARE @Database SYSNAME = (SELECT DB_NAME())
/********************************************************************
code setup for Always On Primary Node; comment out next 4 statements
if not an Always On Node
**********************************************************************/
-- DECLARE @preferredReplica INT
-- SET @preferredReplica
-- = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(@Database))
-- IF (@preferredReplica = 0)
BEGIN
--define Admin schema if not exists
IF NOT EXISTS (SELECT 1 from sys.schemas WHERE [name] = 'Admin')
EXEC sp_executesql N'CREATE SCHEMA [Admin] AUTHORIZATION [dbo]'
--define Admin.AgentIndexRebuilds if not exists
IF OBJECT_ID(N'Admin.AgentIndexRebuilds') IS NULL
EXEC sp_executesql N'
SET ANSI_NULLS ON
--GO
SET QUOTED_IDENTIFIER ON
--GO
CREATE TABLE [Admin].AgentIndexRebuilds(
ID INT IDENTITY(1,1) NOT NULL,
CREATEDATE DATETIME NOT NULL,
DBName SYSNAME NOT NULL,
SchemaName SYSNAME NOT NULL,
TableName SYSNAME NOT NULL,
IndexName SYSNAME NOT NULL,
PartitionNum INT NOT NULL,
Current_Fragmentation FLOAT NOT NULL,
New_Fragmentation FLOAT NULL,
PageSplitForIndex BIGINT NULL,
BadPageSplits BIGINT NULL,
New_PageSplitForIndex BIGINT NULL,
PageAllocationCausedByPageSplit BIGINT NULL,
New_PageAllocationCausedByPageSplit BIGINT NULL,
[FillFactor] INT NULL,
[Object_ID] INT NULL,
Index_ID INT NULL,
Page_Count BIGINT NULL,
Record_Count BIGINT NULL,
Forwarded_Record_Count BIGINT NULL,
New_Forwarded_Record_Count BIGINT NULL,
LagDays INT NULL,
FixFillFactor INT NULL,
DelFlag INT NULL,
CONSTRAINT PK_AgentIndexRebuilds PRIMARY KEY NONCLUSTERED
(ID ASC)
WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF
,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,DATA_COMPRESSION=ROW))
--GO
CREATE UNIQUE CLUSTERED INDEX CIX_AgentIndexRebuilds_CreateDateID
ON [Admin].AgentIndexRebuilds (CREATEDATE ASC,ID ASC)
WITH ( FILLFACTOR = 94, DATA_COMPRESSION = ROW)
--GO
ALTER TABLE Admin.AgentIndexRebuilds
ADD DEFAULT (getdate()) FOR CREATEDATE'
END
GO
--Set Configuration parameters
DECLARE @RedoPeriod INT = 90 --Days
DECLARE @TopWorkCount INT = 15 --Specify how large result
-- set for Work_to_Do
-- --get current database name
DECLARE @Database SYSNAME = (SELECT DB_NAME())
/********************************************************************
code setup for Always On Primary Node; comment out next 4 statements
if not an Always On Node
**********************************************************************/
-- DECLARE @preferredReplica INT
-- SET @preferredReplica
-- = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(@Database))
-- IF (@preferredReplica = 0)
BEGIN
DECLARE @Date DATETIME = GETDATE()
DECLARE @RowCount INT = 0
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @partitioncount BIGINT
DECLARE @schemaname SYSNAME
DECLARE @objectname SYSNAME
DECLARE @indexname SYSNAME
DECLARE @partitionnum BIGINT
DECLARE @partitions BIGINT
DECLARE @frag FLOAT
DECLARE @FillFactor INT
DECLARE @OldFillFactor INT
DECLARE @FixFillFactor INT
DECLARE @LagDate INT
DECLARE @NewFrag FLOAT
DECLARE @NewPageSplitForIndex BIGINT
DECLARE @NewPageAllocationCausedByPageSplit BIGINT
DECLARE @PageCount BIGINT
DECLARE @RecordCount BIGINT
DECLARE @ForwardRecordCount BIGINT
DECLARE @NewForwardRecordCount BIGINT
DECLARE @command NVARCHAR(4000)
DECLARE @Msg VARCHAR(256)
DECLARE @PartitionFlag BIT = 0
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON; --needed for XML ops in query below
-- ensure the temporary work table does not exist
IF OBJECT_ID(N'tempdb..#work_to_do') IS NOT NULL DROP TABLE #work_to_do
-- get worse avg_fragmentation indexs (TOP @TopWorkCount)
-- use Row_Number() below to ensure no duplicates
SELECT TOP (@TopWorkCount) *
INTO #work_to_do
FROM ( SELECT
ps.object_id objectid,
ps.index_id indexid,
o.[name] TableName,
i.[name] IndexName,
ps.partition_number partitionnum,
ps.avg_fragmentation_in_percent frag,
ios.LEAF_ALLOCATION_COUNT PAGE_SPLIT_FOR_INDEX,
tab.split_count BadPageSplit,
ios.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT,
CASE WHEN i.Fill_Factor = 0 THEN 100
ELSE i.Fill_Factor END Fill_Factor,
ps.page_count,
ps.record_count,
ps.forwarded_record_count,
ps.avg_page_space_used_in_percent,
NULL New_Frag,
NULL New_PageSplitForInde,
NULL New_PageAllocationCausedByPageSplit,
NULL New_forwarded_record_count,
0 [Redo_Flag],
ROW_NUMBER() OVER (PARTITION BY ps.object_id,ps.index_id,ps.partition_number,tab.split_count ORDER BY tab.split_count DESC) [RowNumber]
-- --get data for all tables/indexes
-- SAMPLED gives same avg fragmentation as DETAILED and is much faster
FROM sys.dm_db_index_physical_stats (DB_ID(@Database),NULL,NULL,NULL,'SAMPLED') ps
JOIN sys.dm_db_index_operational_stats(DB_ID(@Database),NULL,NULL,NULL) ios
ON ios.index_id = ps.index_id
AND ios.[object_id] = ps.[object_id]
AND ios.partition_number = ps.partition_number
AND ps.index_level = 0
JOIN sys.indexes i
ON i.index_id = ps.index_id
AND i.[object_id] = ps.[object_id]
JOIN sys.objects o
ON o.[object_id] = i.[object_id]
JOIN sys.partitions p
ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
LEFT JOIN sys.allocation_units au
ON au.container_id = p.[partition_id]
LEFT JOIN (SELECT
n.value('(value)[1]', 'bigint') AS alloc_unit_id,
n.value('(@count)[1]', 'bigint') AS split_count
FROM (SELECT CAST(target_data as XML) target_data
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = 'SQLskills_TrackPageSplits'
AND t.target_name = 'histogram' ) as tab
CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n) ) AS tab
ON tab.alloc_unit_id = au.allocation_unit_id
WHERE i.index_id > 0
AND o.[type] = 'U'
AND ps.avg_fragmentation_in_percent > 1.20 --this is rebuild condition
AND ps.index_level = 0 ) sub
/*******************************************************************
The ORDER BY below looks at max avg_frag and then alternates the
next day with indexes with max page splits.
*********************************************************************/
WHERE sub.RowNumber = 1
ORDER BY CASE WHEN DAY(getdate()) % 2 = 1
THEN sub.frag
ELSE sub.BadPageSplit END DESC
/************************************************************************
Go back and find oldest index (>@RedoPeriod) with @FixFillFactor
and add it to #work_to_do
(to keep index fill factors from getting "stale").
***********************************************************************/
IF OBJECT_ID(N'tempdb..#Temp2') IS NOT NULL DROP TABLE #Temp2
SELECT TOP(1) CREATEDATE,ID,DBName,SchemaName,TableName,IndexName
,PartitionNum,Current_Fragmentation,New_Fragmentation
,PageSplitForIndex,New_PageSplitForIndex
,PageAllocationCausedByPageSplit
,New_PageAllocationCausedByPageSplit
,[FillFactor],[Object_ID],Index_ID,page_count
,record_count,forwarded_record_count
,New_forwarded_record_count,LagDays,FixFillFactor
INTO #Temp2
FROM [Admin].AgentIndexRebuilds r
WHERE r.CREATEDATE <= DATEADD(dd,-@RedoPeriod,GETDATE())
AND r.DBName = @Database
AND r.FixFillFactor IS NOT NULL
AND r.DelFlag <> 1
AND NOT EXISTS (SELECT 1 FROM [Admin].AgentIndexRebuilds r2
WHERE r2.DBName = @Database
AND r2.[Object_ID] = r.[Object_ID]
AND r2.Index_ID = r.Index_ID
AND r2.PartitionNum = r.PartitionNum
AND r2.ID > r.ID)
--don't get partitioned tables (no adjusting fill factor)
AND NOT EXISTS (SELECT 1 FROM sys.partitions p
WHERE p.object_id = r.object_id
AND p.index_id = r.index_id
AND p.partition_number > 1)
ORDER BY ID DESC, CreateDate DESC
SET @RowCount = @@ROWCOUNT
/**********************************************************************
Go back and recalculate FillFactor for oldest Table/Index
in Admin.AgentIndexRebuilds
***********************************************************************/
IF @RowCount = 1
BEGIN
UPDATE #Temp2
-- start pertubation cycle over again
SET [FillFactor] = CASE WHEN Index_ID > 1
AND FixFillFactor > 90
THEN 98
WHEN Index_ID > 1
AND FixFillFactor > 80
THEN 94
WHEN Index_ID > 1
AND FixFillFactor >=70
THEN 90
ELSE 100 END, --reset CI back to 100
FixFillFactor = NULL --reset FixFillFactor so that
-- regression can begin
/**********************************************************************
Reset fixfillfactor from previous passes (need to reset it for
all rows with Object_ID, Index_ID, & PartitionNum
***********************************************************************/
UPDATE r
SET FixFillFactor = NULL
FROM [Admin].AgentIndexRebuilds r
JOIN #Temp2 t
ON t.[Object_ID] = r.[Object_ID]
AND t.Index_ID = r.Index_ID
AND t.PartitionNum = r.PartitionNum
AND (r.DelFlag IS NULL OR r.DelFlag = 0)
WHERE r.DBName = @Database;
--add new row to start regression
INSERT INTO #work_to_do
(objectid,indexid,TableName,IndexName,partitionnum
,frag,PAGE_SPLIT_FOR_INDEX
,PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT,Fill_Factor
,page_count,record_count,forwarded_record_count
,avg_page_space_used_in_percent,New_Frag
,New_PageSplitForIndex
,New_PageAllocationCausedByPageSplit
,New_forwarded_record_count,Redo_Flag)
SELECT [OBJECT_ID], Index_ID, TableName,IndexName
,PartitionNum,Current_Fragmentation
,PageSplitForIndex,PageAllocationCausedByPageSplit
,[FillFactor],page_count,record_count
,forwarded_record_count,NULL,NULL
,NULL,NULL,NULL,@RowCount [Redo_Flag]
FROM #Temp2
END
-- Declare the cursor for the list of partitions to be processed.
IF EXISTS (SELECT 1 FROM #work_to_do)
BEGIN
DECLARE [workcursor] CURSOR FOR
SELECT DISTINCT w.objectid, w.indexid
, w.partitionnum, w.frag,w.Fill_Factor
,w.TableName, w.IndexName
,DATEDIFF(dd,sub.CreateDate,GETDATE()) LagDate
,Redo_Flag
FROM #work_to_do w
JOIN sys.indexes i
ON i.object_id = w.objectid
AND i.index_id = w.indexid
LEFT JOIN (SELECT TableName, IndexName, PartitionNum
, MAX(CreateDate) CreateDate
FROM [Admin].AgentIndexRebuilds r
WHERE r.DBName = @Database
GROUP BY TableName,IndexName,PartitionNum) sub
ON sub.TableName = OBJECT_NAME(w.ObjectID)
AND sub.IndexName = i.[name]
AND sub.PartitionNum = w.partitionnum
ORDER BY w.Frag DESC
-- Open the cursor.
OPEN [workcursor]
-- Loop through the [workcursor].
FETCH NEXT FROM [workcursor]
INTO @objectid, @indexid, @partitionnum, @frag, @FillFactor
,@objectname,@indexname,@LagDate,@RowCount
WHILE @@FETCH_STATUS = 0
BEGIN
IF OBJECT_ID(N'tempdb..#Temp3') IS NOT NULL DROP TABLE #Temp3
SELECT @schemaname = s.[name]
FROM sys.objects AS o
JOIN sys.schemas as s
ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid
/**********************************************************************
Cannot reset fillfactor if table is partitioned, but can rebuild
the specified partition number
***********************************************************************/
IF @partitionnum > 1 OR
EXISTS (SELECT 1 FROM sys.partitions p
WHERE p.object_id = @objectid
AND p.Index_ID = @indexid
AND p.partition_number > 1)
SET @PartitionFlag = 1
ELSE
SET @PartitionFlag = 0
SET @OldFillFactor = @FillFactor
SET @FixFillFactor =
(SELECT FixFillFactor
FROM [Admin].[AgentIndexRebuilds] air
WHERE air1.DBName = @Database
AND air1.ID = (SELECT MAX(ID)
FROM [Admin].[AgentIndexRebuilds] air2
WHERE air2.DBName = @Database
AND air2.[Object_ID] = @objectid
AND air2.Index_ID = @indexid
AND air2.PartitionNum = @partitionnum))
/**********************************************************************
This is the logic for changing fill factor per index
Clustered Indexes are perturbed by decrementing the current fill
factor by 1 and nonclustered indexes fill factors are decremented
by one or two depending on the length of time since the index was
last rebuilt. Code is there to ensure the perturbed fill factor
is never less than 70%. This is an arbitrary number I set and
can be changed if required.
***********************************************************************/
IF @FixFillFactor IS NULL
BEGIN
SET @FillFactor = CASE WHEN @RowCount = 1
THEN @FillFactor --to catch redo index
--clustered index, only decrement by 1
--if already 100 then ratchet down
WHEN @indexid = 1 and
@LagDate IS NULL
AND @FillFactor = 100
THEN 99
WHEN @indexid = 1 AND
@LagDate IS NULL
THEN 100
WHEN @indexid = 1 AND
@LagDate <30
THEN @FillFactor -1
--nonclustered indexes,
-- decrement fill factor
-- depending on Lag days.
----if already 100 then ratchet down
WHEN @indexid > 1 AND
@LagDate IS NULL AND
@FillFactor = 100
THEN 98
WHEN @indexid > 1 AND
@LagDate IS NULL
THEN 100
WHEN @indexid > 1 AND
@LagDate < 14
THEN @FillFactor -2
WHEN @indexid > 1 AND
@LagDate >= 14
THEN @FillFactor -1
ELSE @FillFactor END
-- never let FillFactor get to less than 70
IF @FillFactor < 70
BEGIN
SET @FillFactor = 70
PRINT 'FillFactor adjusted back to 70.'
UPDATE [Admin].AgentIndexRebuilds
SET FixFillFactor = @FillFactor
WHERE DBName = @Database
AND Object_ID = @objectid
AND Index_ID = @indexid
AND PartitionNum = @partitionnum
END
END
ELSE
SET @FillFactor = @FixFillFactor
/**********************************************************
Index is not partitioned
***********************************************************/
IF @PartitionFlag = 0
BEGIN
SET @command = N'SET QUOTED_IDENTIFIER ON
ALTER INDEX ' + @indexname +' ON [' + @schemaname +
N'].[' + @objectname + N'] REBUILD WITH (ONLINE = ON,'+
' DATA_COMPRESSION = ROW,MAXDOP = 1,FILLFACTOR = '+
CONVERT(NVARCHAR(5),@FillFactor) + ')'
END
/**********************************************************
IF Index is partitioned, rebuild, but don't play with fill factor
***********************************************************/
IF @PartitionFlag = 1
BEGIN
SET @FillFactor = @OldFillFactor
SET @command = N'SET QUOTED_IDENTIFIER ON
ALTER INDEX ' + @indexname +' ON [' + @schemaname
+ N'].[' + @objectname + N'] REBUILD PARTITION = '
+ CONVERT(VARCHAR(25),@PartitionNum) +
N' WITH (ONLINE = ON, DATA_COMPRESSION = ROW,MAXDOP = 1)'
END
EXEC sys.sp_executesql @command
PRINT N'Executed ' + @command
/**********************************************************
Get new fragmentation after rebuild
***********************************************************/
SELECT ps.avg_fragmentation_in_percent
INTO #Temp3
FROM #work_to_do w
JOIN sys.dm_db_index_physical_stats (DB_ID(@Database),@objectid
, @indexid,@partitionnum
,'SAMPLED') ps
ON ps.index_id = w.indexid
AND ps.object_id = w.objectid
AND ps.partition_number = w.partitionnum
AND ps.index_level = 0
WHERE w.indexid = @indexid
AND w.objectid = @objectid
AND w.partitionnum = @partitionnum
AND ps.index_level = 0
/**********************************************************************
If new frag > old frag, go back 1% fill factor, rebuild index,
and set FixFillFactor (for non-partioned indexes only)
***********************************************************************/
IF EXISTS (SELECT 1 FROM #Temp3
WHERE avg_fragmentation_in_percent > @frag
AND @OldFillFactor <> @FillFactor
AND @RowCount = 0)
AND @PartitionFlag = 0
BEGIN
PRINT 'Setting @FixFillFactor'
SET @FillFactor = @FillFactor + 1
IF @FillFactor > 100 SET @FillFactor = 100
SET @FixFillFactor = @FillFactor
--archive out old data for that table/index/partionnum
UPDATE r
SET DelFlag = 1
FROM [Admin].AgentIndexRebuilds r
JOIN #Temp2 t2
ON t2.DBName = r.DBName
AND t2.Object_ID = r.Object_ID
AND t2.Index_ID = r.Index_ID
AND t2.PartitionNum = r.PartitionNum
WHERE r.DBName = @Database
AND r.Object_ID = @objectid
AND r.Index_ID = @indexid
AND r.PartitionNum = @partitionnum
AND r.ID <= t2.ID
AND r.DelFlag <> 1
SET @command = N'SET QUOTED_IDENTIFIER ON
ALTER INDEX ' + @indexname + N' ON [' + @schemaname
+ '].[' + @objectname + N'] REBUILD '
+ N'WITH (ONLINE=ON,DATA_COMPRESSION=ROW,MAXDOP=1,' +
N'FILLFACTOR = '+CONVERT(NVARCHAR(5),@FillFactor) +
')'
EXEC sys.sp_executesql @command
PRINT N'Executed ' + @command
END
--insert results into history table (AgentIndexRebuilds)
INSERT [Admin].AgentIndexRebuilds (CREATEDATE, DBName
, SchemaName, TableName, IndexName, PartitionNum
, Current_Fragmentation, New_fragmentation
, PageSplitForIndex, BadPageSplits, New_PageSplitForIndex
, PageAllocationCausedByPageSplit
, New_PageAllocationCausedByPageSplit, [FillFactor]
, [Object_ID], Index_ID
, page_count, record_count, forwarded_record_count
, New_forwarded_record_count, LagDays,FixFillFactor,DelFlag)
SELECT @DATE,@Database,@schemaname,@objectname
,@indexname,@partitionnum,@frag
, ps.avg_fragmentation_in_percent
,w.PAGE_SPLIT_FOR_INDEX,w.BadPageSplit,ios.LEAF_ALLOCATION_COUNT
,w.PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
,ios.NONLEAF_ALLOCATION_COUNT,@FillFactor,w.objectid
,w.indexid,w.page_count,w.record_count
,w.forwarded_record_count,ps.forwarded_record_count
,@LagDate,@FixFillFactor,1
FROM #work_to_do w
JOIN sys.dm_db_index_physical_stats
(DB_ID(@Database),@objectid,@indexid,@partitionnum
,'SAMPLED') ps
ON ps.index_id = w.indexid
AND ps.object_id = w.objectid
AND ps.partition_number = w.partitionnum
AND ps.index_level = 0
JOIN sys.dm_db_index_operational_stats
(DB_ID(@Database),@objectid,@indexid,@partitionnum) ios
ON ios.index_id = ps.index_id
AND ios.object_id = ps.object_id
AND ios.partition_number = ps.partition_number
WHERE w.indexid = @indexid
AND w.objectid = @objectid
AND w.partitionnum = @partitionnum
AND ps.index_level = 0
SET @PartitionFlag = 0
FETCH NEXT
FROM [workcursor]
INTO @objectid, @indexid, @partitionnum, @frag
,@FillFactor,@objectname,@indexname,@LagDate,@RowCount
END
-- Close and deallocate the cursor.
CLOSE [workcursor]
DEALLOCATE [workcursor]
--clean up
IF OBJECT_ID(N'tempdb..#Temp2') IS NOT NULL
DROP TABLE #Temp2
IF OBJECT_ID(N'tempdb..#Temp3') IS NOT NULL
DROP TABLE #Temp3
IF OBJECT_ID(N'tempdb..#TablesWithLOBs') IS NOT NULL
DROP TABLE #TablesWithLOBs
END
IF OBJECT_ID(N'tempdb..#work_to_do') IS NOT NULL DROP TABLE #work_to_do
--Data retention
DELETE [Admin].AgentIndexRebuilds
WHERE CreateDate < DATEADD(yy,-3,GETDATE())
END
GO