Comments posted to this topic are about the item Index Utilization Summary
Hall of Fame
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()
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 :
.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 )
sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL )
Hall of Fame
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...
Likes to play Chess
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'
Viewing 5 posts - 1 through 4 (of 4 total)