Introduction

An execution plan is one of the most important feature shipped with SQL Server since long. This feature is not only for DBA or SQL developer, but this is useful for everybody who is dealing with T-SQL (Transact-SQL) in any capacity.

An execution plan guides you to understand what has happened with the query, which was getting executed, with the help of estimated execution plan/actual execution plan. It helps you to identify how your JOIN statements are behaving, whether Index is being used or not, what was the estimation of query optimizer for your query, what are the actual costs taken by the query executed, and much more such as data flow, sampling of rows, reads, writes, logical I/O, physical constraints of query, operators.

Note

When we see questions related to slow query performance, in any technical forum about SQL Server; we used to ask, Can you please provide us with the execution plan of the query? This is the best way to get an insight about the path taken by optimizer...