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

CROSSAPPLYsys.dm_exec_sql_text(r.sql_handle) s
WHERE r.status='running'

3. In addition to that process, let us obtain an extended version of queries that are

executed in batches along with statistical information by executing the following
TSQL statement:
SELECT s2.dbid,s1.sql_handle,(SELECTTOP 1SUBSTRING(s2.
text,statement_start_offset / 2+1 ,((CASEWHEN statement_end_
offset =-1 THEN (LEN(CONVERT(nvarchar(max),s2.text))* 2)
ELSE statement_end_offset END)- statement_start_offset)/ 2+1))
AS sql_statement, execution_count, plan_generation_num,
last_execution_time, total_worker_time, last_worker_
time, min_worker_time, max_worker_time,total_physical_
reads, last_physical_reads, min_physical_reads, max_
physical_reads, total_logical_writes, last_logical_writes,
min_logical_writes, max_logical_writes
FROMsys.dm_exec_query_statsAS s1
CROSSAPPLYsys.dm_exec_sql_text(sql_handle)AS s2
WHERE s2.objectid isnull
ORDERBY s1.sql_handle, s1.statement_start_offset, s1.statement_
end_offset;

The results of the previous query will help you to understand the execution
context such as physical reads, logical writes, and worker threads.
To display the execution plan (detailed) for an SQL statement or batches, use
the following statements at the top of the query: SET SHOWPLAN_ALL, SET
SHOWPLAN_TEXT, and SET SHOWPLAN_XML .
Here is the code example to show the way SQL optimizer analyzes and
optimizes the use of indexes in queries:
USE AdventureWorks2008R2;
GO
SETSHOWPLAN_ALLON;
GO
SELECT Person.Contact.ContactID, Person.Contact.
FirstName, Person.Contact.LastName,
Person.Contact.EmailAddress, HumanResources.Employee.
Gender, HumanResources.Employee.BirthDate
FROM HumanResources.EmployeeINNERJOIN Person.Contact
ON HumanResources.Employee.ContactID= Person.Contact.
ContactID
GO
SETSHOWPLAN_ALLOFF;
GO