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

Question

Post reply

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

Phil Factor

SSC-Insane

Points: 20244
More actions
August 22, 2016 at 10:49 am

#312646

Comments posted to this topic are about the item Exploring SQL Server Indexes: Stairway to Exploring Database Metadata Level 3
Best wishes,
Phil Factor

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

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

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 More actions · Answer 2

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

Jon Brune Old Hand Points: 373 More actions · Answer 3

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

Phil Factor SSC-Insane Points: 20244 More actions · Answer 4

@Jon

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

Best wishes,
Phil Factor

Jon Brune Old Hand Points: 373 More actions · Answer 5

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?

Phil Factor SSC-Insane Points: 20244 More actions · Answer 6

@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

WJBergen SSC Veteran Points: 274 More actions · Answer 7

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

Tom LeBlanc SSC Rookie Points: 36 More actions · Answer 8

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