Index Utilization Summary

Question

Index Utilization Summary

Devendra Thakur

SSCommitted

Points: 1762
More actions
April 11, 2017 at 1:04 pm

#320381

Comments posted to this topic are about the item Index Utilization Summary

Viewing 5 posts - 1 through 4 (of 4 total)

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

Budd Hall of Fame Points: 3781 More actions · Answer 1

Very nice.
I needed a minor alteration.
LEFT JOIN sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)IOS ON Indx.object_id=IOS.object_id AND Indx.index_id=IOS.index_id AND IUS.database_id=IOS.database_id
WHERE(Obj.type_desc='USER_TABLE' OR Obj.type_desc='VIEW')
AND ius.database_id = DB_ID()

Lee Linares SSCrazy Points: 2727 More actions · Answer 2

Thank you for taking the time to share this with us. I made 2 minor changes though to restrict the output to just the current database:
1. I changed this:

sys.dm_db_index_usage_stats IUS ON Indx.index_id=IUS.index_id AND IUS.object_id=Obj.object_id

to this :

sys

.dm_db_index_usage_stats IUS ON Indx.index_id=IUS.index_id AND IUS.object_id=Obj.object_id AND IUS.database_id = DB_ID()

2. I changed this:

sys.dm_db_index_operational_stats (NULL,NULL,NULL,NULL )

to this:

sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL )

Thanks again.

Lee

VoldemarG Hall of Fame Points: 3883 More actions · Answer 3

Question: if everything (each column) from the resultset of the original index-utilization qry
if all columns to the right of has_filter are NULLs -- does it means that the index IS NOT USED AT ALL?
also, why database id and object id would be NULL ? table listed in col 1, index name in col 3 of the result-set -- so why db id and obj id would be nulls, really?

THANK YOU SO MUCH in advance for possible dispersing of my doubts and confusions...

Vlad

Likes to play Chess

jonas.gunnarsson 52434 Ten Centuries Points: 1240 More actions · Answer 4

Nice,
Also add schema, first in select
"Schema" = object_schema_name(Obj.object_id),
And may be the following after the inner join sys.objects Obj on Obj.object_id=Indx.object_id line:
and Obj.name <> 'sysdiagrams'