SQL 2008 R2 RTM! Time to look at some new Execution Log Reports
Wow. We just RTM’ed SQL Server 2008 R2. Check out the official web site SQL Server 2008 R2 Website.
Now that the official release is on its way out the door, I thought I’d share some reports that show off some of the new capabilities. In SQL 2008 R2 there is a new ExecutionLog3 view in the Report Server database. For those familiar with SSRS, the ExecutionLog table, and then ExecutionLog and ExecutionLog2 views were used in previous versions to show which reports ran on the report server, who ran them, and to provide a lot of information about the execution. With SQL Server 2008 R2 the ExecutionLog content is expanded again. The newest version of the log is ExecutionLog3. You will find it by connecting directly to the report server database, looking under views and then you will see it. The execution log views are the only parts of the report server database which are supported across versions.
The reports shown in this Blog post depend on the new ExecutionLog3, they won’t work with SQL 2008 or SQL 2005 or even SQL 2000. The Report Executions by Hour report depends on new capabilities added to report processing in SQL 2008 R2 so it won’t run on SQL 2008. In principle you can recreate the Report Executions by Hour Trend report in SQL 2008, but you’d have to rebuild it since the RDL version I’m using here is the SQL 2008 R2 version.
What are some of the new features these reports show:
1) ExecutionLog3 – contains an expanded set of information and easier querying. Check out the Additional Info column if you want to really get into some detailed information.
2) Report Variables – ability to define variables at the report, data set or group level which allow you a more efficient report design experience – how many times have you copied the same expression around. Now, you can build it once (for certain uses) and reuse the output value in your data regions.
3) Lookup – ability to lookup data across data sets in a single data region. Probably this is the feature so many report authors needed. I have used it in the Report Execution by Hour report to compare the execution time of a report against its historical average. This feature alone should entice many report authors to consider SQL Server 2008 R2 Reporting Services.
What you need to do to run these reports:
1) Create a Shared Data source on your report server that connects to the Report Server database’s ExecutionLog3 table
2) publish the report and configure them to use the data source
3) run them :)
Report Executions By Hour
RDL File: ReportExecutionsByHour.rdl
Larger Image: ReportExecutionsByHour.PNG
Warning: The Report Executions By Hour report takes a while to run since it first, it runs quite a bit of logic and second because the download of all those pretty pictures. I find that running the report in Report Builder 3.0 preview is faster than running it in the Browser… one solution is to run it cached. But frankly, making it a subscriptions might be the best approach to viewing it repeatedly.
Report Executions By Hour Trend
RDL File: ReportExecutionsTrendByHour.rdl
Larger Image: ReportExecutionTrendByHour.PNG
Hopefully you’ll find using these reports helps you run your report servers efficiently. Of course there is a compulsory note about performance of these reports. I think you’ll find that they will take a long time to run as you increate the data volume. I would suggest extracting the data from the ExecutionLog3 view and loading it into a data warehouse.
Take care and good luck,
-Lukasz