confuse with result in dm_db_index_usage_stats

Question

confuse with result in dm_db_index_usage_stats

randy.mata

SSC Rookie

Points: 47
More actions
June 1, 2010 at 1:07 am

#223210

Hi Guys,
I'm randy, i'm quite new in the SQL 2008, i just wondering if someone can help on explaining further the result i get when i execute a query in the above mentioned usage_stats table, specially on the user_seek and user_scan column what is the difference between the 2.
Appreciate all the explanation, feedback and comments
I've attached a partial example of the data.

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

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

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 1

It is basically number of seeks and scans performed on the particular index.

If you don't know the difference between index seeks and scans? please read this article. this is just one article about indexes, search for Index scans and seeks in SSC you will get lots of good articles read them that will help you.

http://qa.sqlservercentral.com/articles/Indexing/68439/

Nag

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

randy.mata SSC Rookie Points: 47 More actions · Answer 2

Thank you Nagesh, i will search the articles then, i hope i can understand fully the meaning of both words. Thanks once again.

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 3

your welcome Randy, the article mentioned in my last reply will help you to kick start.

In simple words seek means in a library you know the location of book which you're searching for i mean which row and so on, so you will just go there and get it (Seek).

Scan means you are searching whole library to find the book you need.

Same thing happens in the index pages databases to get data from data and index files.

I am just comparing it to real world to make it simple, I mean to make you understand concept. Hope this will be informative to you.

Nag

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

randy.mata SSC Rookie Points: 47 More actions · Answer 4

Thanks Nagesh, let me take this opportunity to ask 1 more thing, is there a way to let my query use the seek instead of the scan, or is it the system who handle this? 🙂

Nagesh S-432384 SSCrazy Points: 2231 More actions · Answer 5

Yes you can by adding proper indexes, but again the behavior of index and what your query is doing and look into the execution plans then you can decided on which column you need add index and also you can use hints to force use index but it is not recommended, you can use hints if you are very good at it.

It's a huge concept if you understand the basics of index and how data is fetched from database then you will be able to decided.

Nag

Nag
------------------------------------------------
How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537