Comments posted to this topic are about the item Exploring SQL Server Indexes: Stairway to Exploring Database Metadata Level 3
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.
It is quite possible! Can you please tell me which script? I'll check it.
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,
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?
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.
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
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)