SQL Server Table Scan Operator

Hi Geeks,

I hope you liked our new platform, I’m really excited to publish this post on our brand new web site. In this series we will have a look at different execution plan operators one per day and explore how do we read them, basic reasons as to why they appear on query plan and few tips to eliminate those that hurts query performance.

We will start this series with a basic operator today and I’m sure all of us at some point of our career have identified it to be a major performance impacting element. Yes you are right, it is SQL Server Table Scan operator. Let us replicate the scenario using a simple statement on AdventureWorks2012 database.

SELECT *
FROM dbo.DatabaseLog

This operator is self-explanatory, which indicates that the required result set is returned by scanning entire table row by row. Let us now look at different reasons what forces query optimizer to use a Table Scan operator and are listed below;

• No useful indexes on the table
• All rows of a table is required to be returned by the statement. Important point to note here is, in cases where all rows to be returned from a table, it is both faster and better for optimizer to scan each row rather than look up individual row in the index (irrespective of if index is present or not)
• Table with limited rows

Conclusion: if number of records are small in numbers, table scans are a better option and mostly no issues with the performance whereas if the number of rows are huge in number it is good to define appropriate index or to re-write the statement(s) to limit number of rows.

Happy learning.

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook