SQL Server Performance

Only Rebuilding and Reorganizing USED indexes

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by biged1234567, Aug 17, 2008.

  1. biged1234567 New Member

    Greetings all, I've found some pretty good indexing scripts out there that use the dm_db_index_physical_stats view to determine which indexes are fragmented and need to be either rebuilt or reorg'd. I also read up on dm_db_index_usage_stats, which looks like it can give us information on if indexes are being used or not. I was going to write something that
    1 - ensured SQL had been up for some time (7 or more days of uptime is a good sample size for my server)
    2 - gathered info on un-used indexes using index_usage_stats
    3 - gather info on index framentation
    4 - rebuilds or re-orgs indexes that are fragmented AND are used
    5 - write unsed index information to the event logs for review
    I may keep the 2 scripts/jobs seperate and write dm_db_index_usage_stats query to write to a table over longer periods of time to be sure about the index usage. Does this logic make sense? Anyone seen anything simular to this already? I want to ensure I am not spending cycles rebuilding/re-orging indexes that aren't even being used. Thanks in advance!
  2. satya Moderator

Share This Page