Exploring SQL Server Indexes: Stairway to Exploring Database Metadata Level 3

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

    Best wishes,
    Phil Factor

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

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

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

  • @Jon

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

    Best wishes,
    Phil Factor

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

    --Indexes updated but not read.


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

    i.nameAS '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?

  • @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

  • Mr. Factor

    Your work is truly brilliant and outstanding and much needed.  How about putting all your hard work into a book (I am old so I like books) and a single online site where we can grab the scripts we need.  It would be a great help and speed up our object analysis.

    thanks  in advance

    Bill Bergen

    Bill Bergen

  • Thanks for the article and the code. I have a question about the final query on duplicate statistics: what do we do with that information?

    I ran it against a database that wasn't written in-house and I have a list of 429 entries with duplicate or triplicate matches on columns.

    In looking at one of the table's statistics folder there is a statistic with a name that matches the index and one of the system generated _WA_Sys statistics. Should I delete one of them? If so, which one?

    Thanks in advance,


Viewing 9 posts - 1 through 8 (of 8 total)

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