Home  >  Blog  >   MSBI  > 

SQL Server Reporting Services Performance Tuning - MSBI

Rating: 5
  1. Share:
MSBI Articles

Monitoring by Using Execution Log 2

The reporting services Execution log2 view is a good starting point from which to analyze your current workload and understand its dataset size, performance, and complexity characteristics.

In particular, this view contains a new Addition Info column.

Execution Log2 Addition Info contains Information related to the size of memory pressure responding data structures.

One way this information can be useful is to check whether you have reports with high values (10s or 100s of MBs) – these reports might be candidates for further review, focusing on the design of those reports and the dataset query sizes.

Some tips on how to view thaw ExecutionLog2 view to quickly understand potential performance bottlenecks are given below Linked is the Review Execution Logs reporting services project which creates summary and detail reporting services reports on the last 1,000 entries into the executionLog2 view.

 execution log summary
                                                     Fig: Review Execution Log s (Execution Log2) Summary report

Execution Log Details:

Query the most recent 1000 execution log details to better understand what is happening with RS server.

Instance Name  Report Path User Name Request Type Format Parameters Report Action  Source Status
My server /Review Exe logs/Exe log  User A  Interactive RPL   Render Live rs Success
My server  /Review Exe logs/  User A  Interactive RPL   Render Live rs Success
My server /SQL Auditing Reports/ Overview –DDL Actions User B Interactive RPL Pmin Date = 01/09/200000 and max Date= 01/13/2009 00 00 and report key = 102 Render Live rs Success


Sorting by Elapsedsec or RowCount helps you identify long-running reports.

If the value for TimeDataRetrieval is high, the data source is your bottleneck and you may want to optimize.

If there is a high value for RowCount a lot of data is being retrieved and aggregated by reporting servers – perhaps have your data source do this to reduce the load on your report server.

Related Article: Msbi Interview Questions

Subscriptions or Interactive:

Sorting by the RequestType field allows you to determine whether you have a lot of subscriptions.

you can then determine the bottlrnecks and stager – schedule the report (that is scheduled the subscription execution times of the reports at different times.)

 MindMajix YouTube Channel

Live Data or Snapshots:

Sorting by the Source field allows you to determine your reports are typically live data or snapshots. If the reports can be snapshots (for example, yesterday’s report), create snapshots so you can avoid query execution, report processing, and report rendering.

Load Balanced:

Sorting by the Instance field can help you see whether your network load balancer is handling report requests in a balanced fashion. You can also see if some nodes are down or not processing requests.

 Explore MSBI Sample Resumes Download & Edit, Get Noticed by Top Employers!  

List of Related Microsoft Certification Courses:

 SSIS  Power BI
 SSRS  SharePoint
 SCCM  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator



Join our newsletter

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
IBM Cognos TrainingJun 06 to Jun 21
IBM Cognos TrainingJun 10 to Jun 25
IBM Cognos TrainingJun 13 to Jun 28
IBM Cognos TrainingJun 17 to Jul 02
Last updated: 05 June 2023
About Author
Remy Sharp
Ravindra Savaram

Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

Recommended Courses

1 /15