X hits on this document

98 views

0 shares

0 downloads

0 comments

11 / 48

CHAPTER 28 PERFORMANCE TUNING

status

start_time

command

text

running

2005-10-16 13:53:52.670

SELECT

SELECT r.session_id,

  • r.

    status, r.start_time,

  • r.

    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

645

Document info
Document views98
Page views98
Page last viewedMon Dec 05 11:24:09 UTC 2016
Pages48
Paragraphs987
Words15822

Comments