Thread: a scan is a scan, but....
08-29-11, 16:26 #1Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
Unanswered: a scan is a scan, but....
So I just go this job running daily on my transactional server that sums up Index Seeks, Index Scans, Index Lookups, Index Updates etc....
I got this set up with the thought that I was going to drop all indexes that have zero seeks over the course of 90 days. But now I am thinking about this. In terms of a single querys performance, my old mantra is that a SCAN is a SCAN whether it be a Table or Index because a read is a read and it has to go through them all, right? Well maybe less so with filtered indexes. However what about system wide performance? If the Indexes were not being scanned, the tables would have to be for all scanning operations. If you have queries doing scans on large tables and indexes, which is bad I know, would not having the index there to absorb some of those scans cause further contention on the tables?
I dunno. Maybe I am just rambling.“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
08-29-11, 17:24 #2Registered User
- Join Date
- Mar 2007
- Holmestrand, Norway
Technically speaking, a scan is, well... a scan. But, there are huge differences. Can you imagine a table with a clustered index of some 20000 pages, having a nonclustered index of 500 pages. Which table would you prefer being scanned?
Furthermore, let's say that this is not the main table in your system, and you join this table with several others. Statistics may tell SQL Server that it either have to look up 2000 values (ie on average 4 lookups per page) against this nonclustered index of yours. What is most effective, 2000 lookups or a scan of 500 pages. Quite honestly, I've not come to the point where I know the crossover yet, but at some point it becomes more effective to perform a scan than doing lookups.
If you want to play with it, take a query that joins with merge or hash match, and use the loop query hint to force loop join (essentially forcing index seek instead), and you may see that the query performance drops like a stone.
It's not just rambling, you're starting to look into a huge topic covering the internals of indexes, how they are used, statistics, database structures, file internals and more. I can really recommend the MCM Videos on these topics, but beware, you may get crazy at some point. For me it was too late anyway, so I'm just going for it