SQL Server Execution Plan – Simplifying : Part 1

Dear Friends,

Every time we run into query performance issues one very basic but important question strikes in our mind that; how the query SQL Server execution plan looks like? A query execution plan is query optimizers attempt to calculate the most effective way to implement every query request represented by Transact SQL statements that we submit. A query execution plan is very important from DBA point of view when troubleshooting poor performing queries as they tell you how a query is executed or will be executed.

It is very important to get familiar with different operators (represented as ICONS) that are shown in execution plans in order to understand them correctly before troubleshooting anything. I thought of writing a series on some basic but important execution plan operators which may be helpful to us when we start interpreting them in real life scenarios. It is not possible to cover everything in a blog post or two and even a series however I’ll try to cover as much as possible which may help us to understand how do we interpret graphical execution plans with sample T-SQL queries.

Before I start my demonstration on different plan ICONS, I believe we will have to get ourselves acquainted with what are operators? MSDN describes Operators as following;

Operators describe how SQL Server executes a query or a Data Manipulation Language (DML) statement. The query optimizer uses operators to build a query plan to create the result specified in the query, or to perform the operation specified in the DML statement. The query plan is a tree consisting of physical operators. You can view the query plan by using the SET SHOWPLAN statements, the graphical execution plan options in SQL Server Management Studio, or the SQL Server Profiler Showplan event classes.

These operators can be either logical or physical, which we will have a look later parts of this series. Complete list of operators is available here on MSDN.

Today we will kick start this series with Clustered Index Scan with a simple query, here we go;

Clustered Index Scan

1_SQL_Server_Simplifying_execution_plans_Part1

   

2_SQL_Server_Simplifying_execution_plans_Part1

You can observe that clustered index scan operation performed to retrieve requested result set. When you place mouse pointer to Clustered index scan icon it show index key PK_Person_BusinessEntityID used to fetch result set and estimated number of rows are 19972. Clustered index not only stores key structure but also sort and stores data. So, clustered index scan is almost same as table scans. When we look at Ordered option shown in ToolTip, this is set to False here. Entire index is scanned through row-by-row in order to retrieve result-set which is not an efficient way and may impact overall query performance. We can think about using appropriate predicates in WHERE clause to limit query result as per our requirements.

Stopping here today, in my next post I’ll illustrate some more operators. Stay tuned, I will be back with next part soon.

 

Regards

Kanchan Bhattacharyya

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

Follow me on TwitterFollow me on FaceBook

   

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 →

Leave a Reply

Your email address will not be published.