February 25, 2010 at 8:55 am
Hi folks,
I am learning index:
here is what I found in DMV:
object_idindex_iduser_seeksuser_scansuser_lookups
8695731865200
8695731861280
10775788772020
10775788771500
20661064012020
20661064011500
535752293020
535752291500
3095761411700
How do I determine which nonclustered index is being used when the entire index is not read?
Thank you very much.
February 25, 2010 at 11:07 am
I'm sorry, I just don't understand what you're asking.
You're listing the various indexes, by object id, and showing the aggregate access methods that have been used on these indexes. If you want to get the index name, you can use the object_id and index_id like this:
SELECT s.name + '.' + t.name AS TableName
,i.name AS IndexName
,i.index_id
,ddius.user_seeks
,ddius.user_scans
,ddius.user_updates
,ddius.user_lookups
FROM sys.dm_db_index_usage_stats AS ddius
JOIN sys.tables AS t
ON ddius.object_id = t.object_id
JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
JOIN sys.indexes AS i
ON ddius.index_id = i.index_id
AND ddius.object_id = i.object_id
ORDER BY TableName
,ddius.index_id
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2010 at 11:12 am
I don't understand your question, but the DMV to use is sys.dm_db_index_usage_stats. If you are trying to figure out what indexes might be missing I have a procedure for you. If you are trying to figure out what indexes are not being used I have another procedure for you. However, if you are trying to figure out what indexes are being used in conjunction with/without other indexes then you will need to examine execution plans.
Regards,
Toby
February 25, 2010 at 1:02 pm
:hehe::hehe::hehe:
This is confusing me: the question is actually from a quiz. I just started learning Index. I don't understand the answer: obj_id = 869578136, index_id = 5
February 25, 2010 at 1:34 pm
Oh, I get it now.
It's asking, based on the data provided, which index is only taking part in seek operations. It means the index that doesn't have any scans. Look at the column user_scans. The table and index you mention is the only one with zero there that is also an ID greater than 1, meaning a nonclustered index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 25, 2010 at 2:12 pm
Thank you
March 4, 2010 at 3:24 am
mdivk (2/25/2010)
:hehe::hehe::hehe:This is confusing me: the question is actually from a quiz. I just started learning Index. I don't understand the answer: obj_id = 869578136, index_id = 5
It means you need to spend some time to learn indexes
read below post and related series
http://www.sqlservercentral.com/articles/Indexing/68439/
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply