Managing Index Fragmentation and Compression
For today, I recycle bits from a couple of excellent authors in the SQL community. The first script is for optimizing compression. I “borrowed” this from Paul Nielson, author of the SQL Bible. You can visit his SQL Server bible site at https://www.sqlserverbible.com/. It includes a link for sample scripts, which is where I pulled this from. I’m also including a script for defragmenting indexes, which I borrowed from https://sqlfool.com/2010/04/index-defrag-script-v4-0/. These scripts can be used together. What I am doing is on a monthly basis running the compression script to make adjustments to my compression options based on data, and then doing the defragmentation weekly.
Based on my testing and testing on several other sites, compression should be the norm rather than the exception. Unless you have heavy OLTP processing, most of the time the savings in memory, cache, and ultimately disk I/O because more data is stored in less space is greater than the CPU costs. Along the same lines, backup compression should almost always be used as it will run faster than non-compressed for both backup and restore purposes. They really got compression right in SQL 2008.
Below is the script almost as originally written, but with a couple of modifications. Paul’s version had only 1 threshold, rather than separate for page and row, which I’ve added. In his version, he uses page compression if it is more effective than row and row compression if it is more effective than page. However almost always page is better than row because it incorporates row. I’ve decided to take a slightly different approach and allow a threshold for row and another for page. Page requires more CPU processing than row, so the idea is that we only want page if the compression benefits are significantly better than row. On the other hand, row has a lower CPU cost. By default, I am using 15 and 25%. By default, I’m employing page compression if it has a benefit of 25% savings and row if it has a benefit of 15%, you can pass in different values if you want to be more conservative or aggressive.
First the script for database compression setting from Paul Nielson with my changes noted in the comments:
/****** Object: StoredProcedure [dbo].[db_compression] Script Date: 06/06/2010 21:05:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[db_compression] (
@minCompression float = .15, -- e.g. .25 for minimum of 25% compression
@minPageCompression float = .25 ,-- threshold to use page compression
@ReportModeOnly bit = 0
)
as
set nocount on
-- Paul Nielsen
-- www.SQLServerBible.com
-- March 13, 2008
/*
Mod by Bob L, June, 2010 - Receive parameters for minCompression and minPageCompression
Instead of checking to see if page or row is more effective, simply use the compression
matching the threshold. Passing in a higher page-level threshold than row eliminates
the possibility of choosing the less effective compression type. Normally page compression
will yield a higher return since it is includes row compression. However, it has performance
penalty, so the page compression threshhold should be significant - using 25% as the default.
*/
/*
sets compression for all objects and indexs in the database needing adjustment
if estimated gain is equal to or greater than mincompression parameter
then enables row or page compression whichever is greater gain
if row and page have same gain
then enables row compression
if estimated gain is less than mincompression parameter
then compression is set to none
*/
-- to do:
-- [ ] transaction error from insert...Exec sp_estimate_data_compression_savings
-- [ ] filter objects to only those eligible for compression
IF @minCompression is null SET @minCompression = .15
IF @minPageCompression is null SET @minPageCompression = .25
CREATE TABLE #ObjEst (
PK int identity not null primary key,
object_name varchar(250),
schema_name varchar(250),
index_id INT,
partition_number int,
size_with_current_compression_setting bigint,
size_with_requested_compression_setting bigint,
sample_size_with_current_compression_setting bigint,
sample_size_with_requested_compresison_setting bigint
)
CREATE TABLE #dbEstimate (
PK int identity not null primary key,
schema_name varchar(250),
object_name varchar(250),
index_id INT,
ixName VARCHAR(255),
ixType VARCHAR(50),
partition_number int,
data_compression_desc VARCHAR(50),
None_Size INT,
Row_Size INT,
Page_Size INT
)
INSERT INTO #dbEstimate (
schema_name,
object_name,
index_id,
ixName,
ixType,
partition_number,
data_compression_desc)
select
S.name,
o.name,
I.index_id,
I.name,
I.type_desc,
P.partition_number,
P.data_compression_desc
from sys.schemas as S
join sys.objects as O
on S.schema_id = O.schema_id
join sys.indexes as I
on o.object_id = I.object_id
join sys.partitions as P
on I.object_id = P.object_id
and I.index_id= p.index_id
where O.TYPE = 'U'
-- Determine Compression Estimates
DECLARE
@PK INT,
@Schema varchar(150),
@object varchar(150),
@DAD varchar(25),
@partNO int,
@indexID int,
@ixName VARCHAR(250),
@SQL nVARCHAR(max),
@ixType VARCHAR(50),
@Recommended_Compression VARCHAR(10)
DECLARE cCompress CURSOR FAST_FORWARD
FOR
select schema_name, object_name, index_id, partition_number, data_compression_desc
FROM #dbEstimate
OPEN cCompress
FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD -- prime the cursor
WHILE @@Fetch_Status = 0
BEGIN
IF @DAD = 'none'
BEGIN
-- estimate Page compression
INSERT #ObjEst (
object_name,
schema_name,
index_id,partition_number,
size_with_current_compression_setting,
size_with_requested_compression_setting,
sample_size_with_current_compression_setting,
sample_size_with_requested_compresison_setting )
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema,
@object_name = @object,
@index_id = @indexID,
@partition_number = @partNO,
@data_compression = 'page'
UPDATE #dbEstimate
SET none_size = O.size_with_current_compression_setting,
page_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O
ON D.Schema_name = O.Schema_Name
and D.Object_name = O.object_name
and D.index_id = O.index_id
and D.partition_number = O.partition_number
DELETE #ObjEst
-- estimate Row compression
INSERT #ObjEst (
object_name,
schema_name,
index_id,partition_number,
size_with_current_compression_setting,
size_with_requested_compression_setting,
sample_size_with_current_compression_setting,
sample_size_with_requested_compresison_setting )
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema,
@object_name = @object,
@index_id = @indexID,
@partition_number = @partNO,
@data_compression = 'row'
UPDATE #dbEstimate
SET row_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O
ON D.Schema_name = O.Schema_Name
and D.Object_name = O.object_name
and D.index_id = O.index_id
and D.partition_number = O.partition_number
DELETE #ObjEst
END -- none compression estimate
IF @DAD = 'row'
BEGIN
-- estimate Page compression
INSERT #ObjEst (
object_name,schema_name,
index_id,partition_number,
size_with_current_compression_setting,
size_with_requested_compression_setting,
sample_size_with_current_compression_setting,
sample_size_with_requested_compresison_setting )
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema,
@object_name = @object,
@index_id = @indexID,
@partition_number = @partNO,
@data_compression = 'page'
UPDATE #dbEstimate
SET row_size = O.size_with_current_compression_setting,
page_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O
ON D.Schema_name = O.Schema_Name
and D.Object_name = O.object_name
and D.index_id = O.index_id
and D.partition_number = O.partition_number
DELETE #ObjEst
-- estimate None compression
INSERT #ObjEst (
object_name,schema_name,
index_id,partition_number,
size_with_current_compression_setting,
size_with_requested_compression_setting,
sample_size_with_current_compression_setting,
sample_size_with_requested_compresison_setting )
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema,
@object_name = @object,
@index_id = @indexID,
@partition_number = @partNO,
@data_compression = 'none'
UPDATE #dbEstimate
SET none_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O
ON D.Schema_name = O.Schema_Name
and D.Object_name = O.object_name
and D.index_id = O.index_id
and D.partition_number = O.partition_number
DELETE #ObjEst
END -- row compression estimate
IF @DAD = 'page'
BEGIN
-- estimate Row compression
INSERT #ObjEst (
object_name,
schema_name,
index_id,
partition_number,
size_with_current_compression_setting,
size_with_requested_compression_setting,
sample_size_with_current_compression_setting,
sample_size_with_requested_compresison_setting )
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema,
@object_name = @object,
@index_id = @indexID,
@partition_number = @partNO,
@data_compression = 'row'
UPDATE #dbEstimate
SET page_size = O.size_with_current_compression_setting,
row_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O
ON D.Schema_name = O.Schema_Name
and D.Object_name = O.object_name
and D.index_id = O.index_id
and D.partition_number = O.partition_number
DELETE #ObjEst
-- estimate None compression
INSERT #ObjEst (
object_name,
schema_name,
index_id,partition_number,
size_with_current_compression_setting,
size_with_requested_compression_setting,
sample_size_with_current_compression_setting,
sample_size_with_requested_compresison_setting )
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema,
@object_name = @object,
@index_id = @indexID,
@partition_number = @partNO,
@data_compression = 'none'
UPDATE #dbEstimate
SET none_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O
ON D.Schema_name = O.Schema_Name
and D.Object_name = O.object_name
and D.index_id = O.index_id
and D.partition_number = O.partition_number
DELETE #ObjEst
END -- page compression estimate
FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD
END
CLOSE cCompress
DEALLOCATE cCompress
-- set the compression
DECLARE cCompress CURSOR FAST_FORWARD
FOR
select schema_name, object_name, partition_number, ixName, ixType,
Case
when (1-(cast(Row_Size as float) / none_Size)) >= @minCompression
-- and (Row_Size <= Page_Size)
then 'Row'
when (1-(cast(page_Size as float) / none_Size)) >= @minPageCompression
-- and (Page_Size <= row_Size)
then 'Page'
else 'None'
end as Recommended_Compression
from #dbEstimate
where None_Size <> 0
and (Case
when (1-(cast(page_Size as float) / none_Size)) >= @minPageCompression
and (Page_Size <= row_Size) then 'Page'
when (1-(cast(Row_Size as float) / none_Size)) >= @minCompression then 'Row'
-- and (Row_Size <= Page_Size) then 'Row'
-- Bob L, June, 2010 - Don't check row vs page since we already decided to use
else 'None'
end
<> data_compression_desc)
OPEN cCompress
FETCH cCompress INTO @Schema, @object, @partNO, @ixName, @ixType, @Recommended_Compression
-- prime the cursor
WHILE @@Fetch_Status = 0
BEGIN
IF @ixType = 'Clustered' or @ixType='heap'
set @SQL = 'ALTER TABLE ' + @schema + '.' + @object + ' Rebuild with (data_compression = ' +
@Recommended_Compression + ')'
else
set @SQL = 'ALTER INDEX ' + @ixName + ' on ' + @schema + '.' +
@object + ' Rebuild with (data_compression = ' + @Recommended_Compression + ')'
print @SQL
IF @ReportModeOnly = 0
BEGIN
EXEC sp_executesql @SQL
END
FETCH cCompress INTO @Schema, @object, @partNO, @ixName, @ixType, @Recommended_Compression
-- prime the cursor
END
CLOSE cCompress
DEALLOCATE cCompress
RETURN
GO
Now for the script for defragmenting indexes from Michelle Ufford at https://sqlfool.com/2010/04/index-defrag-script-v4-0/
/* Scroll down to the see notes, disclaimers, and licensing information */
DECLARE @indexDefragLog_rename VARCHAR(128)
, @indexDefragExclusion_rename VARCHAR(128)
, @indexDefragStatus_rename VARCHAR(128);
SELECT @indexDefragLog_rename = 'dba_indexDefragLog_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112)
, @indexDefragExclusion_rename = 'dba_indexDefragExclusion_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112)
, @indexDefragStatus_rename = 'dba_indexDefragStatus_obsolete_' + CONVERT(VARCHAR(10), GETDATE(), 112);
IF Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] = 'dba_indexDefragLog')
EXECUTE SP_RENAME dba_indexDefragLog, @indexDefragLog_rename;
IF Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] = 'dba_indexDefragExclusion')
EXECUTE SP_RENAME dba_indexDefragExclusion, @indexDefragExclusion_rename;
IF Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] = 'dba_indexDefragStatus')
EXECUTE SP_RENAME dba_indexDefragStatus, @indexDefragStatus_rename;
Go
CREATE TABLE dbo.dba_indexDefragLog
(
indexDefrag_id INT IDENTITY(1,1) Not Null
, databaseID INT Not Null
, databaseName NVARCHAR(128) Not Null
, objectID INT Not Null
, objectName NVARCHAR(128) Not Null
, indexID INT Not Null
, indexName NVARCHAR(128) Not Null
, partitionNumber SMALLINT Not Null
, fragmentation FLOAT Not Null
, page_count INT Not Null
, dateTimeStart DATETIME Not Null
, dateTimeEnd DATETIME Null
, durationSeconds INT Null
, sqlStatement VARCHAR(4000) Null
, errorMessage VARCHAR(1000) Null
CONSTRAINT PK_indexDefragLog_v40
PRIMARY KEY CLUSTERED (indexDefrag_id)
);
PRINT 'dba_indexDefragLog Table Created';
CREATE TABLE dbo.dba_indexDefragExclusion
(
databaseID INT Not Null
, databaseName NVARCHAR(128) Not Null
, objectID INT Not Null
, objectName NVARCHAR(128) Not Null
, indexID INT Not Null
, indexName NVARCHAR(128) Not Null
, exclusionMask INT Not Null
/* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */
CONSTRAINT PK_indexDefragExclusion_v40
PRIMARY KEY CLUSTERED (databaseID, objectID, indexID)
);
PRINT 'dba_indexDefragExclusion Table Created';
CREATE TABLE dbo.dba_indexDefragStatus
(
databaseID INT
, databaseName NVARCHAR(128)
, objectID INT
, indexID INT
, partitionNumber SMALLINT
, fragmentation FLOAT
, page_count INT
, range_scan_count BIGINT
, schemaName NVARCHAR(128) Null
, objectName NVARCHAR(128) Null
, indexName NVARCHAR(128) Null
, scanDate DATETIME
, defragDate DATETIME Null
, printStatus BIT DEFAULT(0)
, exclusionMask INT DEFAULT(0)
CONSTRAINT PK_indexDefragStatus_v40
PRIMARY KEY CLUSTERED(databaseID, objectID, indexID, partitionNumber)
);
PRINT 'dba_indexDefragStatus Table Created';
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1
BEGIN
DROP PROCEDURE dbo.dba_indexDefrag_sp;
PRINT 'Procedure dba_indexDefrag_sp dropped';
END;
Go
CREATE PROCEDURE dbo.dba_indexDefrag_sp
/* Declare Parameters */
@minFragmentation FLOAT = 10.0
/* in percent, will not defrag if fragmentation less than specified */
, @rebuildThreshold FLOAT = 30.0
/* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
, @executeSQL BIT = 1
/* 1 = execute; 0 = print command only */
, @defragOrderColumn NVARCHAR(20) = 'range_scan_count'
/* Valid options are: range_scan_count, fragmentation, page_count */
, @defragSortOrder NVARCHAR(4) = 'DESC'
/* Valid options are: ASC, DESC */
, @timeLimit INT = 720 /* defaulted to 12 hours */
/* Optional time limitation; expressed in minutes */
, @DATABASE VARCHAR(128) = Null
/* Option to specify a database name; null will return all */
, @tableName VARCHAR(4000) = Null -- databaseName.schema.tableName
/* Option to specify a table name; null will return all */
, @forceRescan BIT = 0
/* Whether or not to force a rescan of indexes; 1 = force, 0 = use existing scan, if available */
, @scanMode VARCHAR(10) = N'LIMITED'
/* Options are LIMITED, SAMPLED, and DETAILED */
, @minPageCount INT = 8
/* MS recommends > 1 extent (8 pages) */
, @maxPageCount INT = Null
/* NULL = no limit */
, @excludeMaxPartition BIT = 0
/* 1 = exclude right-most populated partition; 0 = do not exclude; see notes for caveats */
, @onlineRebuild BIT = 1
/* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
, @sortInTempDB BIT = 1
/* 1 = perform sort operation in TempDB; 0 = perform sort operation in the index's database */
, @maxDopRestriction TINYINT = Null
/* Option to restrict the number of processors for the operation; only in Enterprise */
, @printCommands BIT = 0
/* 1 = print commands; 0 = do not print commands */
, @printFragmentation BIT = 0
/* 1 = print fragmentation prior to defrag;
0 = do not print */
, @defragDelay CHAR(8) = '00:00:05'
/* time to wait between defrag commands */
, @debugMode BIT = 0
/* display some useful comments to help determine if/where issues occur */
AS
/*********************************************************************************
Name: dba_indexDefrag_sp
Author: Michelle Ufford, https://sqlfool.com
Purpose: Defrags one or more indexes for one or more databases
Notes:
CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING.
DO NOT RUN UNATTENDED ON LARGE DATABASES DURING BUSINESS HOURS.
@minFragmentation defaulted to 10%, will not defrag if fragmentation
is less than that
@rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL;
greater than 30% will result in rebuild instead
@executeSQL 1 = execute the SQL generated by this proc;
0 = print command only
@defragOrderColumn Defines how to prioritize the order of defrags. Only
used if @executeSQL = 1.
Valid options are:
range_scan_count = count of range and table scans on the
index; in general, this is what benefits
the most from defragmentation
fragmentation = amount of fragmentation in the index;
the higher the number, the worse it is
page_count = number of pages in the index; affects
how long it takes to defrag an index
@defragSortOrder The sort order of the ORDER BY clause.
Valid options are ASC (ascending) or DESC (descending).
@timeLimit Optional, limits how much time can be spent performing
index defrags; expressed in minutes.
NOTE: The time limit is checked BEFORE an index defrag
is begun, thus a long index defrag can exceed the
time limitation.
@database Optional, specify specific database name to defrag;
If not specified, all non-system databases will
be defragged.
@tableName Specify if you only want to defrag indexes for a
specific table, format = databaseName.schema.tableName;
if not specified, all tables will be defragged.
@forceRescan Whether or not to force a rescan of indexes. If set
to 0, a rescan will not occur until all indexes have
been defragged. This can span multiple executions.
1 = force a rescan
0 = use previous scan, if there are indexes left to defrag
@scanMode Specifies which scan mode to use to determine
fragmentation levels. Options are:
LIMITED - scans the parent level; quickest mode,
recommended for most cases.
SAMPLED - samples 1% of all data pages; if less than
10k pages, performs a DETAILED scan.
DETAILED - scans all data pages. Use great care with
this mode, as it can cause performance issues.
@minPageCount Specifies how many pages must exist in an index in order
to be considered for a defrag. Defaulted to 8 pages, as
Microsoft recommends only defragging indexes with more
than 1 extent (8 pages).
NOTE: The @minPageCount will restrict the indexes that
are stored in dba_indexDefragStatus table.
@maxPageCount Specifies the maximum number of pages that can exist in
an index and still be considered for a defrag. Useful
for scheduling small indexes during business hours and
large indexes for non-business hours.
NOTE: The @maxPageCount will restrict the indexes that
are defragged during the current operation; it will not
prevent indexes from being stored in the
dba_indexDefragStatus table. This way, a single scan
can support multiple page count thresholds.
@excludeMaxPartition If an index is partitioned, this option specifies whether
to exclude the right-most populated partition. Typically,
this is the partition that is currently being written to in
a sliding-window scenario. Enabling this feature may reduce
contention. This may not be applicable in other types of
partitioning scenarios. Non-partitioned indexes are
unaffected by this option.
1 = exclude right-most populated partition
0 = do not exclude
@onlineRebuild 1 = online rebuild;
0 = offline rebuild
@sortInTempDB Specifies whether to defrag the index in TEMPDB or in the
database the index belongs to. Enabling this option may
result in faster defrags and prevent database file size
inflation.
1 = perform sort operation in TempDB
0 = perform sort operation in the index's database
@maxDopRestriction Option to specify a processor limit for index rebuilds
@printCommands 1 = print commands to screen;
0 = do not print commands
@printFragmentation 1 = print fragmentation to screen;
0 = do not print fragmentation
@defragDelay Time to wait between defrag commands; gives the
server a little time to catch up
@debugMode 1 = display debug comments; helps with troubleshooting
0 = do not display debug comments
Called by: SQL Agent Job or DBA
----------------------------------------------------------------------------
DISCLAIMER:
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 or merchantability and/or fitness for a particular purpose.
----------------------------------------------------------------------------
LICENSE:
This index defrag script is free to download and use for personal, educational,
and internal corporate purposes, provided that this header is preserved.
Redistribution or sale of this index defrag script, in whole or in part, is
prohibited without the author's express written consent.
----------------------------------------------------------------------------
Date Initials Version Description
----------------------------------------------------------------------------
2007-12-18 MFU 1.0 Initial Release
2008-10-17 MFU 1.1 Added @defragDelay, CIX_temp_indexDefragList
2008-11-17 MFU 1.2 Added page_count to log table
, added @printFragmentation option
2009-03-17 MFU 2.0 Provided support for centralized execution
, consolidated Enterprise & Standard versions
, added @debugMode, @maxDopRestriction
, modified LOB and partition logic
2009-06-18 MFU 3.0 Fixed bug in LOB logic, added @scanMode option
, added support for stat rebuilds (@rebuildStats)
, support model and msdb defrag
, added columns to the dba_indexDefragLog table
, modified logging to show "in progress" defrags
, added defrag exclusion list (scheduling)
2009-08-28 MFU 3.1 Fixed read_only bug for database lists
2010-04-20 MFU 4.0 Added time limit option
, added static table with rescan logic
, added parameters for page count & SORT_IN_TEMPDB
, added try/catch logic and additional debug options
, added options for defrag prioritization
, fixed bug for indexes with allow_page_lock = off
, added option to exclude right-most partition
, removed @rebuildStats option
, refer to https://sqlfool.com for full release notes
*********************************************************************************
Example of how to call this script:
Exec dbo.dba_indexDefrag_sp
@executeSQL = 1
, @printCommands = 1
, @debugMode = 1
, @printFragmentation = 1
, @forceRescan = 1
, @maxDopRestriction = 1
, @minPageCount = 8
, @maxPageCount = Null
, @minFragmentation = 1
, @rebuildThreshold = 30
, @defragDelay = '00:00:05'
, @defragOrderColumn = 'page_count'
, @defragSortOrder = 'DESC'
, @excludeMaxPartition = 1
, @timeLimit = Null;
*********************************************************************************/
SET NOCOUNT ON;
SET XACT_Abort ON;
SET Quoted_Identifier ON;
BEGIN
BEGIN Try
/* Just a little validation... */
IF @minFragmentation IS Null
Or @minFragmentation Not Between 0.00 And 100.0
SET @minFragmentation = 10.0;
IF @rebuildThreshold IS Null
Or @rebuildThreshold Not Between 0.00 And 100.0
SET @rebuildThreshold = 30.0;
IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
SET @defragDelay = '00:00:05';
IF @defragOrderColumn IS Null
Or @defragOrderColumn Not In ('range_scan_count', 'fragmentation', 'page_count')
SET @defragOrderColumn = 'range_scan_count';
IF @defragSortOrder IS Null
Or @defragSortOrder Not In ('ASC', 'DESC')
SET @defragSortOrder = 'DESC';
IF @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED')
SET @scanMode = 'LIMITED';
IF @debugMode IS Null
SET @debugMode = 0;
IF @forceRescan IS Null
SET @forceRescan = 0;
IF @sortInTempDB IS Null
SET @sortInTempDB = 1;
IF @debugMode = 1 RAISERROR('Undusting the cogs and starting up...', 0, 42) WITH NoWait;
/* Declare our variables */
DECLARE @objectID INT
, @databaseID INT
, @databaseName NVARCHAR(128)
, @indexID INT
, @partitionCount BIGINT
, @schemaName NVARCHAR(128)
, @objectName NVARCHAR(128)
, @indexName NVARCHAR(128)
, @partitionNumber SMALLINT
, @fragmentation FLOAT
, @pageCount INT
, @sqlCommand NVARCHAR(4000)
, @rebuildCommand NVARCHAR(200)
, @dateTimeStart DATETIME
, @dateTimeEnd DATETIME
, @containsLOB BIT
, @editionCheck BIT
, @debugMessage NVARCHAR(4000)
, @updateSQL NVARCHAR(4000)
, @partitionSQL NVARCHAR(4000)
, @partitionSQL_Param NVARCHAR(1000)
, @LOB_SQL NVARCHAR(4000)
, @LOB_SQL_Param NVARCHAR(1000)
, @indexDefrag_id INT
, @startDateTime DATETIME
, @endDateTime DATETIME
, @getIndexSQL NVARCHAR(4000)
, @getIndexSQL_Param NVARCHAR(4000)
, @allowPageLockSQL NVARCHAR(4000)
, @allowPageLockSQL_Param NVARCHAR(4000)
, @allowPageLocks INT
, @excludeMaxPartitionSQL NVARCHAR(4000);
/* Initialize our variables */
SELECT @startDateTime = GETDATE()
, @endDateTime = DATEADD(MINUTE, @timeLimit, GETDATE());
/* Create our temporary tables */
CREATE TABLE #databaseList
(
databaseID INT
, databaseName VARCHAR(128)
, scanStatus BIT
);
CREATE TABLE #processor
(
[INDEX] INT
, Name VARCHAR(128)
, Internal_Value INT
, Character_Value INT
);
CREATE TABLE #maxPartitionList
(
databaseID INT
, objectID INT
, indexID INT
, maxPartition INT
);
IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait;
/* Make sure we're not exceeding the number of processors we have available */
INSERT INTO #processor
EXECUTE XP_MSVER 'ProcessorCount';
IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor)
SELECT @maxDopRestriction = Internal_Value
FROM #processor;
/* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310)
SET @editionCheck = 1 -- supports online rebuilds
ELSE
SET @editionCheck = 0; -- does not support online rebuilds
/* Output the parameters we're working with */
IF @debugMode = 1
BEGIN
SELECT @debugMessage = 'Your selected parameters are...
Defrag indexes with fragmentation greater than ' + CAST(@minFragmentation AS VARCHAR(10)) + ';
Rebuild indexes with fragmentation greater than ' + CAST(@rebuildThreshold AS VARCHAR(10)) + ';
You' + CASE WHEN @executeSQL = 1 THEN ' DO' ELSE ' DO NOT' END + ' want the commands to be executed automatically;
You want to defrag indexes in ' + @defragSortOrder + ' order of the ' + UPPER(@defragOrderColumn) + ' value;
You have' + CASE WHEN @timeLimit IS Null THEN ' not specified a time limit;' ELSE ' specified a time limit of '
+ CAST(@timeLimit AS VARCHAR(10)) END + ' minutes;
' + CASE WHEN @DATABASE IS Null THEN 'ALL databases' ELSE 'The ' + @DATABASE + ' database' END + ' will be defragged;
' + CASE WHEN @tableName IS Null THEN 'ALL tables' ELSE 'The ' + @tableName + ' table' END + ' will be defragged;
We' + CASE WHEN Exists(SELECT TOP 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS Null)
And @forceRescan <> 1 THEN ' WILL NOT' ELSE ' WILL' END + ' be rescanning indexes;
The scan will be performed in ' + @scanMode + ' mode;
You want to limit defrags to indexes with' + CASE WHEN @maxPageCount IS Null THEN ' more than '
+ CAST(@minPageCount AS VARCHAR(10)) ELSE
' between ' + CAST(@minPageCount AS VARCHAR(10))
+ ' and ' + CAST(@maxPageCount AS VARCHAR(10)) END + ' pages;
Indexes will be defragged' + CASE WHEN @editionCheck = 0 Or @onlineRebuild = 0 THEN ' OFFLINE;' ELSE ' ONLINE;' END + '
Indexes will be sorted in' + CASE WHEN @sortInTempDB = 0 THEN ' the DATABASE' ELSE ' TEMPDB;' END + '
Defrag operations will utilize ' + CASE WHEN @editionCheck = 0 Or @maxDopRestriction IS Null
THEN 'system defaults for processors;'
ELSE CAST(@maxDopRestriction AS VARCHAR(2)) + ' processors;' END + '
You' + CASE WHEN @printCommands = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to print the ALTER INDEX commands;
You' + CASE WHEN @printFragmentation = 1 THEN ' DO' ELSE ' DO NOT' END + ' want to output fragmentation levels;
You want to wait ' + @defragDelay + ' (hh:mm:ss) between defragging indexes;
You want to run in' + CASE WHEN @debugMode = 1 THEN ' DEBUG' ELSE ' SILENT' END + ' mode.';
RAISERROR(@debugMessage, 0, 42) WITH NoWait;
END;
IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait;
/* Retrieve the list of databases to investigate */
INSERT INTO #databaseList
SELECT database_id
, name
, 0 -- not scanned yet for fragmentation
FROM sys.databases
WHERE name = IsNull(@DATABASE, name)
And [name] Not In ('master', 'tempdb')-- exclude system databases
And [STATE] = 0 -- state must be ONLINE
And is_read_only = 0; -- cannot be read_only
/* Check to see if we have indexes in need of defrag; otherwise, re-scan the database(s) */
IF Not Exists(SELECT TOP 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS Null)
Or @forceRescan = 1
BEGIN
/* Truncate our list of indexes to prepare for a new scan */
TRUNCATE TABLE dbo.dba_indexDefragStatus;
IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait;
/* Loop through our list of databases */
WHILE (SELECT COUNT(*) FROM #databaseList WHERE scanStatus = 0) > 0
BEGIN
SELECT TOP 1 @databaseID = databaseID
FROM #databaseList
WHERE scanStatus = 0;
SELECT @debugMessage = ' working on ' + DB_NAME(@databaseID) + '...';
IF @debugMode = 1
RAISERROR(@debugMessage, 0, 42) WITH NoWait;
/* Determine which indexes to defrag using our user-defined parameters */
INSERT INTO dbo.dba_indexDefragStatus
(
databaseID
, databaseName
, objectID
, indexID
, partitionNumber
, fragmentation
, page_count
, range_scan_count
, scanDate
)
SELECT
ps.database_id AS 'databaseID'
, QUOTENAME(DB_NAME(ps.database_id)) AS 'databaseName'
, ps.OBJECT_ID AS 'objectID'
, ps.index_id AS 'indexID'
, ps.partition_number AS 'partitionNumber'
, SUM(ps.avg_fragmentation_in_percent) AS 'fragmentation'
, SUM(ps.page_count) AS 'page_count'
, os.range_scan_count
, GETDATE() AS 'scanDate'
FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode) AS ps
Join sys.dm_db_index_operational_stats(@databaseID, OBJECT_ID(@tableName), Null , Null) AS os
ON ps.database_id = os.database_id
And ps.OBJECT_ID = os.OBJECT_ID
and ps.index_id = os.index_id
And ps.partition_number = os.partition_number
WHERE avg_fragmentation_in_percent >= @minFragmentation
And ps.index_id > 0 -- ignore heaps
And ps.page_count > @minPageCount
And ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
GROUP BY ps.database_id
, QUOTENAME(DB_NAME(ps.database_id))
, ps.OBJECT_ID
, ps.index_id
, ps.partition_number
, os.range_scan_count
OPTION (MaxDop 2);
/* Do we want to exclude right-most populated partition of our partitioned indexes? */
IF @excludeMaxPartition = 1
BEGIN
SET @excludeMaxPartitionSQL = '
Select ' + CAST(@databaseID AS VARCHAR(10)) + ' As [databaseID]
, [object_id]
, index_id
, Max(partition_number) As [maxPartition]
From ' + DB_NAME(@databaseID) + '.sys.partitions
Where partition_number > 1
And [rows] > 0
Group By object_id
, index_id;';
INSERT INTO #maxPartitionList
EXECUTE SP_EXECUTESQL @excludeMaxPartitionSQL;
END;
/* Keep track of which databases have already been scanned */
UPDATE #databaseList
SET scanStatus = 1
WHERE databaseID = @databaseID;
END
/* We don't want to defrag the right-most populated partition, so
delete any records for partitioned indexes where partition = Max(partition) */
IF @excludeMaxPartition = 1
BEGIN
DELETE ids
FROM dbo.dba_indexDefragStatus AS ids
Join #maxPartitionList AS mpl
ON ids.databaseID = mpl.databaseID
And ids.objectID = mpl.objectID
And ids.indexID = mpl.indexID
And ids.partitionNumber = mpl.maxPartition;
END;
/* Update our exclusion mask for any index that has a restriction on the days it can be defragged */
UPDATE ids
SET ids.exclusionMask = ide.exclusionMask
FROM dbo.dba_indexDefragStatus AS ids
Join dbo.dba_indexDefragExclusion AS ide
ON ids.databaseID = ide.databaseID
And ids.objectID = ide.objectID
And ids.indexID = ide.indexID;
END
SELECT @debugMessage = 'Looping through our list... there are ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!'
FROM dbo.dba_indexDefragStatus
WHERE defragDate IS Null
And page_count Between @minPageCount And IsNull(@maxPageCount, page_count);
IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;
/* Begin our loop for defragging */
WHILE (SELECT COUNT(*)
FROM dbo.dba_indexDefragStatus
WHERE (
(@executeSQL = 1 And defragDate IS Null)
Or (@executeSQL = 0 And defragDate IS Null And printStatus = 0)
)
And exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) = 0
And page_count Between @minPageCount And IsNull(@maxPageCount, page_count)) > 0
BEGIN
/* Check to see if we need to exit our loop because of our time limit */
IF IsNull(@endDateTime, GETDATE()) < GETDATE()
BEGIN
RAISERROR('Our time limit has been exceeded!', 11, 42) WITH NoWait;
END;
IF @debugMode = 1 RAISERROR(' Picking an index to beat into shape...', 0, 42) WITH NoWait;
/* Grab the index with the highest priority, based on the values submitted;
Look at the exclusion mask to ensure it can be defragged today */
SET @getIndexSQL = N'
Select Top 1
@objectID_Out = objectID
, @indexID_Out = indexID
, @databaseID_Out = databaseID
, @databaseName_Out = databaseName
, @fragmentation_Out = fragmentation
, @partitionNumber_Out = partitionNumber
, @pageCount_Out = page_count
From dbo.dba_indexDefragStatus
Where defragDate Is Null '
+ CASE WHEN @executeSQL = 0 THEN 'And printStatus = 0' ELSE '' END + '
And exclusionMask & Power(2, DatePart(weekday, GetDate())-1) = 0
And page_count Between @p_minPageCount and IsNull(@p_maxPageCount, page_count)
Order By + ' + @defragOrderColumn + ' ' + @defragSortOrder;
SET @getIndexSQL_Param = N'@objectID_Out int OutPut
, @indexID_Out int OutPut
, @databaseID_Out int OutPut
, @databaseName_Out nvarchar(128) OutPut
, @fragmentation_Out int OutPut
, @partitionNumber_Out int OutPut
, @pageCount_Out int OutPut
, @p_minPageCount int
, @p_maxPageCount int';
EXECUTE SP_EXECUTESQL @getIndexSQL
, @getIndexSQL_Param
, @p_minPageCount = @minPageCount
, @p_maxPageCount = @maxPageCount
, @objectID_Out = @objectID OUTPUT
, @indexID_Out = @indexID OUTPUT
, @databaseID_Out = @databaseID OUTPUT
, @databaseName_Out = @databaseName OUTPUT
, @fragmentation_Out = @fragmentation OUTPUT
, @partitionNumber_Out = @partitionNumber OUTPUT
, @pageCount_Out = @pageCount OUTPUT;
IF @debugMode = 1 RAISERROR(' Looking up the specifics for our index...', 0, 42) WITH NoWait;
/* Look up index information */
SELECT @updateSQL = N'Update ids
Set schemaName = QuoteName(s.name)
, objectName = QuoteName(o.name)
, indexName = QuoteName(i.name)
From dbo.dba_indexDefragStatus As ids
Inner Join ' + @databaseName + '.sys.objects As o
On ids.objectID = o.object_id
Inner Join ' + @databaseName + '.sys.indexes As i
On o.object_id = i.object_id
And ids.indexID = i.index_id
Inner Join ' + @databaseName + '.sys.schemas As s
On o.schema_id = s.schema_id
Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + '
And i.type > 0
And ids.databaseID = ' + CAST(@databaseID AS VARCHAR(10));
EXECUTE SP_EXECUTESQL @updateSQL;
/* Grab our object names */
SELECT @objectName = objectName
, @schemaName = schemaName
, @indexName = indexName
FROM dbo.dba_indexDefragStatus
WHERE objectID = @objectID
And indexID = @indexID
And databaseID = @databaseID;
IF @debugMode = 1 RAISERROR(' Grabbing the partition count...', 0, 42) WITH NoWait;
/* Determine if the index is partitioned */
SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*)
From ' + @databaseName + '.sys.partitions
Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';'
, @partitionSQL_Param = '@partitionCount_OUT int OutPut';
EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT;
IF @debugMode = 1 RAISERROR(' Seeing if there are any LOBs to be handled...', 0, 42) WITH NoWait;
/* Determine if the table contains LOBs */
SELECT @LOB_SQL = ' Select @containsLOB_OUT = Count(*)
From ' + @databaseName + '.sys.columns With (NoLock)
Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + '
And (system_type_id In (34, 35, 99)
Or max_length = -1);'
/* system_type_id --> 34 = image, 35 = text, 99 = ntext
max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */
, @LOB_SQL_Param = '@containsLOB_OUT int OutPut';
EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT;
IF @debugMode = 1 RAISERROR(' Checking for indexes that do not allow page locks...', 0, 42) WITH NoWait;
/* Determine if page locks are allowed; for those indexes, we need to always rebuild */
SELECT @allowPageLockSQL = 'Select @allowPageLocks_OUT = Count(*)
From ' + @databaseName + '.sys.indexes
Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
And index_id = ' + CAST(@indexID AS VARCHAR(10)) + '
And Allow_Page_Locks = 0;'
, @allowPageLockSQL_Param = '@allowPageLocks_OUT int OutPut';
EXECUTE SP_EXECUTESQL @allowPageLockSQL, @allowPageLockSQL_Param, @allowPageLocks_OUT = @allowPageLocks OUTPUT;
IF @debugMode = 1 RAISERROR(' Building our SQL statements...', 0, 42) WITH NoWait;
/* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */
IF (@fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1)
And @allowPageLocks = 0
BEGIN
SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
+ @schemaName + N'.' + @objectName + N' ReOrganize';
/* If our index is partitioned, we should always reorganize */
IF @partitionCount > 1
SET @sqlCommand = @sqlCommand + N' Partition = '
+ CAST(@partitionNumber AS NVARCHAR(10));
END
/* If the index is heavily fragmented and doesn't contain any partitions or LOB's,
or if the index does not allow page locks, rebuild it */
ELSE IF (@fragmentation >= @rebuildThreshold Or @allowPageLocks <> 0)
And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1
BEGIN
/* Set online rebuild options; requires Enterprise Edition */
IF @onlineRebuild = 1 And @editionCheck = 1
SET @rebuildCommand = N' Rebuild With (Online = On';
ELSE
SET @rebuildCommand = N' Rebuild With (Online = Off';
/* Set sort operation preferences */
IF @sortInTempDB = 1
SET @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = On';
ELSE
SET @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = Off';
/* Set processor restriction options; requires Enterprise Edition */
IF @maxDopRestriction IS Not Null And @editionCheck = 1
SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')';
ELSE
SET @rebuildCommand = @rebuildCommand + N')';
SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
+ @schemaName + N'.' + @objectName + @rebuildCommand;
END
ELSE
/* Print an error message if any indexes happen to not meet the criteria above */
IF @printCommands = 1 Or @debugMode = 1
RAISERROR('We are unable to defrag this index.', 0, 42) WITH NoWait;
/* Are we executing the SQL? If so, do it */
IF @executeSQL = 1
BEGIN
SET @debugMessage = 'Executing: ' + @sqlCommand;
/* Print the commands we're executing if specified to do so */
IF @printCommands = 1 Or @debugMode = 1
RAISERROR(@debugMessage, 0, 42) WITH NoWait;
/* Grab the time for logging purposes */
SET @dateTimeStart = GETDATE();
/* Log our actions */
INSERT INTO dbo.dba_indexDefragLog
(
databaseID
, databaseName
, objectID
, objectName
, indexID
, indexName
, partitionNumber
, fragmentation
, page_count
, dateTimeStart
, sqlStatement
)
SELECT
@databaseID
, @databaseName
, @objectID
, @objectName
, @indexID
, @indexName
, @partitionNumber
, @fragmentation
, @pageCount
, @dateTimeStart
, @sqlCommand;
SET @indexDefrag_id = SCOPE_IDENTITY();
/* Wrap our execution attempt in a try/catch and log any errors that occur */
BEGIN Try
/* Execute our defrag! */
EXECUTE SP_EXECUTESQL @sqlCommand;
SET @dateTimeEnd = GETDATE();
/* Update our log with our completion time */
UPDATE dbo.dba_indexDefragLog
SET dateTimeEnd = @dateTimeEnd
, durationSeconds = DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd)
WHERE indexDefrag_id = @indexDefrag_id;
END Try
BEGIN Catch
/* Update our log with our error message */
UPDATE dbo.dba_indexDefragLog
SET dateTimeEnd = GETDATE()
, durationSeconds = -1
, errorMessage = Error_Message()
WHERE indexDefrag_id = @indexDefrag_id;
IF @debugMode = 1
RAISERROR(' An error has occurred executing this command! Please review the dba_indexDefragLog table for details.'
, 0, 42) WITH NoWait;
END Catch
/* Just a little breather for the server */
WAITFOR Delay @defragDelay;
UPDATE dbo.dba_indexDefragStatus
SET defragDate = GETDATE()
, printStatus = 1
WHERE databaseID = @databaseID
And objectID = @objectID
And indexID = @indexID
And partitionNumber = @partitionNumber;
END
ELSE
/* Looks like we're not executing, just printing the commands */
BEGIN
IF @debugMode = 1 RAISERROR(' Printing SQL statements...', 0, 42) WITH NoWait;
IF @printCommands = 1 Or @debugMode = 1
PRINT IsNull(@sqlCommand, 'error!');
UPDATE dbo.dba_indexDefragStatus
SET printStatus = 1
WHERE databaseID = @databaseID
And objectID = @objectID
And indexID = @indexID
And partitionNumber = @partitionNumber;
END
END
/* Do we want to output our fragmentation results? */
IF @printFragmentation = 1
BEGIN
IF @debugMode = 1 RAISERROR(' Displaying a summary of our action...', 0, 42) WITH NoWait;
SELECT databaseID
, databaseName
, objectID
, objectName
, indexID
, indexName
, partitionNumber
, fragmentation
, page_count
, range_scan_count
FROM dbo.dba_indexDefragStatus
WHERE defragDate >= @startDateTime
ORDER BY defragDate;
END;
END Try
BEGIN Catch
SET @debugMessage = Error_Message() + ' (Line Number: ' + CAST(Error_Line() AS VARCHAR(10)) + ')';
PRINT @debugMessage;
END Catch;
/* When everything is said and done, make sure to get rid of our temp table */
DROP TABLE #databaseList;
DROP TABLE #processor;
DROP TABLE #maxPartitionList;
IF @debugMode = 1 RAISERROR('DONE! Thank you for taking care of your indexes! :)', 0, 42) WITH NoWait;
SET NOCOUNT OFF;
RETURN 0
END