If in SqlProfiler you can see that to execute a query a Scan is Started, does this mean a full table scan or can it just be a lookup? If it can be both, how can you tell which one of the two it is?
+3
A:
From the documentation:
The Scan:Started event class occurs when a table or index scan is started.
So it could be either one. The IndexID field will tell you if it is an index, and which one.
Not that it really matters very much. A clustered index scan basically is a table scan. A nonclustered index scan is better, but only a little. If you see any full scan, it means either (a) you're using non-sargable predicates or predicates on fields that aren't indexed, or (b) the predicate fields are indexed but the output columns aren't covered by the index, and the optimizer has decided that it is cheaper to perform a full scan than a bookmark/RID lookup.
Index scans aren't often much better than table scans, performance-wise, so you should try to eliminate whatever is leading to it, if possible.
Aaronaught
2010-03-13 20:24:58
Do you mean that it's possible to eliminate all table scans?
Lieven Cardoen
2010-03-13 21:09:54
@Lieven Cardoen: It's theoretically possible, although in practice you have a number of requirements to balance (query performance, insert/update/delete performance, database size, and so on). It also generally doesn't pay to have indexes on tables smaller than 100 rows or so if they're not going to grow at all. The answer is always "it depends", but if the table has a lot of data then a covering index can mean the difference between a 5-second query and a 50 ms query.
Aaronaught
2010-03-13 21:18:24
So, it's right to say that table scans don't mean that you have a bad database design? It depends on the context, table, ...
Lieven Cardoen
2010-03-13 22:19:40
@Lieven Cardoen: Table scans are *usually* an indicator of either a poorly-written query or a poorly-designed database, especially if the scan is on a large table, but there are obviously exceptions. It's no good to speculate - you need to identify the specific query that's causing the table/index scan, measure the improvement you get from having a covering index, and then make a decision as to whether or not it's justified to keep that index (based on other requirements).
Aaronaught
2010-03-14 00:13:03