none
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

     

     

    Thursday, December 11, 2008 1:04 PM

All replies