Slow performance of sys.dm_db_index_physical_stats()

Question

Hi,

 

The DMV function sys.dm_db_index_physical_stats() is performing like a dog. Currently it is accounting for 96% of the workload according to the execution plan.

 

I want to evaluate all of the indexes within a given database however this is proviing very difficult. Database has 200+ tables and is 30GB in size.

 

Is there a more efficient way of structuring this query or is this a lost cause?

 

Code Snippet

WITH cte AS

(

SELECT

@DatabaseName AS database_name,

@DatabaseID AS database_id,

OBJECT_NAME(dt.object_id) AS table_name,

dt.OBJECT_ID AS table_id,

--sch.name AS scheme_name,

si.name AS index_name,

dt.index_id AS index_id,

dt.index_depth,

dt.index_level,

sc.column_name AS index_columns,

dt.index_type_desc,

dt.alloc_unit_type_desc,

dt.avg_fragmentation_in_percent,

dt.avg_fragment_size_in_pages,

dt.page_count,

dt.record_count,

dt.avg_page_space_used_in_percent,

si.object_id,

si.type_desc

FROM

sys.dm_db_index_physical_stats(@DatabaseID, NULL, NULL, NULL, @ScanMode) dt

INNER JOIN sys.indexes si

ON si.object_id = dt.object_id

INNER JOIN sys.index_columns ic

ON si.object_id = ic.object_id AND si.index_id = ic.index_id

INNER JOIN information_schema.tables st

ON OBJECT_NAME(si.object_id) = st.table_name

INNER JOIN information_schema.COLUMNS sc

ON ic.column_id = sc.ordinal_position AND sc.table_name = OBJECT_NAME(dt.object_id)

WHERE

si.index_id = dt.index_id

AND index_level = 0

AND dt.index_id <> 0 --index object is not a heap.

AND avg_fragmentation_in_percent >= @FragThreshold

AND si.index_id >0 AND si.is_hypothetical = 0

)

SELECT DISTINCT

database_name,

database_id,

table_name,

table_id,

index_name,

index_id,

index_depth,

index_level,

index_columns = SUBSTRING(

(

SELECT ', ' + index_columns

FROM cte c1

WHERE c1.table_name = c2.table_name

AND c1.index_name = c2.index_name AND c1.type_desc = c2.type_desc

FOR XML PATH('')

),3,1000),

index_type_desc,

alloc_unit_type_desc,

avg_fragmentation_in_percent,

avg_fragment_size_in_pages,

page_count,

record_count,

avg_page_space_used_in_percent

INTO tmp_ResultSet

FROM cte c2

 

 

Answer 1

It is a known issue that sys.dm_db_index_physical_stats can have performance implications based on db size and level of detail, it is stated in BOL.  Best bet is to run this in non-peak hours (assuming that exists in your environment) when it can safely churn through without impacting users.

Answer 2

John,

In addition to Whitney's comment, you may consider using the LIMITED scanning mode, that is the fastest.

The following article is on query optimization:

http://www.sqlusa.com/articles/query-optimization/ 

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 3

Hi,

Thanks for your comments guys. So are we saying that there is nothing more I can do to optimize this query?

---

John Sansom (MCITP) | Please mark answers that solve your problem