“Are table scans always Bad?” – I was asked this question during a recent Comprehensive Database Performance Health Check. Well, honestly no. Scans are not always bad they are there in the SQL Server execution plan because they are needed. Let us see a very simple demonstration about Top 1 and Index Scan.
It is a misconception that table scans are always bad and table seeks are better. I have often seen many times where table seeks are performing poorly compared with table scans. However, I must agree that I have seen enough time when there are table scan query is taking too long because it has to read the huge table entirely. With the same query, when the index is created or the query is modified and it shows the seek as operator the performance is optimized. While this is common we must understand that table scan was there as it was needed earlier and now with the changes table seek was needed and it appeared the query. We should actually say that table scan or index scan was a hint to the opportunity to write query better.
Top 1 and Index Scan
Lots of people think that when the scan operator appears, it scans the entire table. Well, today let us see a very simple example where we have an Index scan but the table is not scanned entirely.
Run the following query.
SET STATISTICS IO ON SELECT TOP 1 i.InvoiceID, i.BillToCustomerID, i.Comments FROM [Sales].[Invoices] i
Now let us see the message window.
Table ‘Invoices’. Scan count 1, logical reads 3
Here you can see that the query is reading only 3 pages from the table invoice which is a very huge table. If we remove the top from the query, it will show us a logical read of 11400 pages. Let us check the execution plan.
When you check the execution plan, it is very clear that even though there is a scan operation it is actually reading only 1 row.
So yeah, not all the scan operations even read the entire table or index. If you have any questions, please feel free to reach out to me on LinkedIn.
Reference: Pinal Dave (https://blog.sqlauthority.com)
