SQL SERVER – Index Scans are Not Always Bad

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.

SET STATISTICS IO ON
SELECT TOP 1 i.InvoiceID, i.BillToCustomerID, i.Comments
FROM [WideWorldImporters].[Sales].[Invoices] i

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.

SET STATISTICS IO ON
SELECT TOP 1 i.InvoiceID, i.BillToCustomerID, i.Comments
FROM [WideWorldImporters].[Sales].[Invoices] i
ORDER BY [OrderID], [Comments]

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.

• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171
• Best Value for Maximum Worker Threads – SQL in Sixty Seconds #170
• Copy Database – SQL in Sixty Seconds #169
• 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
• Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
• 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
• Get Memory Details – SQL in Sixty Seconds #165
• Get CPU Details – SQL in Sixty Seconds #164
• Shutdown SQL Server Via T-SQL – SQL in Sixty Seconds #163

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