BOL - Where to find Details on info returned by SYS.DM_DB_INDEX_USAGE_STATS

Question

BOL - Where to find Details on info returned by SYS.DM_DB_INDEX_USAGE_STATS

YSLGuru

SSC-Insane

Points: 21632
More actions
April 6, 2010 at 10:51 am

#143911

Does anyone know where in BOL or somehwre else like MSDN, one can find detailed explinations on the 4 various metrics tracked by SYS.DM_DB_INDEX_USAGE_STATS , specifically the colimns/fields
User_Seeks, User_Scans, User_Lookups, User_Updates
I know what I believe each does but I'd like to see something offical in BOL and so far I'm not seeing anything that explains what each of these tracks. Some of the items like thge 'Last_xxxxx' are easier to follw since they are dates and the use of 'Last' implies the last or most recent but the explinations of the above 4 are lacking in any explination.
Thanks
Kindest Regards,
Just say No to Facebook!

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

YSLGuru (4/6/2010)
Does anyone know where in BOL or somehwre else like MSDN, one can find detailed explinations on the 4 various metrics tracked by SYS.DM_DB_INDEX_USAGE_STATS , specifically the colimns/fields
User_Seeks, User_Scans, User_Lookups, User_Updates
I know what I believe each does but I'd like to see something offical in BOL and so far I'm not seeing anything that explains what each of these tracks. Some of the items like thge 'Last_xxxxx' are easier to follw since they are dates and the use of 'Last' implies the last or most recent but the explinations of the above 4 are lacking in any explination.
Thanks

I hope I'm confused.

Is this what you're looking for?

"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

mangeshd Right there with Babe Points: 744 More actions · Answer 2

http://msdn.microsoft.com/en-us/library/ms188755.aspx

Are you not happy with the information presented here in BOL:

<paste>

Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

</paste>

YSLGuru SSC-Insane Points: 21632 More actions · Answer 3

Grant Fritchey (4/6/2010)
YSLGuru (4/6/2010)
Does anyone know where in BOL or somehwre else like MSDN, one can find detailed explinations on the 4 various metrics tracked by SYS.DM_DB_INDEX_USAGE_STATS , specifically the columns/fields
User_Seeks, User_Scans, User_Lookups, User_Updates
I know what I believe each does but I'd like to see something offical in BOL and so far I'm not seeing anything that explains what each of these tracks. Some of the items like thge 'Last_xxxxx' are easier to follw since they are dates and the use of 'Last' implies the last or most recent but the explinations of the above 4 are lacking in any explination.
Thanks
I hope I'm confused.
Is this what you're looking for?

Thanks for the link Grant. When I click on the link in BOL on mys sytem for this VIEW I get a different page that has nothing on it but the name of the thing. I thought here was something else with a lot more text that listed every piece of data this thing returned. I think my copy of BOL is messed up.

Thanks

Kindest Regards,

Just say No to Facebook!

dmoldovan SSCertifiable Points: 6334 More actions · Answer 4

Under which circumstances is it possible that several records exist in sys.dm_db_index_usage_stats for the same index?

YSLGuru SSC-Insane Points: 21632 More actions · Answer 5

dmoldovan (4/9/2010)
Under which circumstances is it possible that several records exist in sys.dm_db_index_usage_stats for the same index?

I'm confused by your question, did you post this tothe wrong thread perhaps? There's nothing in my original post atht I can read that says anything about multiple records for the same index.

Kindest Regards,

Just say No to Facebook!

dmoldovan SSCertifiable Points: 6334 More actions · Answer 6

YSLGuru (4/11/2010)
dmoldovan (4/9/2010)
Under which circumstances is it possible that several records exist in sys.dm_db_index_usage_stats for the same index?
I'm confused by your question, did you post this tothe wrong thread perhaps? There's nothing in my original post atht I can read that says anything about multiple records for the same index.

I'm sorry for this inconvenience, I've joined this post because you request info about sys.dm_db_index_usage_stats.