|
The following
Microsoft SQL Server T-SQL queries will generate index fragmentation report for SQL Server 2005 / 2008 and SQL Server 2000 versions.
-- Index fragmentation in a database - QUICK SYNTAX
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008'),
NULL,NULL,NULL,NULL);
------------
USE DatabaseName;
-- SQL Server 2005/2008 Index Fragmentation Report
-- SQL Server DMV - Dynamic Management View
SELECT schema_name(t.schema_id) AS [Schema],
object_name(ps.object_id) AS [Table],
i.name AS [Index],
ps.Index_type_desc AS IndexType,
convert(TINYINT,ps.avg_fragmentation_in_percent) AS [AvgFrag%],
convert(TINYINT,ps.avg_page_space_used_in_percent) AS [AvgSpaceUsed%],
ps.record_count AS RecordCnt,
ps.fragment_count AS FragmentCnt
FROM sys.dm_db_index_physical_stats(db_id(db_name()),
NULL,NULL,NULL,'DETAILED') ps -- Faster option: SAMPLED
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
INNER JOIN sys.tables t
ON ps.object_id = t.object_id
WHERE t.is_ms_shipped = 0
ORDER BY [Schema], [Table], [Index]
/*
Schema Table Index IndexType AvgFrag% AvgSpaceUsed% RecordCnt FragmentCnt
dbo TranHist PK_ID_TRANHIST CLUSTERED INDEX 52 73 484437 191
....*/
------------
-- SQL Server 2005 and on fragmentation queries based on sys.dm_db_index_physical_stats
USE AdventureWorks2008;
GO
-- DETAILED fragmentation info on table
SELECT DB=DB_NAME(database_id), [Object]=OBJECT_NAME (OBJECT_ID),
* FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks2008'),
OBJECT_ID(N'Sales.SalesOrderHeaderSalesReason'), NULL, NULL , 'DETAILED');
GO
-- Fragmentation info on table
SELECT DB=DB_NAME(database_id), [Object]=OBJECT_NAME (OBJECT_ID),
* FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks2008'),
OBJECT_ID(N'Sales.SalesOrderHeaderSalesReason'), NULL, NULL , NULL);
GO
-- All fragmentation info
SELECT DB=DB_NAME(database_id), [Object]=OBJECT_NAME (OBJECT_ID),
* FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks2008'),
NULL, NULL, NULL , NULL);
GO
------------
-- SQL Server 2000 index fragmentation for all indexes of a table
use AdventureWorks2008;
dbcc showcontig('Sales.SalesOrderDetail') with all_indexes
/*
....
DBCC SHOWCONTIG scanning 'SalesOrderDetail' table...
Table: 'SalesOrderDetail' (898102240); index ID: 3, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 227
- Extents Scanned..............................: 31
- Extent Switches..............................: 30
- Avg. Pages per Extent........................: 7.3
- Scan Density [Best Count:Actual Count].......: 93.55% [29:31]
- Logical Scan Fragmentation ..................: 3.08%
- Extent Scan Fragmentation ...................: 25.81%
- Avg. Bytes Free per Page.....................: 79.5
- Avg. Page Density (full).....................: 99.02%
...
*/
------------
------------
-- SQL Server 2000 version - DBCC SHOWCONTIG for index fragmentation
------------
USE Northwind;
SELECT 'DBCC SHOWCONTIG (' + convert(VARCHAR,o.id) + ',' +
convert(VARCHAR,i.indid) + ') -- ' + object_name(o.id) + -- table name
'.' + i.name -- index name
FROM sysobjects o
JOIN sysindexes i
ON (o.id = i.id)
WHERE o.xtype = 'U'
AND i.rows > 0
ORDER BY object_name(o.id),
i.indid
GO
/* Partial results
(No column name)
DBCC SHOWCONTIG (21575115,1) -- Categories.PK_Categories
DBCC SHOWCONTIG (21575115,2) -- Categories.CategoryName
DBCC SHOWCONTIG (53575229,1) -- Customers.PK_Customers
DBCC SHOWCONTIG (53575229,2) -- Customers.City
DBCC SHOWCONTIG (53575229,3) -- Customers.CompanyName
DBCC SHOWCONTIG (53575229,4) -- Customers.PostalCode
*/
Related Link on REBUILD indexes:
http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/
Related article:
Are you using SQL's Missing Index DMVs?
|
