Loading...

Collecting Query Performance Data on Microsoft SQL Server Express

If Microsoft SQL Server Express software was installed during the installation of your Commvault software, for example, when you installed the Edge solution, use the following method to collect query performance data for queries running on the CommServe database.

Creating the Troubleshooting SQL database

The data you collect is stored in the Troubleshooting SQL database in the WhoIsActive table.

  1. If you are not logged on to the CommServe computer, run the qlogin command as the administrator.
  2. On the command line, go to software_installation_directory/Base, and then type the following command:

    dbmaintenance -wiarefresh

  3. To log off the CommServe computer, run the qlogout command.

Downloading the Stored Procedure

The DBMaintenance utility uses a third-party stored procedure called Who is Active to collect query performance data. The Who is Active stored procedure is available on the sqlblog website.

You must download the Who is Active stored procedure, unzip it, and place the unzipped file in the software_installation_path/Base folder. After the files are placed in the Base folder, the Who is Active stored procedure is installed automatically when the DBMaintenance utility is run.

Running the Stored Procedure

Manually run the WIA_Tracer stored procedure from the Microsoft sqlcmd utility.

Important: Keep the sqlcmd utility window open for the duration of the WIA_Tracer job. If the window is closed or stopped, data collection stops.

  1. Open the Microsoft sqlcmd utility (SQLCmd.exe).
  2. On the command line, type the following command:

    USE TroubleShooting; EXEC WIA_Tracer @cfgId=frequency_duration

    where frequency_duration is the ID for the frequency and duration configuration. For example, set @cfgId=9 to take a trace snapshot every 15 seconds for the next 72 hours.

    Valid values:

    • 1, trace snapshot every 5 seconds for the next 24 hours
    • 2, trace snapshot every 10 seconds for the next 24 hours
    • 3, trace snapshot every 15 seconds for the next 24 hours
    • 4, trace snapshot every 5 seconds for the next 48 hours
    • 5, trace snapshot every 10 seconds for the next 48 hours
    • 6, trace snapshot every 15 seconds for the next 48 hours
    • 7, trace snapshot every 5 seconds for the next 72 hours
    • 8, trace snapshot every 10 seconds for the next 72 hours
    • 9, trace snapshot every 15 seconds for the next 72 hours
    • 100, trace snapshot every 15 seconds for the next 7 days (168 hours)
    • 101, trace snapshot every 15 seconds for the next 14 days (336 hours)
    • 1000, trace snapshot every 15 seconds for the next 365 days

What to Do Next

Analyze the data to troubleshoot problems with queries running on the CommServe database.

Last modified: 3/12/2018 3:00:35 PM