Stairway to Exploring Database Metadata Level 3: Exploring Indexes

  • Phil Factor

    SSCoach

    Points: 19959

    Comments posted to this topic are about the item Stairway to Exploring Database Metadata Level 3: Exploring Indexes

    Best wishes,
    Phil Factor
    Simple Talk

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    This is one of the best, all in one place, articles about indexes I've ever read. Thanks so much.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    I love the script about indexes that haven't been read.

  • Jon Brune

    Old Hand

    Points: 323

    I think maybe there is one bug? Perhaps the script should also be screening out clustered indexes.

  • Phil Factor

    SSCoach

    Points: 19959

    @Jon

    It is quite possible! Can you please tell me which script? I'll check it.

    Best wishes,
    Phil Factor
    Simple Talk

  • Jon Brune

    Old Hand

    Points: 323

    D'oh. I forgot there were so many queries on that post, sorry.

    --Indexes updated but not read.

    SELECT

    object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) as Thetable,

    i.name AS 'Index'

    FROM sys.indexes i

    left outer join sys.dm_db_index_usage_stats s

    ON s.object_id = i.object_id

    AND s.index_id = i.index_id

    AND s.database_id = DB_ID()

    WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1

    AND i.index_id > 0 --Exclude heaps.

    AND i.is_primary_key = 0 --and Exclude primary keys.

    AND i.is_unique = 0 --and Exclude unique constraints.

    AND coalesce(s.user_lookups + s.user_scans + s.user_seeks,0) = 0 --No user reads.

    AND coalesce(s.user_updates,0) > 0 --Index is being updated.

    AND i.type_desc <> 'CLUSTERED' --Maybe add this here so people don't drop clustereds?

  • Phil Factor

    SSCoach

    Points: 19959

    @Jon

    That's an interesting thought. Thanks for that. I must confess that I've never come across this happening. Would anything go wrong if that table that had a clustered index that wasn't the primary key and didn't even have a unique constraint and that was never used then became a heap? Wouldn't people want to know that a table had such an unusual choice for a clustered index that was unused even thought the table was being updated? I'll check that with my editor, who knows a lot more about that than I.

    Best wishes,
    Phil Factor
    Simple Talk

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply