In all OLTP environments, virtually all indexes will become fragmented over time.
Nearly all UPDATE, INSERT or DELETE activity will cause your indexes to become less
well organized than they were when they were first created. There will be more page
splits, there will be a greater number of pages with less data on them, and consequently,
there will be more I/O required to satisfy each SELECT. And the more fragmented
your data and indexes become, the slower your application will be, and the more
space your data will take up. What can you do to address this? You can reindex them
on a regular basis.
On a basic level, you can use the Database Maintenance Wizard to perform reindexing,
and create Maintenance Plans to do the job. This will work if you are prepared to
accept its inherent limitations. Firstly, the reindexing, which is set up and done
by the Maintenance Wizard, is indiscriminate. It will reindex everything, regardless
of whether it needs it or not. Now if you have a large database with large tables
and lots of indexes, this may be problematic, as it may take too long, certainly
longer than your available maintenance window, to indiscriminately reindex the entire
database. And that's the problem - it's all or nothing, you can't segment your database
tables in any way at all.
So what else can you do? You can write a script to reindex selected tables. This
way you can segment your database and reduce the time spent during your maintenance
window on reindexing. This time is something you will want to reduce to a minimum,
as reindexing exclusively locks tables, preventing users from accessing it while
the indexing is occurring. So you could, for example, reindex one-fifth of your
tables each night per working day of the week, so all are done at least once a week.
However, this is also indiscriminate - you will be reindexing tables whether their
data and indexes are fragmented or not.
This is where selective reindexing is recommended. You need to be able to check
your tables' indexes and data fragmentation, retain the data, and then act on it,
to reindex in a discriminate and deterministic way. Only by taking a systematic
approach such as this, can you be assured that you are only reindexing the table
data and indexes that actually need it. And only in this way can you minimize the
amount of time taken to reindex. Reducing reindexing time is crucial, as during
a full reindex, if you don't want to negatively affect your users.
We use the command DBCC SHOWCONTIG()
One of the big advances between SQL Server 2000 and previous versions of SQL Server
was in this simple, yet crucial command. DBCC SHOWCONTIG is the tool supplied with
SQL Server to check how fragmented an index is. In previous versions of SQL Server
(7.0 and earlier), this command would only output text. This is fine if the command
is being used on a manual basis. However, for automation purposes, it creates serious
problems. It means you need to cycle though each table and output to text file,
then construct a cumbersome process for reading and interpreting the textual output
in order to obtain the information you're after.
SQL Server 2000 introduced a key clause to the DBCC SHOWCONTIG() command, namely
WITH TABLERESULTS. This means you can run the command and capture the output straight
into a table, rather than having to output to text file and include a layer of clumsy
XP_CMDSHELL manipulation of text files.
This means that in SQL Server 2000, you can construct a procedure to cycle through
your tables, running DBCC SHOWCONTIG on them, capturing the fragmentation information
from the command on each into a table. You can then cycle through the results, conditionally
taking defragmentation action on the indexes, depending on how fragmented they are.
This is what the accompanying stored procedure does.
CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL
AS
/*
This stored procedure checks index fragmentation in a database and defragments
indexes whose scan densities fall below a specified threshold, @magfrag, which
is passed to the SP. This SP was initially based on a code sample in SQL Server 2000 Books Online.
Must be run in the database to be defragmented.
*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)
--check this is being run in a user database
SELECT @dbname = db_name()
IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
BEGIN
PRINT 'This procedure should not be run in system databases.'
RETURN
END
--begin Stage 1: checking fragmentation
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0
-- Create the temporary table to hold fragmentation information
CREATE TABLE #fraglist
(
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
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
)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database running dbcc showcontig on each one
FETCH NEXT
FROM tables
INTO @tableidchar
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES,
NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tableidchar
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Report the ouput of showcontig for results checking
SELECT * FROM #fraglist
-- Begin Stage 2: (defrag) declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
FROM #fraglist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Write to output start time for information purposes
SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
-- Open the cursor
OPEN indexes
-- Loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON
SELECT @execstr = 'DBCC DBREINDEX (' + "'" +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + "'" + ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT 'Now executing: '
SELECT(@execstr)
EXEC (@execstr)
SET QUOTED_IDENTIFIER OFF
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Report on finish time for information purposes
SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
-- Delete the temporary table
DROP TABLE #fraglist
GO
This stored procedure should be created in the master database, which will enable
it to be run in any user database on a server.
It is run by calling it in a user database, and passing it a parameter (MAXFRAG).
This is a percentage value. What this means is to defragment any indexes whose scan
density fall below this value. For example, if you want to defragment any indexes
who scan density is less than 95%:
USE pubs
GO
EXEC sp_deframent_indexes 95.00
This procedure depends upon the measure scan density, but scan density is not a
valid measure of fragmentation of indexes which span multiple files. If your indexes
do span multiple files, you will need to modify this SP to fragment on the basis
of another measure (e.g. Logical Frag). However, this kind of modification is beyond
the scope of this article; if your indexes span multiple files, you'll need to do
more work.