Troubleshooting SQL Reporting Services Issues Part1:

A customer came to the SQL Server Customer lab to investigate a problem with one of the feature I’m responsible for – data-driven subscriptions.  In looking at their solution, we discussed any number of problems they had encountered.  I realized that it can be difficult to find out where to start looking when a problem occurs.  This post will hopefully provide you a starting point as you endeavor to fix the issues you run into.

Reports can take up

-          a lot of memory,

-          a lot of time to execute,

-          a lot of CPU

Generally speaking, it is possible for errors to occur as a result:

-          Out of Memory

-          Internal Errors

-          Rendering errors

This begs the question, “How to diagnose issues when running reports in the report server?”

General process to follow:

Performance, Snapshots, Caching (Reporting Services)

Report server performance is affected by a combination of factors that include hardware, number of concurrent users accessing reports, the amount of data in a report, and output format. To understand the performance factors that are specific to your installation and which remedies will produce the results you want, you will need to get baseline data and run tests.

General principles to consider include the following:

·         Report processing and rendering are memory intensive operations. When possible, choose a computer that has a lot of memory.

·         Hosting the report server and the report server database on separate computers tends to provide better performance than hosting both on a single high-end computer.

·         If all reports are processing slowly, consider a scale-out deployment where multiple report server instances support a single report server database. For best results, use load balancing software to distribute requests evenly across the deployment.

·         If a single report is processing slowly, tune report dataset queries if the report must run on demand. You might also consider using shared datasets that you can cache, caching the report, or running the report as a snapshot.

·         If all reports process slowly in a specific format (for example, while rendering to PDF), consider file share delivery, adding more memory, or choosing a different format.

·         Optimize SQL Reporting Services Queries

Optimize your report queries. Usually, the bulk of report execution time is spent executing queries and retrieving results. If you are using SQL Server, tools such as Query Analyzer and Profiler can help you optimize queries.

·         Report Subscription Scheduling

Whenever possible, schedule report subscription processing to run outside of normal office hours. This will reduce load on the reporting server and improve availability for ad hoc report requests.

Architecture

Figure 1 represents a typical scale-out Reporting Services environment; as noted by the red box, the focus of this technical note is that of Performance Optimization of your scale-out deployment.

Figure 1: Reporting Services Scale-Out Architecture

Should You Use 64-Bit?

Yes.

Why, you ask? The answer can be divided into two different sections – how 64-bit helps your report server catalog performance and how it helps your report server performance.

How 64-bit Helps Your Report Server Catalog Performance

Remember that your report server catalogs are SQL Server databases, so your standard database techniques for optimizing SQL Server databases come into play here. Since SQL Server 2005, the database has been written natively for 64-bit and it is able to make use of the additional addressable memory.

How 64-bit Helps Your Report Server Service

For your report servers, the answer is slightly more complicated. As a general statement, most of your Reporting Services reports are memory-intensive, and the additional addressable memory made available by 64-bit will provide you with more scale. Note that for some workloads, 32-bit can execute faster, especially in scenarios where you have many small reports. But if you have more memory made available by 64-bit, you can handle more concurrent report users. Because there is less memory contention, report throughput will be higher; that is, you can allow more users to view and export larger reports. In SQL Server 2005 Reporting Services, each report’s data set is placed into memory; the more concurrent reports, the more memory used. If you use 32-bit, you can easily hit the 3 GB ceiling, which can also result in Internet Information Services (IIS) process recycles, leading to report failure.

But SQL Server 2008 Reporting Services is not memory-bound. It is able to effectively use the file system in order to move data structures in and out of memory and the file system if the report server experiences memory pressure. These memory limits are explicitly configurable in SQL Server 2008 Reporting Services via RSReportServer.config, as noted in the Memory Configurations for SQL Server 2008 Reporting Services section below. When Reporting Services uses the file system, the reports run more slowly, because it is much more efficient to request data from the memory than from disk. The file system is used only if Reporting Services memory usage gets close to the configured memory limits. If you overload the report server in SQL Server 2008 Reporting Services with a large number of concurrent users and/or very large reports, all of your reports can still complete processing, albeit more slowly, because Reporting Services can hold all of this data without running out of memory space. In enterprise environments, you will eventually run into situations where your servers will need to be able to handle many concurrent users and a large load – the optimization in SQL Server 2008 Reporting Services (in comparison to SQL Server 2005 Reporting Services) is that while the reports may run slower at times, they will complete.

