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

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 and storage engine
while executing the query, even without physically accessing the
production server of the person who has asked the question.
There are a few different ways to see the execution plan provided by SQL Server; some of the
important ways are listed here:
F Graphical execution plan
F Text execution plan
F XML execution plan
We will see each of these in detail in this chapter, but even before you start looking at
different ways of seeing the execution plan, we would like to explain some fundamentals
about how query is being processed in SQL Server.
We want to keep the long story short as a detailed understanding of the internals would need
a few chapters or maybe a whole topic. Kindly note that the steps which are being performed
during the execution of the query are beyond the scope of this topic, so only the necessary
steps are mentioned, along with some important terminology which is useful to understand
the chapter correctly.
F Relational engine: The relational engine, also known as query processor (QP)
manages execution of the query, requests data from the storage engine, and
processes result set.
F Command parser: The command parser checks for proper syntax and translates
T-SQL command into query tree. If there is any syntax error, parser immediately pops
up the error.
F Query optimizer: Query optimizer takes the query tree from the command parser and
if it is a Data Manipulation Language (DML) statement, it tries to optimize it. The
query optimizer first dismantles the batch it has received in form of query tree into
small pieces, and then tries to optimize each piece by finding different ways and then
choosing the best suited way to execute the query. Query optimizer is a cost-based
optimizer as the optimizer chooses the plan that it determines would cost the least,
based on its finding which is based on estimated memory requirements, index(es)
and statistics available on table, number of required I/O, and CPU utilization. Based
on all these analyses, query optimizer generates the estimated execution plan.
F Storage engine: As the name suggests, storage engine takes care of data access,
modification, and caching. Storage engine also takes responsibility to read data
from disk or from memory and retains data integrity. The storage engine receives
information regarding the query from the query processor along with the execution
plan in the form of a query tree.