Managing Server Components in VS .NET

Printing QR-Code in VS .NET Managing Server Components

14. Click the Execution Log Reports folder link. 15. Click the Average Report Execution Times report link. Notice that neither the Product Profitability Filter report nor the Product Profitability Query Parameter report is displayed in this report. The execution log reports are dependent on the data that currently exists in the RSExecutionLog database, which was extracted before you executed the product reports. You need to update the RSExecutionLog database to append records for the execution logging for the product reports.

The RSExecutionLog is not designed to stay synchronized with the execution logging data in the ReportServer database. You need to determine a reasonable frequency for updating the RSExecutionLog database with new execution logging records, and then schedule the SSIS package to execute with this frequency. (Refer to SQL Server Books Online for more information about scheduling an SSIS package.) In this procedure, you ll update the RSExecutionLog database by executing the RSExecutionLog_Update package. Update the execution log database 1. Switch to the Execute Package Utility, and then click Execute. 2. Click Close twice. The SSIS package appended new records to the RSExecutionLog database. Note

Rather than append records to the logging database, you might want to remove records for older data. You can use the cleanup.sql script in the C:\Documents and Settings\<username>\My Documents\Microsoft Press\rs2005sbs\chap11 folder. This file is also located in the C:\Program Files\Microsoft SQL Server\90\Samples \Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports folder if you installed the SQL Server 2005 samples.

3. Close the Execute Package Utility. In this procedure, you ll use execution logging reports to compare the performance of the Product Profitability Filter and Product Profitability Query Parameter reports. Compare execution performance 1. In Report Manager, return to the Execution Log Reports page, and then click the Average Report Execution Times report link. 2. If necessary, replace the values in the Start Date and End Date boxes with the current date and time in the End Date box, and then click View Report. You might need to scroll through the report to find the product reports.

Part III:

The averages for the product reports should look similar to this:

The report shown in this example was exported to Microsoft Excel and reformatted to better display all data for these two reports. You might need to scroll horizontally to view the values in each column in Report Manager.

The averages shown in your report will differ because of variations in server configurations. Using the report illustrated here, you can see that the relative performance of the two reports is similar. The average rendering time of the two product reports in the previous illustration is nearly the same, because rendering was required when each report was opened and each time View Report was clicked. The processing time and data retrieval time is greater for the Product Profitability Filter report, even though the query executed only once. However, the query retrieved data for all categories, which increased the data retrieval time as well as the processing time of the report. In the case of the Product Profitability Query Parameter report, the use of query parameters to retrieve a smaller dataset actually resulted in faster performance overall compared to the other report. As indicated in the Average Report Execution Times report, the times displayed in the report are milliseconds, so any delay experienced by the user in executing these reports is not particularly noticeable. However, when you are working with reports including production data, you can use the information in the Average Report Execution Times report to help you discover bottlenecks so you can take appropriate action to resolve report problems. For example, if you see that data retrieval is consuming a considerable amount of the overall processing time, you might try to improve the performance of the SQL query. If, on the other hand, you see processing is taking a long time, you might look for ways to reduce the complexity of the report. 3. Click the Execution Log Reports folder link, and then click the Report Parameters report link. 4. Expand Product Profitability Filter, and then expand Product Profitability Query Parameter.

11: