SQL SERVER - Index Scans are Not Always Bad - SQL Authority with Pinal Dave

Are Index Scans Bad? The answer No. Index Scans are Not Always Bad. This was in-depth discussed with my client during the Comprehensive Database Performance Health Check. Let us learn about it today.

Myth – Index Scans Bad

Lots of people think that seeks are better and scans are bad but the truth is both of them are needed when they are needed. Additionally, when people see an index scan they think that the entire table or index is scanned, well that is not true as well.

Today, let us see a simple script that demonstrates that even though the execution plan shows Index Scan, it is not giving accessing the entire table.

As a matter of fact, when seeing the execution plan, we can see that it is only retrieving a single row. Let us check the output of the STATISTICS IO, it is clear from it that they are reading very little data from the table.

Table ‘Invoices’. Scan count 1, logical reads 3, physical reads 0

Now if you think it is because of the TOP operator, well, it is not true. Here is another query where I am getting the same result but this time entire table is scanned and also STATISTICS IO shows a huge amount of rows.

So essentially, the moral of the story is – Scans are not always bad.

Table ‘Invoices’. Scan count 17, logical reads 11994, physical reads 0
Table ‘Worktable’. Scan count 0, logical reads 0

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)