Share via

Finding Fragmented Indexes in a Database Running in SQL Server 2000 Compatibility Mode on 2005

  • Article

The Index Physical Statistics report in SQL Management Studio is an easy way to identify fragmented indexes. But the report won't run against a database set to 8.0 (SQL Server 2000) compatibility mode. Here's a script that will generate a list of fragmented indexes for databases running with 8.0 mode:

USE Your Database; -- change the name of the target database here and in the variable @dbname below

GO

SET NOCOUNT ON;

DECLARE @frag float;

DECLARE @dbname nvarchar(130);

DECLARE @dbid int;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SET @dbname = N'Your Database'-- change the name of the target database here

SET @frag = 10.0 -- change this value to adjust the threshold for fragmentation

SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = @dbname

SELECT

PS.object_id AS Objectid,

O.name AS ObjectName,

S.name AS SchemaName,

I.name AS IndexName,

PS.index_id AS IndexId,

PS.partition_number AS PartitionNum,

ROUND(PS.avg_fragmentation_in_percent, 2) AS Fragmentation,

PS.record_count AS RecordCount

FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, 'SAMPLED') PS

JOIN sys.objects O ON PS.object_id = O.object_id

JOIN sys.schemas S ON S.schema_id = O.schema_id

JOIN sys.indexes I ON I.object_id = PS.object_id

AND I.index_id = PS.index_id

WHERE PS.avg_fragmentation_in_percent > @frag AND PS.index_id > 0

ORDER BY record_count desc;

This script was adapted from a script originally published on MSDN under the SQL Server Books Online topic sys.dm_db_index_physical_stats.

Comments