Technical Article

Checking fragmented indexes

,

Here's a simple script for a procedure where you pass a database (and optional schema...else assume DBO) and you get a list of ALTER INDEX...REORGANIZE statements that target any indices fragmented more than 10% which you can change to suit your needs. The ALTER INDEX syntax for 2005/2008 replaces the DBCC INDEXDEFRAG which will soon be deprecated.

Be aware that this script takes quite a while to generate on a database with lots of indices, even with LIMITED being passed to dm_db_index_physical_stats for the sample quality.

If you feel comfortable with this script, you can obviously adapt it to execute each one of those ALTER INDEX statements automatically by reading from the table.

 

Enjoy!

 

Gaby Abed

-- This script, as is, generates of list of index defrag candidates,
-- current threshhold at 10%, on a target database with default schema
-- of dbo.
use msdb
go
create procedure check_fragmented_indexes
 @dbname sysname,
 @schemaname sysname = 'dbo'
as
create table ##fragmented_indexes
(
 schemaname varchar(128),
 dbname varchar(128),
 tablename varchar(128),
 index_name varchar(128),
 fragmentation float,
 numpages int
)

create table ##schema_table
(
 schemaname varchar(128),
 tablename varchar(128)
)
declare @query varchar(5000)

select @query = 'use ' + @dbname + ';
 insert into ##fragmented_indexes(dbname, tablename, index_name, fragmentation, numpages)
 SELECT db_name(ps.database_id), object_name(ps.OBJECT_ID),
 b.name, ps.avg_fragmentation_in_percent, ps.page_count
 FROM sys.dm_db_index_physical_stats (db_id(''' +@dbname+ '''), NULL, NULL, NULL, ''LIMITED'') AS ps
 INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
 AND ps.index_id = b.index_id
 WHERE ps.database_id = DB_ID() and avg_fragmentation_in_percent > 10 and b.name <> ''NULL''
 ORDER BY object_name(ps.OBJECT_ID);

 insert into ##schema_table
 select s.name, t.name from sys.tables t
 left join sys.schemas s on (s.schema_id = t.schema_id)
 order by s.name, t.name
'

exec(@query)

update ##fragmented_indexes
 set schemaname = (select schemaname from ##schema_table where ##schema_table.tablename = ##fragmented_indexes.tablename)

select 'ALTER INDEX ' + index_name + ' ON ' + quotename(schemaname) + '.' + quotename(tablename) + ' REORGANIZE;'
 from ##fragmented_indexes

drop table ##fragmented_indexes
drop table ##schema_table

go
exec check_fragmented_indexes 'master' -- target DB goes here
go
drop proc check_fragmented_indexes

Rate

5 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (3)

You rated this post out of 5. Change rating