|
Execute the following Microsoft SQL Server
T-SQL script in Management Studio Query Editor to generate >DBCC SHOWCONTIG< commands
for all the indexes in AdventureWorks database:
-- Find index fragmentation for a specific table
SELECT index_id, index_type_desc,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
DB_ID('AdventureWorks2008'),
OBJECT_ID('Sales.SalesOrderDetail'),
NULL,
NULL,
NULL
);
/* index_id index_type_desc avg_fragmentation_in_percent
1 CLUSTERED INDEX 0.242914979757085
2 NONCLUSTERED INDEX 0.735294117647059
3 NONCLUSTERED INDEX 3.08370044052863
6 NONCLUSTERED INDEX 0.797872340425532 */
------------
-- Microsoft SQL Server index fragmentation script generator
USE AdventureWorks;
SELECT 'dbcc showcontig (' + convert(VARCHAR,i.[object_id]) + ',' + -- table id
convert(VARCHAR,i.index_id) + ') -- ' + -- index id
s.name + -- schema name
'.' + object_name(i.[object_id]) + -- table name
'.' + i.name -- index name
FROM sys.schemas s
JOIN sys.objects o
ON (s.schema_id = o.schema_id)
JOIN sys.indexes i
ON (o.[object_id] = i.[object_id])
WHERE o.TYPE = 'U'
AND i.TYPE IN (1,2)
ORDER BY s.name,
object_name(i.[object_id]),
i.index_id
GO
/* Partial results
dbcc showcontig (642101328,1) --
-- Sales.SalesOrderDetail.PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
dbcc showcontig (642101328,2) -- Sales.SalesOrderDetail.AK_SalesOrderDetail_rowguid
dbcc showcontig (642101328,3) -- Sales.SalesOrderDetail.IX_SalesOrderDetail_ProductID
dbcc showcontig (754101727,1) -- Sales.SalesOrderHeader.PK_SalesOrderHeader_SalesOrderID
dbcc showcontig (754101727,2) -- Sales.SalesOrderHeader.AK_SalesOrderHeader_rowguid
dbcc showcontig (754101727,3) --
-- Sales.SalesOrderHeader.AK_SalesOrderHeader_SalesOrderNumber
*/
Related article:
Index Fragmentation Report in SQL Server 2005 and 2008
|
