Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: reviewing Index usage

    Any good queries to assist me at a new client with regards to reviewing their current index usage possibly using sys.dm_db_index_usage_stats? I have noticed on some tables at a cursory glance that they have an index on a singular column for every column in the table. Also looking at the disk usage it appears the index size is 3x the data size. Client is currently running a purge job every (DELETE statements on 4 tables) every 30 minutes and are reindexing all of their indexes nightly which seems excessive. I might have a solution regarding a more effective purge routine using table partitioning which I think will reduce the frequency in which indexes need to be reindexed to perhaps weekly and will also hopefully reduce the size of their transaction log backups which occur every 30 minutes.

    But back to the original question, just looking at the indexes are there any safe approaches to select indexes to drop? I ran a query to review any "unused" indexes and only came up with 2, but not sure how solid my query is, shown below:

    Code:
    DECLARE @dbid INT
    SELECT @dbid = DB_ID(DB_NAME())
    SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME = I.NAME,
    I.INDEX_ID
    FROM SYS.INDEXES I
    JOIN SYS.OBJECTS O
    ON I.OBJECT_ID = O.OBJECT_ID
    WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
    AND I.INDEX_ID NOT IN (
    SELECT S.INDEX_ID
    FROM SYS.DM_DB_INDEX_USAGE_STATS S
    WHERE S.OBJECT_ID = I.OBJECT_ID
    AND I.INDEX_ID = S.INDEX_ID
    AND DATABASE_ID = @dbid)
    ORDER BY OBJECTNAME,
    I.INDEX_ID,
    INDEXNAME ASC
    GO

  2. #2
    Join Date
    Jun 2005
    Posts
    319

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •