August 15, 2010 at 4:02 pm
I'm playing around the index_usage_stats DMV to learn it. there are two fields: user_seek and user_scan. The second made me wonder if an index scan is the same as a table scan because it's going through every rows in a table. How is it different.
Thanks!
August 15, 2010 at 5:07 pm
An index scan isn't the same as a table scan, unless you are looking at the cluster index, in which case they could be the same, since the cluster index is the logical order of the table.
You would typically get an index scan if you have a WHERE clause that uses the 2nd or subsequent collumn of an index, particularly if the result set is also part of the index.
Since this dmv is particularly related to indices, it will only reflect index scans, including cluster index scans, but not table scans.
If you are learning anbout these dmvs, it's worth while creating a test database on a test server and seeing what happens and how the counters change as you run different queries. If you restart SQL you flush these dmvs, so you can test queries that do table scans, cluster index scans etc and see what gets reported where.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 15, 2010 at 11:32 pm
SQL Iron Chef (8/15/2010)
The second made me wonder if an index scan is the same as a table scan because it's going through every rows in a table. How is it different.
Table scan = read of all of the pages of the table
Index scan = read of all of the pages of an index.
If the index in question is the clustered index (index_id = 1), the a scan of the index is a table scan, seeing as the clustered index is the table. For the nonclustered indexes, the index is a separate structure from the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply