Tune Reports
The Tune Reports service task is designed to optimize a report to make it run more efficiently by looking at different ways of generating SQL for each report. The service task analyzes a report and determine whether it is more efficient to use temporary tables or Common Table Expressions (CTE) in the generated SQL. This service task only analyzes a report if the 'Select automatically once' or the 'Automatically tune over time' report setting is enabled in the report.
For more detailed information on the SQL Query methods available for a report, refer to theReport tab of the Report Wizard.
The Tune Reports Service Task has the following options:
| Field | Description |
|---|---|
| Information | This text field is used to provide a description of the purpose of the tune reports task being created and is only present to help users manage their tasks. |
| Tune Reports | The Tune Reports Service Task runs report queries multiple times using CTE and temp tables in turns. Every query is run twice and the time that each query takes to run is measured to determine which is the most accurate. When you create the Tune Reports Service Task you have the options to clear the buffer pool and clear the plan cache. Clearing the buffer pool requires sysadmin privileges while clearing the cache requires ALTER SERVER STATE permission. If neither option is selected, the Tune Reports service task still runs and tune the reports, but it is not as accurate. |
| Clear Buffer Pool (requires sysadmin) | When selected, this performs DBCC DROPCLEANBUFFERS to clear the buffer pool. If the buffers are not cleared the queries reuse data from the cache so the tuning is not as accurate as if the buffers had been cleared. The side-effect of clearing the buffer pool is that the next query that runs takes a longer time to complete as it does not have any cached data. Note that the clearing of buffers affects all the databases on SQL Server so it should be used with caution. This option requires the user under which the Server Service executes to have sysadmin privileges. |
| Clear Plan Cache | When selected, this performs DBCC FREEPROCCACHE - which requires ALTER SERVER STATE permission to clear the plan cache. Clearing of the cache is needed so that the queries that the Tune Reports Service Task runs are not affected by previously executed queries. |
| Location | This option only displays if you have chosen to replicate your database prior to installation of the Console, but is disabled for this service task. |
| Maximum task run time |
Specify the amount of time that the service task has to complete. For example, if you set the Maximum task run time to 5 minutes, after 5 minutes has elapsed from the time the service task first executes, the service job stops and its status shows as Aborted. The default setting is indefinitely. The following options are available:
|
-
Note: For detailed information on the SQL commands referenced above, please refer to your Microsoft SQL Server documentation.
This service task is scheduled by default to run weekly with a new installation of the Console.