Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Checking Your Database Fragmentation Level

By Nicholas Cain, (first published: 2004/06/04)

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:

  1. 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.
  2. DefragText to hold the text of the DBCC DBREINDEX statements when they are created.
  3. 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).
  4. 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:

  1. “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
  1. “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’
  1. “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
  1. “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
  1. “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

Total article views: 33703 | Views in the last 30 days: 35
 
Related Articles
FORUM

Creating Index on the subcription database tables

Creating Index on the subcription database tables

FORUM

script to create indexes existing in a database

script to create indexes existing in a database

FORUM

Tips on creating indexes

Tips on creating indexes

FORUM

Creating an index

Creating an index

SCRIPT

Generate Create All Secondary Indexes For Entire Database

Generate Script to create all secondary indexes for a database. I have included the ability to gene...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones