About SQL Server reports and system tools

The system_tools command has a number of options that relate to SQL Server performance reports. Using these command options, you can do the following:
Action Command option
Determine the IDs of the SQL Server performance report time intervals -sqlListIntervals
Generate a performance report using the interval IDs -mssqlPerfRpt
Determine the ID of each generated report -listPerfReports
Download an existing report to a local directory using its ID -getPerfReport
Note: All PolicyCenter command prompt tools require that you provide the password of an administrative user for the server on which you run the command.

See also

system_tools -password password -sqlListIntervals n

Use this command option to list the n number of most recent Query Store runtime statistics intervals, including its ID. Each interval ID specifies a unique time interval. For example, with n is 3, the -sqlListIntervals option generates something similar to the following output.
3 most recent Query Store runtime stats intervals
ID=14, StartIntervalTime=Fri Aug 24 12:00:00 MDT 2018, EndIntervalTime=Fri Aug 24 12:00:00 MDT 2018
ID=13, StartIntervalTime=Fri Aug 24 11:00:00 MDT 2018, EndIntervalTime=Fri Aug 24 11:00:00 MDT 2018
ID=12, StartIntervalTime=Fri Aug 24 10:00:00 MDT 2018, EndIntervalTime=Fri Aug 24 10:00:00 MDT 2018

system_tools -password password -mssqlPerfRpt n1 n2 collectstatistics

Use this command option to generate a SQL Server performance report defined by the option parameters. If you do not know the interval ID, run the -sqlListIntervals command option. The -mssqlPerfRpt option parameters have the following meanings:
n1, n2
Optional interval IDs that indicate the time period to use for the SQL Server Query Store report:
  • Enter a single interval ID to generate a report for a single defined time interval.
  • Enter two interval IDs to generate a report across a time range that includes the beginning and ending intervals and all time intervals in between.

After PolicyCenter generates the report, the SQL Server Performance Report screen indicates the time period used for the performance report.
collectstatistics A Boolean value that determines whether PolicyCenter includes database statistics (true) in the output report.
Depending on how you set the command options, this command option generates something similar to the following output.
Submitting SQL Server Performance Report from interval 15 to 16 with collect statistics set to true
View results on the SQL Server Performance Report Page or download it here.
To view the resulting report, do one of the following:
  • Navigate to the Server Tools SQL Server Performance Report screen.
  • Download the report to your local drive by running the -getPerfReport option.

system_tools -password password -listPerfReports n

Use this command to list the n most recent SQL Server performance reports, including the report ID. For example, if n is 2, the -listPerfReports command option generates something similar to the following output.
Most recent database performance downloads
ID: pc:5, Status: Succeeded, Start: 08/24/2018 1:29 PM, End: 08/24/2018 1:37 PM, Description: Report
      for interval 15, not including database statistics
ID: pc:4, Status: Succeeded, Start: 08/24/2018 1:11 PM, End: 08/24/2018 1:18 PM, Description: Report
      without Query Store, not including database statistics

system_tools -password password -getPerfReport ID

Use this command to download a copy of the SQL Server performance report specified by ID to a local drive. If you do not know the report ID, run the -listPerfReports option. This command option generates something similar to the following output.
Database performance report stored in C:pc10\admin\bin\DbPerfReport_Fri_Aug_24_14.58.30_MDT_2018.zip

To download the report file to a directory other than the default directory, add the -filepath option to the -getPerfReport command option.