There are a lot of posts on the site regarding index fragmentation and possible ways to deal with this. You could certainly use the Database Maintenance Plans built into SQL Server to handle your re-indexing needs, however frequently you want something a little more directed than the catch all solution provided by the maintenance plan.
In this article I will take you through the steps of building a dts package to run either on an ad-hoc or scheduled basis that goes out to your databases to grab the index fragmentation levels and email you a script to run to defrag those indexes that are fragmented beyond the level that you select. As a part of the dts it will archive the index information to archive tables so that you can go back over time and trend the fragmentation, which will allow you to investigate possible changes to the fill factor that you are using.
Creating the Database Objects
First things first, we are going to need a database to hold the information, if you already have a database for this purpose, otherwise we’ll create one and call in AdminDB.
| CREATE DATABASE ADMINDB |
In this database we are going to create four tables:
- Index_Info_Northwind to hold the information from each database that we want to check. The table columns match the output columns from a DBCC SHOWCONTIG with two additional columns (with default values) to hold the database name and the current date. For the sake of this article we are going to use the Northwind database. Change the suffix of the table to match the database you want to capture the results from.
- DefragText to hold the text of the DBCC DBREINDEX statements when they are created.
- Index_Info_Daily to hold the list of indexes that we will be creating the re-index script
for (population of this table will be based upon criteria specified
later).
- Index_Info_Archive to hold historical index data. This will allow trending of indexes to see
their fragmentation over time helping you to decide on fill factors that
might need to be changed, or to allow you to alter the job schedule to fit
better with the amount of fragmentation that occurs within your databases.
USE ADMINDB
GO
CREATE TABLE [INDEX_INFO_NORTHWIND] (
[dbname] [varchar] (50) NOT NULL CONSTRAINT [DF_DB] DEFAULT ('NORTHWIND'),
[ObjectName] [varchar] (50) NULL ,
[ObjectId] [int] NULL ,
[IndexName] [varchar] (100) NULL ,
[IndexId] [int] NULL ,
[Lvl] [int] NULL ,
[CountPages] [int] NULL ,
[CountRows] [int] NULL ,
[MinRecSize] [int] NULL ,
[MaxRecSize] [int] NULL ,
[AvgRecSize] [int] NULL ,
[ForRecCount] [int] NULL ,
[Extents] [int] NULL ,
[ExtentSwitches] [int] NULL ,
[AvgFreeBytes] [int] NULL ,
[AvgPageDensity] [int] NULL ,
[ScanDensity] [decimal](9, 0) NULL ,
[BestCount] [int] NULL ,
[ActualCount] [int] NULL ,
[LogicalFrag] [decimal](9, 0) NULL ,
[ExtentFrag] [decimal](9, 0) NULL ,
[CAPTURE_DATE] [datetime] NULL CONSTRAINT [DF_DATE] DEFAULT (getdate())
)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[DEFRAGTEXT] (
[THETEXT] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[INDEX_INFO_DAILY] (
[dbname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectId] [int] NULL ,
[IndexName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexId] [int] NULL ,
[Lvl] [int] NULL ,
[CountPages] [int] NULL ,
[CountRows] [int] NULL ,
[MinRecSize] [int] NULL ,
[MaxRecSize] [int] NULL ,
[AvgRecSize] [int] NULL ,
[ForRecCount] [int] NULL ,
[Extents] [int] NULL ,
[ExtentSwitches] [int] NULL ,
[AvgFreeBytes] [int] NULL ,
[AvgPageDensity] [int] NULL ,
[ScanDensity] [decimal](9, 0) NULL ,
[BestCount] [int] NULL ,
[ActualCount] [int] NULL ,
[LogicalFrag] [decimal](9, 0) NULL ,
[ExtentFrag] [decimal](9, 0) NULL ,
[CAPTURE_DATE] [datetime] NOT NULL ,
[THEID] [int] IDENTITY (1, 1) NOT NULL
)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[INDEX_INFO_ARCHIVE] (
[DBNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OBJECTNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INDEXNAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCANDENSITY] [decimal](9, 0) NULL ,
[CAPTURE_DATE] [datetime] NULL
)
ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [INDEX_INFO_ARCHIVE_OBJECTNAME] ON [dbo].[INDEX_INFO_ARCHIVE]([OBJECTNAME])
ON [PRIMARY]
GO
Next we’ll need a stored procedure to do the work of pulling the showcontig results into the table.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE USP_DBshowcontig_single_db @name varchar(50)
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @dbname VARCHAR(20)
DECLARE @sql VARCHAR(1000)
DECLARE @inserttable VARCHAR(3200)
-- Create the table
CREATE TABLE #DBFRAGMENT (
ObjectName VARCHAR (50),
ObjectId INT,
IndexName VARCHAR (100),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
create table #tablename (table_name varchar(400))
--DECLARE DB Cursor
DECLARE databases CURSOR FOR
SELECT NAME
FROM MASTER.DBO.SYSDATABASES
WHERE NAME = @NAME
--Open the cursor
OPEN databases
FETCH NEXT FROM databases INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'SELECT TABLE_NAME = NAME FROM ' + @dbname + '..SYSOBJECTS WHERE XTYPE =' + '''' + 'U' + ''''
print @sql
insert into #tablename exec(@sql)
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM #tablename
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TABLENAME
-- Do the showcontig of all indexes of the table
INSERT INTO #DBFRAGMENT
EXEC ('USE ' + @dbname + ' DBCC SHOWCONTIG (''' + @tablename + ''') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM tables INTO @tablename
END
set @inserttable ='INSERT INTO ADMINDB.DBO.INDEX_INFO_'+@NAME+'(ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
select ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> ''''
--PRINT @INSERTTABLE
EXEC (@inserttable)
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
delete from #tablename
delete from #DBFRAGMENT
FETCH NEXT FROM databases INTO @dbname
END
CLOSE databases
DEALLOCATE databases
drop table #tablename
--Delete the temporary table
DROP TABLE #DBFRAGMENT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
We will create one more procedure, this one will do the work of pulling the data from the Index_Information table and making it into a reindex statement that can be run or exported to a text file for execution later. The script as stands will re-index the tables as you go along, to prevent this comment out the line “exec (@sql)”.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE USP_Create_Reindex_Script
AS
SET ARITHABORT ON
DECLARE @DBNAME VARCHAR(50),
@OBJECTNAME VARCHAR(100),
@INDEXNAME VARCHAR(100),
@SQL VARCHAR(2000),
@THEID INT
SELECT @THEID = MIN(THEID) FROM INDEX_INFO_DAILY WHERE THEID > 0
WHILE @THEID IS NOT NULL
BEGIN
SELECT @DBNAME = DBNAME FROM INDEX_INFO_DAILY WHERE THEID = @THEID
SELECT @OBJECTNAME = OBJECTNAME FROM INDEX_INFO_DAILY WHERE THEID = @THEID
SELECT @INDEXNAME = INDEXNAME FROM INDEX_INFO_DAILY WHERE THEID = @THEID
SET @SQL = 'USE '+@DBNAME+'; DBCC DBREINDEX ('+@OBJECTNAME+','+@INDEXNAME+',80);'
INSERT INTO DEFRAGTEXT(THETEXT) VALUES (@SQL)
exec(@sql) --Comment out if you don’t want to automate the reindex
SELECT @THEID = MIN(THEID) FROM INDEX_INFO_DAILY WHERE THEID > 0 AND THEID > @THEID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Creating the DTS Package
Now we have all of the objects it’s time to create the package. It will be laid out as shown below.
Firstly you will need to make the connection to your SQL Server, make the default database whatever you are using, in our example here it’s AdminDB, then we go through the steps:
- “Archive Old Data” – This takes the previous days data from the Index_Info_Daily table and inserts it into the Index_Info_Archive table.
INSERT INTO INDEX_INFO_ARCHIVE SELECT DBNAME, OBJECTNAME, INDEXNAME, SCANDENSITY, CAPTURE_DATE FROM INDEX_INFO_DAILY
- “Index Info Northwind” – Executes the procedure USP_DBshowcontig_single_db and passes the database name as a parameter (in this case Northwind).
EXEC USP_DBshowcontig_single_db ‘Northwind’
- “Insert Daily Table” – This truncates the daily table (removing yesterdays entries) and inserts the data for today based upon set criteria. In this case we are just inserting those objects with a scan density of less than 75% and that have more than 5000 rows.
TRUNCATE TABLE INDEX_INFO_DAILY GO INSERT INTO INDEX_INFO_DAILY SELECT * FROM INDEX_INFO_NORTHWIND WHERE CAPTURE_DATE > DATEADD (HOUR, -12, GETDATE()) AND SCANDENSITY < 75 AND COUNTROWS > 5000 GO
- “Create Reindex” – This truncates the DefragText table, then executes the USP_Create_Reindex_Script which populates the DefragText table with the new reindex script and (if you have not uncommented that section of the procedure) will also reindex those objects inserted into Index_Info_Daily in the previous step.
TRUNCATE TABLE DEFRAGTEXT GO EXEC USP_CREATE_REINDEX_SCRIPT GO
- “Email Results” – This just uses xp_sendmail to send out the reindex script in an email allowing you to run it manually should you not wish to use the automated reindex functionality (which you should not do initially until you are confident of the impact that the reindex will have upon your server and the potential impact to users).
Exec master..xp_sendmail @recipients = ‘recipient@email.com’, @subject = ‘Database Reindex Script’, @query = ‘SELECT * FROM ADMINDB.DBO.DEFRAGTEXT’
Further Enhancements
This is the basic package, it can be spruced up a lot and more functionality added. For example you can add as many databases as you like, just add another table for each one and repeat step 2 of the DTS package for each one, remembering to include those tables in the insert in step 3. The script created will change databases as it needs to, so the single reindex script is as simple as copying the text from the email into query analyzer and hitting F5.
On my servers currently I am running this against 30 databases, on a nightly basis and having the script automatically reindex. As I have few, if any, users overnight this is not a problem for me, however you should make sure that you are not impacting users when you do this.
I am also running a data pump to put the object information into an excel spreadsheet, which then gets emailed to my manager and a couple of the development staff. While it’s not a complete trending report it does serve to identify some problem indexes.
You might want to add some kind of advanced reporting functionality into this at a later point to show the trend of the indexes, I’ve used this in the past for a couple of indexes that were showing up on an almost nightly basis, where I changed the fill factor on those indexes and now weeks go by before I see them appear in the report again.
If there are any enhancements you would like me to add, or functionality that you would like to see improved let me know, or if you come up with an improvement please ‘share the wealth’.
Nicholas Cain
April 2004
