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

TableScan

TableScanToolTip

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 FaceBookJoin the fastest growing SQL Server group on FaceBook | Follow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

2 Comments on “SQL Server Table Scan Operator”

  1. Hi
    these “one a day” series are handy articles to get a taste of what each characteristic (operator / DMV / trace flag etc.) has to offer the DBA, but it is very difficult to navigate between them if you wish to review 4 or 5 at a time one after the other. I am starting at day 1, then have to click about 5 links to get to day 2. Would it be possible to put a link to “next article in series” at bottom of each page. The current “Next” button links to the next article written which is generally on another topic.

    thanks again

    Conor

Leave a Reply

Your email address will not be published. Required fields are marked *