locked
Find index fragmentation for only 1 index RRS feed

  • Question

  • Hi All, I need a query to find index fragmentation for ONLY one index. I know the below query for one table: SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N''), OBJECT_ID(N''), NULL, NULL , 'DETAILED') As the table is very big, when I used Management Studio to find fragmentation for the specific index, it hangs...any solution???
    • Moved by Tom Phillips Monday, December 12, 2011 1:24 PM TSQL question (From:SQL Server Database Engine)
    Monday, December 12, 2011 11:12 AM

Answers

  • SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
      DB_ID('testdb'),
      OBJECT_ID('dbo.T1'),
      1,
      NULL,
      NULL
    );

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Kalman Toth Friday, December 16, 2011 3:43 PM
    Monday, December 12, 2011 11:34 AM
    Answerer
  • Hi,

    In the query below you have given NULL for the INDEX_ID, the reason why you are getting the fragmentation for all indexes. You need to find the index id and then run the query by specifying the indexid

    SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N''), OBJECT_ID(N''), NULL, NULL , 'DETAILED')

    it would look similar to

    SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N''), OBJECT_ID(N''), 1, NULL , 'DETAILED')

     

    to get the index id you can use the below query

    select * from sys.indexes where object_id = OBJECT_id('table_name')

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    • Proposed as answer by Naomi N Monday, December 12, 2011 2:54 PM
    • Marked as answer by Kalman Toth Friday, December 16, 2011 3:43 PM
    Monday, December 12, 2011 11:48 AM

All replies

  • Try use LIMITED or default

    declare @ixId int
    select @ixId=indid from sys.sysindexes where id=OBJECT_ID('TableName') and name='inx_name'
    
    SELECT * FROM sys.dm_db_index_physical_stats (DB_ID('db_name'), OBJECT_ID('TableName'), @ixId, NULL , default) 
    


     

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Monday, December 12, 2011 11:33 AM
  • SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
      DB_ID('testdb'),
      OBJECT_ID('dbo.T1'),
      1,
      NULL,
      NULL
    );

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Kalman Toth Friday, December 16, 2011 3:43 PM
    Monday, December 12, 2011 11:34 AM
    Answerer
  • Hi,

    In the query below you have given NULL for the INDEX_ID, the reason why you are getting the fragmentation for all indexes. You need to find the index id and then run the query by specifying the indexid

    SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N''), OBJECT_ID(N''), NULL, NULL , 'DETAILED')

    it would look similar to

    SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N''), OBJECT_ID(N''), 1, NULL , 'DETAILED')

     

    to get the index id you can use the below query

    select * from sys.indexes where object_id = OBJECT_id('table_name')

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    • Proposed as answer by Naomi N Monday, December 12, 2011 2:54 PM
    • Marked as answer by Kalman Toth Friday, December 16, 2011 3:43 PM
    Monday, December 12, 2011 11:48 AM