Exceptions

Keep in mind that certain data providers are not available for 64-bit (for example, the Microsoft JET provider or certain third-party providers). In these cases, customers will need to continue using 32-bit for their Reporting Services environment.

 Handling a Large Workload

As noted in the previous section, the two main issues concerning enterprise reporting environments are the ability to handle concurrent user load and the ability to handle a large workload (that is, large reports). To help mitigate the concurrency issue, your solution is to scale out to multiple report servers to handle the user query load.

To get the highest performance when handling large workloads that include user requests for large reports, implement the following recommendations.

Control the Size of Your Reports

You will first want to determine the purpose of these reports and whether a large multipage report is even necessary. If a large report is necessary, how frequently will it be used? If you provide users with smaller summary reports, can you reduce the frequency with which users attempt to access this large multipage report? Large reports have a significant processing load on the report server, the report server catalog, and report data, so it is necessary to evaluate each report on a case-by-case basis.

Some common problems with these large reports are that they contain data fields that are not used in the report or they contain duplicate datasets. Often users retrieve more data than they really need. To significantly reduce the load placed on your Reporting Services environment, create summary reports that use aggregates created at the data source, and include only the necessary columns. If you want to provide data feeds, you can do this asynchronously using more appropriate tools, such as SQL Server Integration Services, to provide the file data feed.

Use Cache Execution

If you have reports that do not need to have live execution, enable the cache execution setting for each of your appropriate reports. This setting causes the report server to cache a temporary copy of those reports in memory.

Configure and Schedule Your Reports

For your large reports, use the Report Execution Timeouts setting to control how long a report can execute before it times out. Some reports simply need a long time to run, so timeouts will not help you there, but if reports are based on bad or runaway queries, execution timeouts ensure that resources are not being inappropriately utilized.

If you have large reports that create data processing bottlenecks, you can mitigate resource contention issues by usingScheduled Snapshots. Instead of the report data itself, a regularly scheduled report execution snapshot is used to render the report. The scheduled snapshot can be executed during off-peak hours, leaving more resources available for live report users during peak hours.

Deliver Rendered Reports for Nonbrowser Formats

The rendering performance of nonbrowser formats such as PDF and XLS has improved in SQL Server 2008 Reporting Services. Nevertheless, to reduce the load on your SQL Server Reporting Services environment, you can place nonbrowser format reports onto a file share and/or SharePoint® team services, so users can access the file directly instead of continually regenerating the report.

Prepopulate the Report Cache by Using Data-Driven Subscriptions for Parameterized Reports

For your large parameterized reports, you can improve performance by prepopulating the report cache using data-driven subscriptions. Data-driven subscriptions enable easier population of the cache for set combinations of parameter values that are frequently used when the parameterized report is executed. Note that if you choose a set of parameters that are not used, you take on the cost of running the cache with little value in return. Therefore, to identify the more frequent parameter value combinations, analyze the ExecutionLog2 view as explained below. Ultimately, when a user opens a report, the report server can now use a cached copy of the report instead of creating the report on demand. You can schedule and populate the report cache by using data-driven subscriptions.

Back to the Report Catalogs

You can also increase the sizes of your report server catalogs, which allows the databases to store more of the snapshot data.

Tuning the Web Service

IIS and Http.sys tuning helps get the last incremental performance out of the report server computer. The low-level options allow you to change the length of the HTTP request queue, the duration that connections are kept alive, and so on. For large concurrent reporting loads, it may be necessary to change these settings to allow your server computer to accept enough requests to fully utilize the server resources.

You should consider this only if your servers are at maximum load and you do not see full resource utilization or if you experience connection failures to the Reporting Services process. To do this:

For SQL Server 2005 Reporting Services, tune IIS.

For SQL Server 2008 Reporting Services, tune Http.sys within the operating system: Windows® 2003 or Windows 2008.

Monitoring by Using ExecutionLog2

