通过

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

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

  • Last updated on