Loading...

Collecting Query Performance Data Using the Command Line

You can collect data about query performance for queries running on the CommServe database using the command line interface. After the data is collected, you can analyze it to troubleshoot problems.

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 sp_whoisactive website.

Go to sp_whoisactive Downloads and download the latest version of the Who is Active stored procedure. Review the author's copyright agreement. On the CommServe computer, in the SQL Server Management Studio (SSMS), in the master database, install and execute the Who is Active script. For instructions about how to execute a stored procedure in SSMS, on the Microsoft documentation website, see Execute a Stored Procedure.

Running the Utility

The DBMaintenance utility is located in the software_installation_path/Base folder. From the command prompt, run dbmaintenance with the appropriate parameters.

To automatically detect the database instance and retrieve the logon information from the encrypted data in the registry, run this utility on a CommServe database without any connection parameters.

To provide the database instance and the logon credentials, run this utility with the following connection parameters:

  • -S <DBServer>
  • -U <username>
  • -P <password>

Collection Parameters

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

Use the following parameters to manage the tracer jobs that collect data on query performance:

  • wialistconfigs - Lists the available frequency and duration configurations. For example, take a trace snapshot every 15 seconds for the next 72 hours. These configurations are predefined.
  • startwiatracer [-wiatracercfg <configurationId>] [-wiatruncate] [-wiarefresh] [-wiaprunedays <days>] [-wiatrans] - Adds the WIA_Tracer SQL Server agent job that collects the data.
    • wiatracercfg - The frequency and duration configuration to run. For a list of available configurations, use the wialistconfigs parameter. If the wiatracercfg parameter is not used, configuration 2 is run: trace snapshot every 10 seconds for the next 24 hours.
    • wiatruncate - Truncates the WhoIsActive table before starting the WIA_Tracer SQL Server agent job.
    • wiarefresh - Installs the latest SQL scripts.
    • wiaprunedays - Prunes collected data older than the defined number of days. Valid values are 4 to 14 days. The default value is 6 days.
    • wiatrans - Collects active transactions.
  • stopwiatracer - Stops the WIA_Tracer SQL Server agent job.

Note: Running the utility without parameters will return the complete list of supported parameters.

Examples

The following command lists the available frequency and duration configurations used with the wiatracercfg parameter.

dbmaintenance -wialistconfigs

The following command truncates the WhoIsActive table and then starts a WIA_Tracer SQL Server agent job using configuration ID 3 (trace snapshot every 15 seconds for the next 24 hours).

dbmaintenance -startwiatracer -wiatracercfg 3 -wiatruncate

What to Do Next

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

Last modified: 4/17/2019 6:54:40 PM