The Reporting Services ExecutionLog2 view is a good starting point from which to analyze your current workloads and understand its dataset size, performance, and complexity characteristics. For more information, see Robert Bruckner’s blog, which provides extensive details on the ExecutionLog2 view (http://blogs.msdn.com/robertbruckner/archive/2009/01/05/executionlog2-view.aspx). For more information about query and reporting on report execution log data, see SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms155836.aspx).

In particular, this view contains a new AdditionalInfo column. ExecutionLog2.AdditionalInfo 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.

Below are some tips on how to view the ExecutionLog2 view to quickly understand potential performance bottlenecks, which creates summary and detail Reporting Services reports on the last 1,000 entries into the ExecutionLog2 view, with the sorting options noted below.

Figure 2: Review Execution Logs (ExecutionLog2) Summary Report

Figure 3: Review Execution Logs (ExecutionLog2) Details Report

Long-Running?

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 the there is a high value for RowCount, a lot of data is being retrieved and aggregated by Reporting Services – perhaps have your data source do this to reduce the load on your report server.

Subscriptions or Interactive?

Sorting by the RequestType field allows you to determine whether you have a lot of subscriptions; you can then determine the bottlenecks and stagger-schedule the reports (that is, schedule the subscription execution times of the reports at different times).

Live Data or Snapshots?

Sorting by the Source field allows you to determine whether 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 rending.

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.

Discover Report Patterns

Sorting by ReportPath and TimeStart can help you to find interesting report patterns – for example, an expensive report that takes 5 minutes to run is executed every 10 minutes.

Report Health

You can sort by status to determine if you have a high number of failures that occurred before (for example, incorrect RDL) or after (for example, subscription delivery error) the report is processed. This can also help you identify reports where there is outdated information or settings (for example, expired data source passwords or missing subreports).

In addition, if ScalabilityTime > 0, Reporting Services is in scale mode, which means that it is under heavy memory pressure and will start pushing long-running reports to the file system to ensure enough memory to complete smaller query requests. If this happens frequently, consider trying one of the following:

·         Reduce the size of the dataset.

·         Simplify the grouping, filtering, or sorting logic in the report to use less memory.

·         Add more memory.

·         Add more servers to better handle the load.

Data-Driven Subscriptions

Based on all of this information, you can then create your own data-driven subscriptions that can sort, filter, and track your issues. For example, you can create a subscription that alerts you if Errors > 5%.

Memory Configurations for SQL Server 2008 Reporting Services

As alluded to above in the 64-bit section, memory in SQL Server 2008 Reporting Services is used more efficiently at the data structure level. Under intensive workload pressures, it uses a file system cache in order to reduce memory utilization. By being more efficient and writing to disk, it can process more report executions (even large reports) successfully. The administrator uses settings to determine when SQL Server 2008 Reporting Services starts to use the file system cache and how aggressively the cache is used. The administrator should consider configuring memory settings for the SQL Server 2008 Reporting Services to optimally use their computer resources. Fine-tuning these settings can provide an increase in performance under intensive workload over the default configuration.

For more information, see SQL Server 2008 Books Online: Configuring Available Memory for Report Server Applications. The key memory configurations are:

WorkingSetMinimum: This is the minimum amount of memory that Reporting Services makes available in order for it to perform its task; that is, it does not use the file system cache if SQL Server Reporting Services process memory utilization is below this limit. It is configured as a KB value within the RSReportServer.config file. After this threshold is hit, Reporting Services responds to memory pressure by having long-running requests use the file system cache and smaller queries use memory.

For many concurrent users, you can potentially increase this configuration value after it hits peak performance, enabling more process requests to be completed within memory instead of the file system.

 WorkingSetMaximum: This is the maximum amount of physical memory Reporting Services will use; it is also configured as a KB value within the RSReportServer.config file. After this threshold is hit and exceeded for a period of time, Reporting Services recycles the app domains to reduce memory utilization. This will ensure that there is enough memory left for the operating system to complete its task. You can increase this value if you want to process more reports concurrently.

MemorySafetyMargin and MemoryThreshold: These values define how aggressively the file system cache is used.MemorySafetyMargin defines the boundary between low-pressure and medium-pressure scenarios, with a default value of 80%. MemoryThreshold defines the boundary between medium-pressure and high-pressure scenarios, with a default value of 90%. Both are configured within the RSReportServer.config file.

In general, if you are constantly hitting memory thresholds, it is recommended that you consider scaling up (adding more memory, altering these configuration values) and then scaling out. You should scale up first because resources are better managed in SQL Server 2008 Reporting Services.

Memory Configurations for SQL Server 2005 Reporting Services

You can scale up by adding more memory as well. But the impact may not be as dramatic; one scenario might see a doubling of memory and CPU (to 16 GB RAM and 8 cores) with only 1/3 increase in capacity. Nevertheless, there are things you can still do to scale up SQL Server 2005 Reporting Services prior to scaling out.

As with SQL Server 2008 Reporting Services, you can use memory configurations to address memory threshold issues. There are two primary memory configurations in SQL Server 2005 Reporting Services that we recommend you change if you’re constantly hitting memory thresholds:

MemoryLimit: This configuration is similar to WorkingSetMinimum in SQL Server 2008. Its default is 60% of physical memory. Increasing the value helps Reporting Services handle more requests. After this threshold is hit, no new requests are accepted.

MaximumMemoryLimit: This configuration is similar to WorkingSetMaximum in SQL Server 2008. Its default is 80% of physical memory. But unlike the SQL Server 2008 version, when its threshold is met, it starts aborting processes instead of rejecting new requests.

While we do recommend memory configuration changes if you’re constantly hitting memory thresholds, note that changing these values might create or exacerbate other resource contentions.

Troubleshooting Reports: Report Performance

1.     My data takes too long to retrieve

Filter, sort, and aggregation is more efficient on the data source than during report processing. Write queries to return only the level of detail that you show in the report. The following list suggests ideas for evaluating each report query in the report:

·         Write queries with WHERE clauses or HAVING clauses that limit the data to just what the user must see in the report. Use query parameters to restrict data that is retrieved at run time. Query parameters are automatically bound to corresponding report parameters, and enable a user to decide which data they are interested in. For more information, Please see below

USE AdventureWorks2008R2;

GO

SELECT OrdD1.SalesOrderID AS OrderID,

       SUM(OrdD1.OrderQty) AS "Units Sold",

       SUM(OrdD1.UnitPrice * OrdD1.OrderQty) AS Revenue

FROM Sales.SalesOrderDetail AS OrdD1

WHERE OrdD1.SalesOrderID in (SELECT OrdD2.SalesOrderID

    FROM Sales.SalesOrderDetail AS OrdD2

    WHERE OrdD2.UnitPrice > $100)

GROUP BY OrdD1.SalesOrderID

HAVING SUM(OrdD1.OrderQty) > 100;

·         Write queries with the ORDER BY clause to presort data that is retrieved for a report. Sort the data in the order you want it sorted in the report. Presorted data improves report processing time because of the way it is stored in memory. Many report processing tasks do not require sorting data before processing it. For example, SUM is not order-dependent. Data within group instances is not automatically sorted. If you do not need sorted data in the report, do not set sort expressions on the dataset or data region. For more information, Please see below

USE AdventureWorks2008R2;

GO

SELECT ProductID, Name FROM Production.Product

WHERE Name LIKE 'Lock Washer%'

ORDER BY ProductID ;

·         Write queries with GROUP BY to aggregate values on the data source.Many times, the most effective way to communicate information is by aggregating values and displaying summaries. You can calculate some level of aggregates on the data source and retrieve them for a dataset. The "detail" data in the dataset now represents aggregates calculated on the data source. For more information, Please see below

To aggregate values for all rows

2.     Be sure the table you want to aggregate is already present in the Diagram pane.

3.     Right-click the background of the Diagram pane, then choose Group By from the shortcut menu. The Query and View Designer adds a Group By column to the grid in the Criteria pane.

4.     Add the column you want to aggregate to the Criteria pane. Be sure that the column is marked for output.

5.     In the Group By grid column, select the appropriate aggregate function, such as: Sum, Avg, Min, Max, Count. If you want to aggregate only unique rows in the result set, choose an aggregate function with the DISTINCT options, such as Min Distinct. Do not choose Group By, Expression, or Where, because those options do not apply when you are aggregating all rows.

6.     The Query and View Designer replaces the column name in the statement in the SQL pane with the aggregate function that you specify. For example, the SQL statement might look like this:

SELECT SUM(price)

FROM titles

If you want to create more than one aggregation in the query, repeat steps 3 and 4.

7.     When you add another column to the query output list or order by list, the Query and View Designer automatically fills the term Group By into the Group By column of the grid. Select the appropriate aggregate function.

8.     Add search conditions, if any, to specify the subset of rows you want to summarize.

After these pre-aggregated values are in a report, you can continue to aggregate the values as long as you are using an aggregate function that is mathematically transitive, for example, SUM. For example, assume that you have a set of 6 values: 1, 2, 3, 4, 5, 6. If you group the values into pairs, you have a set of 3 values: 3, 7, 11. You can calculate the sum on the first set (21) and calculate the sum of the second set (21) and the sums are the same regardless of the grouping. If you average the values in the sets by using the AVG function, you get a different result for each set. The average for the set of 6 is 21/6 or 3.5. The average of the set of 3 is 21/3 or 7. AVG is not a transitive function.

Consider the amount of data needed for a chart or gauge. Drawing hundreds of points in a few pixels on a monitor degrades performance and does not enhance the visual display of the graphics. More than 7 or 8 slices in a pie chart is of questionable value. 

For report items with conditional visibility, the report processor must apply grouping, sorting, and filtering expressions even if only the top level of data is at first visible. Although on-demand processing in SQL Server 2008 Reporting Services optimizes data evaluation by processing only data that is visible, all possible data is part of the report. If the user is only interested in seeing detail data some of the time, a drillthrough report is a better choice.

2. Query Times Out

Query time-out values are specified during report authoring when you define a dataset. The time-out value is stored with the report, in the Timeout element for the Query. By default, this value is set to 30 seconds.

Large Amounts of Network Traffic Cause Wait Times for the User

Large amounts of data passed as network traffic can introduce wait times for the user. Depending on your expected user base and the expected volume of report views, you can select the appropriate approach for deploying report server components.

For example, the following strategies might help reduce the wait times for the user:

Keep the report server catalog on the same computer as the report server.

The report server database tempdb manages report data that is retrieved for each dataset query in a report definition. Keeping report data with the report processor reduces network traffic that can slow report exeuction.

For data warehouse data sources, keep the data warehouse on a separate server than the report server.

Although retrieving data across the network does add an extra task for report execution, having both the data warehouse and Reporting ServicesServices that contend for memory on the same server can slow performance.

3. Report data displays incorrectly when site server and client are different language versions

When you display reports containing data from client computers running a different language version of Windows than the version installed on the site server, localized report data from the client computer might display incorrectly in reports generated by SQL Reporting Services.

Solution

There is currently no workaround to this issue.

4. Rerunning a displayed SQL Reporting Services report after changing its properties does not refresh the display of the report in the Configuration Manager console

When you modify the properties of a displayed SQL Reporting Services report in the Configuration Manager console and then rerun the report, the report display is not refreshed.

Workaround

Use one of the following workarounds:

In the Report Options dialog box, select Open reports in a new window.

Close and then reopen the Configuration Manager console, and then run the report again.

5. Report links in the Configuration Manager console do not work after removing a reporting services point

If you have specified Use Reporting Services Reports for Admin console report links in the Report Options dialog box and then remove the reporting services point site role, report links in the Configuration Manager console will fail to display and you will receive the error message Unable to launch the report because it does not exist on the report server or the report server is unavailable.

Solution

Use one of the following solutions:

Clear the Use Reporting Services Reports for Admin console report links check box before removing the reporting services point site role.

In the Report Options dialog box, reselect the reporting point under Classic Reporting and then click OK.

6. Cannot run reports from the Configuration Manager console

When you try to run a SQL Reporting Services report from the Configuration Manager console, the report fails with insufficient privileges.

Solution

When reports are retrieved from SQL Reporting Services to the Configuration Manager console, the report properties are also retrieved. To retrieve these properties, use one of the following workarounds:

Modify the Browser role to include the Consumes Reports privilege.

Create a new role with the same privileges as the Browser role, and add the Consumes Reports privilege to this role.

Run the report directly from your SQL Reporting Services Web page.