Find fragmentation info of all indexes/tables in a database

Question

Hello all,

How can I find fragmentation information of all indexes/tables in a database without using dynamic management views?

Thanks.

Answer 1

Hi,

Try this 

select * from sys.dm_db_index_physical_stats(db_id('databasename'),null,null,null,null) where where avg_fragmentation_in_percent>15

---

Thanks and regards, Rishabh

Answer 2

Isn't dm_db_index_physical_stats a dynamic management view? I'm looking for a way without using them.

Thanks.

 

Answer 3

Check this blog post

http://www.brentozar.com/archive/2009/02/index-fragmentation-findings-part-1-the-basics/

---

For every expert, there is an equal and opposite expert. - Becker's Law

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

Check out the following article on index fragmentation reporting:

SELECT object_name(IPS.object_id) AS [TableName],
   SI.name AS [IndexName],
   IPS.Index_type_desc,
   IPS.avg_fragmentation_in_percent,
   IPS.avg_fragment_size_in_pages,
   IPS.avg_page_space_used_in_percent,
   IPS.record_count,
   IPS.ghost_record_count,
   IPS.fragment_count,
   IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5

http://www.mssqltips.com/tip.asp?tip=1708

 

---

Kalman Toth, SQL Server & BI Training; SQLUSA.com

Answer 5

HI,
   this is the code for finding fragmentation status of indexes on a particular table

SELECT DB_NAME(ps.[database_id]) AS [database_name], 
    OBJECT_NAME(ps.[object_id],
    DB_ID()) AS [object_name], 
    si.[name] AS [index_name],
    ps.[index_type_desc],
    ps.[avg_fragmentation_in_percent],
    ps.[page_count] 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
   JOIN sys.sysindexes si
   ON ps.OBJECT_ID = si.id 
   AND ps.index_id = si.indid 
WHERE index_type_desc <> 'HEAP' 
ORDER BY OBJECT_NAME(ps.[object_id], DB_ID()), index_id

hope this helps...............

---

Regards, Tejas Patil

Answer 6

HI,
   this is the code for finding fragmentation status of indexes on a particular table

SELECT DB_NAME(ps.[database_id]) AS [database_name], 

  OBJECT_NAME(ps.[object_id],

  DB_ID()) AS [object_name], 

  si.[name] AS [index_name],

  ps.[index_type_desc],

  ps.[avg_fragmentation_in_percent],

  ps.[page_count] 

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps

  JOIN sys.sysindexes si

  ON ps.OBJECT_ID = si.id 

  AND ps.index_id = si.indid 

WHERE index_type_desc <> 'HEAP' 

ORDER BY OBJECT_NAME(ps.[object_id], DB_ID()), index_id

hope this helps...............

---

Regards, Tejas Patil

This query returns the list of CLUSTERED and NON CLUSTERED indexes with their page count and Freagmentation Percent.

Hope this helps,

Jaspatil

---

Regards, Tejas Patil