Post reply

dm_db_index_physical_stats not responding

  • January 13, 2016 at 8:38 am

    #303748

    i have a problem with my script to detect the Index Stats Is to Slow, know why ?

    SELECT GETDATE(), db_name(ps.database_id), object_name (ps.OBJECT_ID), ps.index_id, b.name, ps.avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

    AND ps.index_id = b.index_id

    AND avg_fragmentation_in_percent > 0

    WHERE ps.database_id = DB_ID()

    OR

    SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag

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

    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

  • January 13, 2016 at 8:45 am

    #1851600

    Yes - every time you use dm_db_index_physical_stats, SQL Server has to go away and examine the indexes to find out their fragmentation level. Even though you've filtered on those with more than 10%, it still has to look at every index to know which ones have that level of fragmentation. That's the most likely explanation given the information you've provided, anyway.

    John

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply