SQL Server Performance

dm_db_index_physical_stats taking long time to retrieve

Discussion in 'SQL Server 2005 General DBA Questions' started by AJITH123, Aug 4, 2011.

  1. AJITH123 Member

    Hi Guys

    I have an issue with dm_db_index_physical_stats, it is taking long time (around 30 Min) to retrive the result against a table having 50 Million records. Can you suggest which is the best approach to get the fragmentation level other than this method? The database size is more than 2TB.
  2. satya Moderator

    It is expected depending upon the number of indexes on that table,.
    How frequently you are performing REORG and REINDEX on thsi table?
  3. AJITH123 Member

    Satya,
    The table have only one index, however some of the tables in the database is huge..The database is more than 2TB. so now other than the below mentioned query can we find out the fragmentation? or can we modify the qury? or isther any sqlcmd command to give the same?

    SELECT
    DISTINCTOBJECT_NAME(object_id)
    NAME

    FROM sys.dm_db_index_physical_stats(DB_ID(@DBNAME),NULL,NULL,NULL,'DETAILED')

    WHERE
    avg_fragmentation_in_percent >

    30

    ANDOBJECT_NAME(object_id)='Note'
  4. FrankKalis Moderator

  5. satya Moderator

    When you are asking for a detailed informatio the process is expected to take long time depending upon the other processes that are trying to access the same table and resources of the system.

    When you can continue the database optimization jobs such as REORG or REINDEX of highly accessed tables the performance loss can be controlled, still if you see that its a problem then code assesment is only way to find the root cause.

Share This Page