CHAPTER 28 ■ PERFORMANCE TUNING
command, s.text FROM
s y s . d m _ e x e c _ r e q u e s t s CROSS APPLY sys.dm_exec_sql_text(r.sql_ handle) s WHERE r.status = 'running' r
session id 55 _
How It Works
The sys.dm_exec_requests dynamic management view returns information about all requests executing on a SQL Server instance.
The first line of the query selects the session ID, status of the query, start time, command type (for example SELECT, INSERT, UPDATE, DELETE), and actual SQL text:
SELECT r.session_id, r.status, r.start_time, r.command, s.text
In the FROM clause, the sys.dm_exec_requests dynamic management view is cross-applied against the sys.dm_exec_sql_text dynamic management function. This function takes the sql_handle from the sys.dm_exec_requests dynamic management view and returns the associated SQL text:
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
The WHERE clause then designates that currently running processes be returned: WHERE r.status = 'running'
Viewing a Query’s Graphical Execution Plan
Knowing how SQL Server executes a query can help you determine how best to fix a poorly performing query. Common operations that you can identify by viewing a query’s execution plan (graphical or command based) include:
Identifying whether the query is performing scans (looking at all the pages in a heap or index)
or seeks (only accessing selected rows).
Identifying missing statistics or other warnings.
Performing costly sort or calculation activities.
In this recipe, I’ll demonstrate how to view the estimated and actual execution plans for a query. I’ll start by using the previous recipe’s long duration query in SQL Server Management Studio, using the AdventureWorks database context (see Figure 28-7):
SELECT CustomerID, ContactID, Demographics, ModifiedDate FROM Sales.Individual