Loading...

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.