CHAPTER 28 ■ PERFORMANCE TUNING
To kick off the trace, click Run in the Trace Properties dialog box.
In SQL Server Management Studio, within a Query Editor, the following query is executed: SELECT CustomerID, ContactID, Demographics, ModifiedDate FROM Sales.Individual
9. Switching back to the SQL Server Profiler, you can see that the query was indeed captured in the trace (see Figure 28-6). In order to stop the trace, go to the File menu and select Stop Trace. By highlighting the SQL:BatchCompleted row, you can see the full SELECT statement in the lower pane. The duration shows that the query took 7.8 seconds, and originated from server process ID 52.
Figure 28-6. SQL Server Profiler results
How It Works
In this recipe, I demonstrated using SQL Server Profiler to identify a long running query. I filtered the list based on the query’s duration in this example, but you can also add additional filters based on your needs. For example, you can add an additional filter to only include activity for the AdventureWorks database. Or, you could add filters to only include queries that reference a specific database object or column name. Once you’ve captured the activity you are looking for, you can save the trace output to a file or table. You can also launch the Database Engine Tuning Advisor to evaluate the trace data for potential index improvements. I’ll cover the Database Engine Tuning Advisor later on in the chapter.
C a p t u r i n g E x e c u t i n g Q u e r i e s U s i n g s y s . d m _ e x e c _ r e q u e s t s
In addition to capturing queries in SQL Server Profiler, you can also capture the SQL for currently executing queries by querying the sys.dm_exec_requests dynamic management view, as this recipe demonstrates:
S E L E C T r . s e s s i o n _ i d , r . s t a t u s , r . s t a r t _ t i m e , r . c o m m a n d , s . t e x FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s WHERE r.status = 'running' t
This captures any queries that are currently being executed—even the current query used to capture those queries: