/****************The Lytic Group, copyright, 2021*****************/
/****************Published February, 2021 *****************/
/*********from http://www.LyticGroup.com by Edward Heraux*****************/
/************************************************************************************************
This pair of stored procs together will allow you to defrag/reindex all the tables in a database
that you deem necessary, based on a fragmentation percentage that you pass in as a parameter.
It also keeps a "Before" and "After" history of your tables' fragmentation
every time you run it, so that you can actually report on this regular maintenance easily.
Schedule this beauty to run occasionally, and a good portion of your job of
keeping your tables streamlined and healthy gets done for you.
*******************************************************************************************/
/****************IMPORTANT WARNING!!!!!*****************/
/**********Do not run the second stored proc, uspReindexAll,
at just any old time of day on a production SQL Server!!!!!
This is an expensive stored procedure that will take ages to run
(and tie up a lot of resources) on a large database,
especially if little defragmentation has ever been done on it.
Pick the quietest, slowest part of your SQL Server's work week,
and this script can be a handy administration automation tool.
*******************************************************************/
--1. uspTableContiguity
/*This first stored procedure makes all the data from DBCC SHOWCONTIG
available so that we can populate a table with its data
*/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC dbo.uspTableContiguity
--Pass a NULL @tablename if you want to capture the contiguity stats for all tables
@tablename varchar(255)=NULL
AS
--Show contiguity info for all tables if no table name is passed in
--(The next stored proc, however, will always call this to obtain info on all tables)
IF @tablename IS NULL
BEGIN
DBCC SHOWCONTIG WITH TABLERESULTS
RETURN
END
ELSE
--otherwise find the ID of the table passed in
BEGIN
DECLARE @tableid int
SET @tableid=(SELECT id FROM sysobjects where type='u' and name = @tablename)
DBCC SHOWCONTIG (@tableid) WITH TABLERESULTS
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------End of first proc------------------------
--2. uspReindexTables
/***********************************************************************/
/*This second stored procedure grabs and looks through the data produced by
DBCC SHOWCONTIG, and REINDEXes all the tables that are above a fragmentation
percentage that you specify.
It will also create & update a table with time-stamped
records of the fragmentation of those tables before and after the REINDEXing gets done.
***********************************************************************/
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO
ALTER PROC [dbo].[uspReindexTables]
@db sysname
, @FragThreshold int --Minimum fragmentation percentage that you want
--to trigger a reindexing of that table or index.
--We recommend 30.
AS
DECLARE @ErrorText varchar(2048)
SET @FragThreshold=100-@FragThreshold
BEGIN TRAN
BEGIN TRY
/*Make sure the table is in place to store all the fragmentation data*/
IF NOT EXISTS (SELECT * FROM sysobjects where type='u' AND name='tblFragmentationInfo')
CREATE TABLE dbo.tblFragmentationInfo (
RowID int IDENTITY,
[ObjectName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectID] [int] NULL ,
[IndexName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexID] [int] NULL ,
[Level] [int] NULL ,
[Pages] [int] NULL ,
[Rows] [int] NULL ,
[MinimumRecordSize] [int] NULL ,
[MaximumRecordSize] [int] NULL ,
[AverageRecordSize] [decimal](11, 5) NULL ,
[ForwardedRecords] [int] NULL ,
[Extents] [int] NULL ,
[ExtentSwitches] [int] NULL ,
[AverageFreeBytes] [decimal](11, 5) NULL ,
[AveragePageDensity] [decimal](11, 5) NULL ,
[ScanDensity] [decimal](11, 5) NULL ,
[BestCount] [int] NULL ,
[ActualCount] [int] NULL ,
[LogicalFragmentation] [decimal](18, 0) NULL ,
[ExtentFragmentation] [decimal](11, 5) NULL ,
[SchemaID] int NULL,
[SchemaName] sysname NULL,
[CheckDate] [datetime] NULL CONSTRAINT [DF_tblFragmentationInfo_DefragDate] DEFAULT (getdate()),
[DefragDate] [datetime] NULL, --the time-stamp of when this table was reindexed during this sp
[TableSize] AS ([Pages] * 8),
[InProcess] bit CONSTRAINT DF_InProcess_Yes DEFAULT (1) --Distinguishes records added during this running of the sp
-- from records already in the table from prior executions
) ON [PRIMARY]
--(Just for testing)
--ELSE PRINT 'tblFragmentationInfo exists'
/*Dump a 'before' snapshot of current fragmentation into tblFragmentationInfo*/
INSERT INTO dbo.tblFragmentationInfo (ObjectName, ObjectId, IndexName, IndexID, [Level], Pages, [rows], MinimumRecordSize,
MaximumRecordSize, AverageRecordSize, ForwardedRecords, Extents, ExtentSwitches, AverageFreeBytes,
AveragePageDensity, ScanDensity, BestCount, ActualCount, LogicalFragmentation, ExtentFragmentation)
EXECUTE dbo.uspTableContiguity
IF @@Error<>0
BEGIN
RAISERROR ('Could not create snapshot of tables. tblFragmentationInfo was not populated.',16,1)
ROLLBACK TRAN
RETURN
END
/*Put the schema IDs in the table*/
UPDATE tblFragmentationInfo
SET SchemaName=S.name, SchemaID=S.schema_id
FROM sys.schemas S
INNER JOIN sys.all_objects A on A.Schema_id=S.schema_id
INNER JOIN tblFragmentationInfo F ON F.ObjectID=A.object_id
/*Get rid of system tables in tblFragmentationInfo, which can't be REINDEXed*/
DELETE FROM dbo.tblFragmentationInfo WHERE LEFT(ObjectName,3)='sys'
END TRY
/*Error handling block*/
BEGIN CATCH
SELECT @ErrorText='Errors INSERTing/UPDATing tblFragmentationInfo table. ErrorNum ' + ERROR_NUMBER() +': ' + ERROR_MESSAGE()
RAISERROR(@ErrorText,16,1)
ROLLBACK TRAN
RETURN
END CATCH
COMMIT
/*Create a temp table holding a list of the database's tables that are fragmented enough for concern,
but just the ones called up for this procedure*/
SELECT RowID, ObjectName, SchemaName, ScanDensity
INTO #tblFragmentedTables
FROM dbo.tblFragmentationInfo
WHERE ScanDensity<@FragThreshold AND InProcess<>0
DECLARE @rows int, @counter int, @CurrentRowID int
DECLARE @FQTableName sysname
SELECT @counter=1, @rows=COUNT(*)
FROM #tblFragmentedTables
/*(Just for testing) A quick look at which tables we're about to REINDEX*/
--SELECT 'Tables About To Be Defragged'
--SELECT * FROM #tblFragmentedTables
/*Begin a loop to defrag each table that made it into the #FragmentedTables table.
(This is SO much more efficient than a cursor!!)*/
BEGIN TRY
WHILE @counter<=@rows
BEGIN
SELECT Top 1 @CurrentRowID=RowID, @FQTableName=SchemaName + '.' + ObjectName
FROM #tblFragmentedTables
ORDER BY RowID ASC
--(Just for testing) Show which single table is about to be REINDEXed
--SELECT @CurrentRowID AS CurrentRowID, @FQTableName AS CurrentTableName
DBCC DBREINDEX(@FQTableName,'',0)
--Log that this table's been taken care of, back in tblFragmentationinfo.
UPDATE dbo.tblFragmentationInfo
SET InProcess=0
FROM dbo.tblFragmentationInfo F
INNER JOIN (SELECT Top 1 SchemaName,ObjectName FROM #tblFragmentedTables) AS Sub
ON F.SchemaName=Sub.SchemaName AND F.ObjectName=Sub.ObjectName
WHERE InProcess<>0
--Prepare for the next iteration of the loop.
DELETE #tblFragmentedTables WHERE RowID=@CurrentRowID
SET @counter=@counter+1
END
END TRY
--Error handling block
BEGIN CATCH
SELECT @ErrorText='Failed reindexing a table. ErrorNum ' + ERROR_NUMBER() +': ' + ERROR_MESSAGE()
RAISERROR(@ErrorText,16,1)
ROLLBACK TRAN
RETURN
END CATCH
/*A bunch of things to finalize the "After" info in tblFragmentationInfo, and clean up after ourselves*/
BEGIN TRAN
BEGIN TRY
/* Dump an 'after' snapshot of current fragmentation into tblFragmentationInfo */
INSERT dbo.tblFragmentationInfo (ObjectName, ObjectId, IndexName, IndexID, [Level], Pages, [rows], MinimumRecordSize,
MaximumRecordSize, AverageRecordSize, ForwardedRecords, Extents, ExtentSwitches, AverageFreeBytes,
AveragePageDensity, ScanDensity, BestCount, ActualCount, LogicalFragmentation, ExtentFragmentation)
EXECUTE dbo.uspTableContiguity
/*Make sure the 'after' picture doesn't get processed in the future
, since InProcess defaults to TRUE*/
UPDATE dbo.tblFragmentationInfo
SET InProcess=0
WHERE InProcess<>0
/*Get rid of system tables in tblFragmentationInfo, which can't be REINDEXed*/
DELETE FROM dbo.tblFragmentationInfo WHERE LEFT(ObjectName,3)='sys'
--(Just for testing) Take a look at the reindexing history to-date
--SELECT ObjectName, ScanDensity, CheckDate, DefragDate, InProcess
--FROM dbo.tblFragmentationInfo
--ORDER BY CheckDate, ObjectName
END TRY
--Error handling block
BEGIN CATCH
SELECT @ErrorText='ErrorNum ' + ERROR_NUMBER() +': ' + ERROR_MESSAGE()
RAISERROR(@ErrorText,16,1)
ROLLBACK TRAN
RETURN
END CATCH
COMMIT
--Get rid of the temp table
DROP TABLE #tblFragmentedTables
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO