BPSoftware.com Blog

Monday, November 24, 2008

SQL: Index Fragmentation Maintenance

In a previous post, I discussed SQL Server's built in ability for suggesting missing indexes. As records are inserted, updated and deleted from tables indexes can become fragmented.

A fragmented index affects performance (table scans and index seeks) when the DBMS needs to search for records.

This probably leaves the question, "How can one defragment the indexes of SQL tables?"

Within SQL Server 2005 and greater you can setup a maintenance plan to defragment all the indexes within all or a particular database. You can also right click on an index and select to either Rebuild or Reorganize and index (rebuilding will re-create the index whereas reorganize reorders the index pages).

I prefer to have a little more control and use the following script as part of my maintenance plan. This script allows for a little more control in the selecting of tables, indexes and databases to maintain. I tend to rebuild indexes that are more than 30% fragmented and reorganize those that are greater than 5%.

SQL Server 2005 (and greater) has index information more readily accessible. The key to this script is the index information that is contained in sys.dm_db_index_physical_stats. This table returns the size and fragmentation information for each index.

/** **/
2

-- Create table to hold index information
3

SET NOCOUNT ON;
4

create table _IndexInformation (
6

database_id smallint,
7

ObjectName varchar(255),
8

[object_id] int,
9

IndexName varchar(255),
10

[index_id] int,
11

index_level int,
12

index_type_desc varchar(60),
13

avg_fragmentation_in_percent float,
14

fragment_count float,
15

page_count bigint,
16

record_count bigint,
17

index_depth int
18

);
19

/** **/

/** **/
2

-- clear table contents
3

truncate table _IndexInformation;
4

-- populate table
6

insert into _IndexInformation
7

select
8

database_id,
9

object_name(i.object_id) AS [ObjectName],
10

i.[object_id],
11

i.name AS [IndexName],
12

dm.index_id,
13

dm.index_level,
14

dm.index_type_desc,
15

dm.avg_fragmentation_in_percent,
16

dm.fragment_count,
17

dm.page_count,
18

dm.record_count,
19

dm.index_depth
20

from
21

-- database, table and index information can be specified
22

/*
23

sys.dm_db_index_physical_stats (
24

{ database_id NULL 0 DEFAULT }
25

, { object_id NULL 0 DEFAULT }
26

, { index_id NULL 0 -1 DEFAULT }
27

, { partition_number NULL 0 DEFAULT }
28

, { mode NULL DEFAULT }
29

)
29

This example uses database 1; you should replace this with the ID of the database you wish to process
30

*/
31

sys.dm_db_index_physical_stats(1,null,null,null,'detailed') dm
32

INNER JOIN sys.indexes i ON
33

(i.object_id = dm.object_id) AND (i.index_id = dm.index_id);
34

/** **/
35

/** **/
2

-- read index table and maintain indexes
3

SET NOCOUNT ON;
4

SET ANSI_PADDING ON;
5

SET ANSI_NULLS ON;
6

SET QUOTED_IDENTIFIER ON;
7

-- Declare variables.
10

declare @tablename as varchar(255)
11

declare @indexname as varchar(255)
12

declare @frag as float
13

declare @cmd as varchar(1024)
14

-- Declare a cursor.
16

declare indexes insensitive scroll cursor
17

for
18

select
19

ObjectName,
20

IndexName,
21

avg_fragmentation_in_percent
22

from
23

_IndexInformation
24

where
25

(avg_fragmentation_in_percent >= 5);
26

-- Open the cursor.
28

open indexes
29

-- Loop through all the indexes.
32

while (1=1)
33

begin
34

fetch next from indexes into @tablename, @indexname, @frag;
35

if @@fetch_status <> 0 break;
36

-- if fragmentation is above 30% rebuild index
37

-- if fragmentation is above 5% reorganize index
38

if (@frag >= 30)
39

begin
40

set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REBUILD;'
41

exec (@cmd)
42

print @cmd;
43

end
44

else
45

if (@frag >= 5)
46

begin
47

set @cmd = 'ALTER INDEX [' + rtrim(@indexname) + '] ON [' + @tablename +'] REORGANIZE;'
48

exec (@cmd)
49

print @cmd;
50

end;
51

end
52

-- Close cursor
56

close indexes;
57

deallocate indexes;
58

/** **/
59

/** **/
2

-- delete index table
3

drop table _IndexInformation;
4

/** **/
5

I have the script schedule to as part of my database maintenance plan. I run the script several times with additional selection criteria limiting it to groups of tables in a particular database.

Labels: Code, SQL

posted by Brad Prendergast at 9:51:00 PM

Comments:

Shouldn't you specify database id in sys.dm_db_index_physical_stats query (second listing row 31)?

Otherwise you get funny results as object_id's are database specific.

posted by

Anonymous Tuesday, November 25, 2008 7:28:00 AM

The use of sys.dm_db_index_physical_stats does not require a database, however in this case that is how it is used. The actual production code does specify a database; it omitted it for example. I will add a reference back in and a comment indicating such. Thanks!

posted by

Brad Prendergast Tuesday, November 25, 2008 7:34:00 AM