Quickie Script – Create Index Rebuild or Reorg Commands

The scenario here is, you’ve just performed some massively fragmenting operation, like a shrink.  (Don’t look at me like that – it happens, just don’t do it as regular maintenance…) You know you need to get in and do some index maintenance. You could run your nightly index maintenance job, but in this case, you want to do this now, instead of waiting for off hours or running that job in the middle of the day.  Furthermore, you want to see what indexes need maintenance and maybe run some of the commands independently.  Here’s a quick script to make that easy for you.  It outputs the table and index name, page count, and percent fragmentation, as well as a one line command to rebuild or defrag the index, depending on what thresholds you want to use for that.


  Quick script to create index defrag / rebuild commands.
  Useful for when you KNOW you have the work to do. 
  Not so useful for ongoing index maintenance, and should
  not be used for such.  

  The usual cautions about running this in production, 
  fitness for a particular purpose, or guarantee that this
  will fix anything apply. 

  DMMaxwell, October 2012.


   TableName = SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ips.object_id)
  ,IndexName = ix.name 
  ,Pages = ips.page_count 
  ,Fragmentation = ips.avg_fragmentation_in_percent 
  ,ReindexCmd = 'ALTER INDEX [' + ix.name + '] ON [' + 
    SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME(ips.object_id) + '] ' + 
      WHEN ips.avg_fragmentation_in_percent > 15 THEN 'REBUILD; ' 
        /* BOL recommendation is 30 - being more aggressive. */
      WHEN ips.avg_fragmentation_in_percent > 5 THEN 'REORGANIZE; '  
        /* BOL recommendation I actually like. */
FROM sys.dm_db_index_physical_stats(
  DB_ID('AdventureWorks'),  /* Your database name here. */
  NULL, /* Specify object ID or NULL for all of them.*/
  NULL, /* Specify index ID or NULL for all of them.*/
  NULL, /* Specify partition ID or NULL for all of them.*/
  'LIMITED' /* Going for speed, rather than depth of info. */
  ) ips
INNER JOIN sys.tables t
  ON ips.object_id = t.object_id
INNER JOIN sys.indexes ix 
  ON ips.object_id = ix.object_id
 AND ips.index_id = ix.index_id
WHERE ips.page_count > 500  
        /* Usually 1000 */
  AND ips.avg_fragmentation_in_percent > 5 
        /* Ignore fragmentation less than 5%. */
        /* These numbers vary, based on a multitude of factors. 
           I'm being a bit aggressive today. */
  AND ips.index_type_desc != 'HEAP' 
        /* Can't defragment a heap... */

Thanks for reading.


Please Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.