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      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?       FROM
sys.dm_db_index_physical_stats(DB_ID(@DBNAME),NULL,NULL,NUL