CHAPTER 10 MANAGING REPORTSThe first goal in the following sections is to set up and extend the built-in logging functionality of
SSRS using tools provided in the SSRS installation. You’ll need to log all activity so that you can pinpoint
the reports and users who are most impacting the server. We have created a custom SSRS report, Report
Execution Log, which will deliver the logging statistics to administrators and contain dynamic column
groupings based on a report parameter. We’ll show how you can use this report for your SSRS
deployment.
The second goal in the following sections is to show how to perform benchmarking tests on the
SSRS servers in our test Web farm to ensure there won’t be any unexpected performance problems when
SSRS is deployed to a production environment. We’ll show how to work with a Web application stress-
test utility called Application Center Test (ACT) to gauge performance.
Configuring SSRS Logging
Getting to the execution log information in SSRS is a fairly straightforward procedure. It consists of a
main table in the SSRS database called, appropriately enough, ExecutionLogStorage. When SSRS is
installed by default, execution logging is enabled and set to maintain 60 days of logging. After 60 days,
the log entries older than 60 days are removed from the table automatically. However, you do have the
option to change the duration by connecting to the instance of SSRS using SSMS, then right clicking the
server and selecting Properties. After the Server Properties windows comes up, navigate to the Logging
tab. Figure 10-24 shows the settings if you wanted to change the number of days to hold 365 days rather
than the default 60 days.
With the execution details being stored and being that one of the aims is to build a custom SSRS
report to deliver report execution information to administrators, you’ll need to be able to query the log
data. Fortunately for us, Microsoft has created three views that utilize the execution details to make it
easy to determine information such as the user executing the report, duration a report took to process or
render and even if it was executed interactively or via a subscription. The three views in the ReportServer
database are ExecutionLog, ExecutionLog2, and ExecutionLog3. We will utilize these views as a basis for
our administrative report.
Figure 10-24. Report Server Property execution log settings
Transforming the ExecutionLog Table
Setting up SSRS to transform the logging data is a simple step-by-step procedure. Over the last several
years, we have created many solutions to transfer and store the execution data into a historical database
so that we do not lose any of the execution details when SSRS removes it from the database. One such
solution is to use a SQL Server Integration Services (SSIS) package to transfer the execution history on a
daily basis. As part of the Pro_SSRS solution, we have included a SSIS project with a single package called
Pro_SSRS_Load_ExecutionLog.dtsx. Furthermore, as part of the download is the Pro_SSRSExecutionLog