Check SQL Server a specified database index fragmentation percentage (SQL)

This T-SQL sample script illustrates how to check index fragmentation of a specified database in SQL Server.

 
 
 
 
 
4 Star
(57)
37,879 times
Add to favorites
Databases
7/29/2013
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Contribution
    5 Posts | Last post April 12, 2016
  • script only running for Master database
    2 Posts | Last post January 20, 2016
    • how to execute the script against all database at once?
      need to view the frag. indexes for all databases on an instance.
    • You can use the procedure sp_msforeachdb.
  • Script needs updation
    2 Posts | Last post May 09, 2015
    • You must include page_count value in script. You must filter only Indexes which has page_count >1000 otherwise when user will rebuild index its likely fragmentation would be there and he might get confused. Plus there is no point in rebuilding index which has so less pages.
      
      SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
      ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
      indexstats.avg_fragmentation_in_percent 
      FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
      INNER JOIN sys.indexes ind  
      ON ind.object_id = indexstats.object_id 
      AND ind.index_id = indexstats.index_id 
      WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count >1000
      ORDER BY indexstats.avg_fragmentation_in_percent DESC
    • Shanky_621 added indexstats.page_count is very important.
  • Getting an error - Any help would be appreciated
    3 Posts | Last post January 20, 2014
    • I get the following error when trying to run this query.  I am using SQL Server 2008.
      
      Msg 102, Level 15, State 1, Line 4
      Incorrect syntax near '('.
      
      It seems to work fine when I run it against the master database, but I would like to be able to run it against another one.  
    • Apparently the database I am trying to get index information on is set for compatibility level 80 as well.  I think this may be part of my problem?
    • Yes, The DMV queries will not work with compatibility mode 80. That means, will not work on SQL Server 2000 and will work only the versions from SQL Server 2005 and above.
  • Does the above scripts consist logs truncation ?
    2 Posts | Last post August 07, 2013
    • can i combine both truncate scripts and fragmentation into one ?
    • Hi Guowen,
      
      Could you provide more information about truncate scripts? Thank you.