August 22, 2016 at 10:49 am
Comments posted to this topic are about the item Exploring SQL Server Indexes: Stairway to Exploring Database Metadata Level 3
Best wishes,
Phil Factor
September 28, 2016 at 6:44 am
This is one of the best, all in one place, articles about indexes I've ever read. Thanks so much.
September 28, 2016 at 6:44 am
I love the script about indexes that haven't been read.
September 29, 2016 at 8:17 am
I think maybe there is one bug? Perhaps the script should also be screening out clustered indexes.
September 29, 2016 at 10:04 am
@Jon
It is quite possible! Can you please tell me which script? I'll check it.
Best wishes,
Phil Factor
September 29, 2016 at 10:13 am
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.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?
September 29, 2016 at 11:06 am
@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
August 5, 2020 at 11:12 am
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
August 28, 2020 at 12:51 am
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,
Tom
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy