Administrating the Core Database Engine - Microsoft SQL Server 2008 R2 Administration - page 59

The sys.dm_exec_sql_text function returns the text of the SQL batch that is identified
with the sql_handle column. As you can see in the previous query, the function takes the
sql_handle value from the sys.dm_exec_requests DMV with a CROSS APPLY join type
to return associated SQL text. The final layer is the WHERE clause, which gives you the values
for the status of the request which can be: Background / Running / Runnable / Sleeping /
Suspended , and cannot be null to complete the execution. To obtain a current snapshot of
the query execution, the following two DMVs sys.dm_exec_sql_text and sys.dm_exec_
requests are used.
Understanding a query execution plan is essential to fix a poorly performing query to obtain a
detailed execution plan in the form of graphical/text/XML format. The following information is
visible from the query execution plan:
F Highest cost queries within a batch and highest cost operators within a query
F Index or table scans (accessing all the pages in a heap or index) against using seeks
F Missing statistics or other warnings
F Costly sort or calculation activities
F High row counts being passed from operator to operator
F Discrepancies between the estimated and actual row counts
F Implicit data type conversions
The output is provided with the plan information, without executing the query which allows you
to adjust the query or indexes on the referenced tables before actually executing it. Each of
these commands return the information in a different way. Further, within the query editor at
the beginning of the query, using SET SHOWPLAN_ALL returns the estimated query plan in a
tabular format, with multiple columns and rows. The SET SHOWPLAN_TEXT command returns
the data in a single column, with multiple rows for each operation. You can also return a query
execution plan in XML format using the SET SHOWPLAN_XML command. The syntax for each
of these commands is very similar. Each command is enabled when set to ON , and disabled
when set to OFF :
SET SHOWPLAN_ALL { ON | OFF}
SET SHOWPLAN_TEXT { ON | OFF}
SET SHOWPLAN_XML { ON | OFF}
The output includes information such as the estimated IO or CPU of each operation, estimated
rows involved in the operation, operation cost (relative to itself and variations of the query),
and the physical and logical operators used.