Monitoring with Execution Plans - Microsoft SQL Server 2012 Performance Tuning - page 89

5

Monitoring with
Execution Plans

In this chapter we will cover:

F Working with an Estimated Execution Plan
F Working with an Actual Execution Plan
F Monitoring the performance of a query by SET SHOWPLAN_XML
F Monitoring the performance of a query by SET STATISTICS XML
F Monitoring the performance of a query by SET STATISTICS IO
F Finding the Execution Time of a query by SET STATISTICS TIME
F Including and understanding Client Statistics

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.