Collecting Query Performance Data Using the DBMaintenance Utility
You can collect data about query performance for queries running on the CommServe database. After the data is collected, you can analyze it to troubleshoot problems. For information on analyzing the data, see Analyzing the Query Performance Data Collected by the DBMaintenance Utility.
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 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>
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.
The following command lists the available frequency and duration configurations used with the wiatracercfg parameter.
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 48 hours).
dbmaintenance -startwiatracer -wiatracercfg 3 -wiatruncate