Analyzing the Query Performance Data Collected by the DBMaintenance Utility

After query performance data is collected by the DBMaintenance utility, you can analyze the data to troubleshoot query problems, for example:

• Find long running and high CPU utilization queries

• Find blocking chains

• Find deadlocks

You can write your own queries against the WhoIsActive table or you can use a predefined database view.

Before You Begin

Run the DBMaintenance utility to collect the query performance data and store it in the Troubleshooting SQL database in the WhoIsActive table. For information, see Collecting Query Performance Data Using the DBMaintenance Utility.

Procedure

1. On the CommServe computer, open the SQL Server Management Studio (SSMS) software.

2. Run queries against the query performance data:

   • Go to CommServe > Databases > Troubleshooting > Views and select from the following views:

     • SELECT * FROM WIA_LastestCollection

       Displays the latest rows inserted into the WhoIsActive table.

     • SELECT * FROM WIA_LongRunningQueriesGt1MinView

       Displays queries running for more than 60 seconds.

     • SELECT * FROM WIA_BlockedSessionsView

       Display queries blocked by another session.

     • SELECT * FROM WIA_BlockedSessionsReport

       Display queries blocked by another session and the blocking query the other session is running.

     • SELECT * FROM WIA_ActiveTracerView

       Displays information about the running WIA_Tracer SQL Server agent job.

     • SELECT * FROM TL_ListActiveTransactions

       Displays active transactions.

   • Write your own queries against the WhoIsActive table.