Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 339

How to do it...

The following steps are required in implementing sustainable index maintenance practices for
a VLDB and 24/7 environment:

1. The first step in the index maintenance practice is to identify the indexes that require
maintenance or indexes that are heavily fragmented.

2. SQL Server provides a data management view (DMV) sys.dm_db_index_

physical_stats that is a multi-statement table-valued function that returns size
and fragmentation information for data and indexes of a specified table or view.

3. Execute the following TSQL statement to obtain index fragmentation levels on

[Person].[Sales] table in AdventureWorks2008R2 database:
USE Adventureworks2008R2
GO
--Obtain DETAILED fragmentation information
select str(index_id,3,0) as indid,
left(index_type_desc, 20) as index_type_desc,
index_depth as idx_depth,
index_level as idx_level,
str(avg_fragmentation_in_percent, 5,2) as avg_frgmnt_pct,
str(page_count, 10,0) as pg_cnt
FROM sys.dm_db_index_physical_stats
(db_id(), object_id('person.sales'),null, 0, 'DETAILED')
go

4. The previous script gives the information of fragmented indexes on a specific table,
and to obtain all the fragmented tables information, execute the following:
--Return the information index operational stats
--related to current low level I/O, locking, latching and access
method
SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL,
NULL);
GO

--DETAILED index fragmentation report for all the tables in the
database
--Ensure to change DB_ID value in below query as per the database
SELECT object_name(IdxPhyStat.object_id) AS [TableName],
SysIdx.name AS [IndexName],
IdxPhyStat.Index_type_desc,
IdxPhyStat.avg_fragmentation_in_percent,
IdxPhyStat.avg_page_space_used_in_percent,
IdxPhyStat.record_count,
IdxPhyStat.fragment_count,