Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server Reporting Services analysis and tuning

Commands running in your report server may be preventing real-time performance. Analyze the impact of performance hogs and get help tuning Reporting Services.

While working on a project, I wanted to monitor SQL Server's activity using SQL Profiler. My private SQL Server...

was not in use by any application or user at the time of monitoring. I ran the trace and realized that the report server was issuing SQL commands every few seconds. I decided to analyze the impact of this activity on database servers and figure out how to change this behavior.

Monitoring report server

After running the trace, I received the results shown in Figure 1. I filtered the report server database only.


Figure 1: Monitoring results found in SQL Profiler, report server database.

For example, you can see the following command runs every 10 seconds. It shows activity that occurs in the report server itself. Remember that the reports can degrade performance.

As another example, the following command runs every minute:

exec GetMyRunningJobs @ComputerName = N'MYSERVER', @JobType = 1

Some of the repeated commands perform few reads. My server has nothing else running on it at the time of monitoring. I've seen a higher amount of I/O for these commands on other active servers with more than a few reports.

If your database server must have near real-time performance or it is heavily used by other applications, such behavior can degrade performance. If you need to know by how much, test your specific environment.

Performance-tuning Reporting Services

There are many ways to tune Reporting Services. Here are some general tips:

  • Move the Reporting Services service and databases to a different server if needed, or expand your current server.
  • Depending on report characteristics and application logic, it is sometimes better to have a copy of data set aside for reporting purposes. This will garner better performance in both environments.
  • For example, if your reports are for decision support or reading static historical data, you can copy the needed data on a periodic basis and set it aside. You can also use continuous replication for reporting so as not to interfere with the OLTP environment, maybe having different indexes, data structures, etc.
  • If a copy of the data is not available, using a with (nolock) hint or a transaction isolation level read uncommitted (dirty read) in the reports' queries can improve performance and solve locking problems. This can be accomplished only if the database design and application logic permit dirty reads.

Report Server Configuration File

You can modify some parameters in the Report Server Configuration File (RSReportServer.config) to reduce or change the amount of activity in the report server.

This XML file is located by default in: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer.

After changing it, you just have to restart the report server service. Here is an example of the main parameters that affect the commands that run in intervals on my server. You can play with the parameter values until the load satisfies your server's need.

Parameter Description Affected command
CleanupCycleMinutes Specifies the number of minutes after which old sessions and expired snapshots are removed from the report server databases. Valid values range from 0 to maximum integer. The default is 10. Setting the value to 0 disables the database clean up in the process. ------------- #1 -----------
declare @P1 int
set @P1=0
exec CleanExpiredSessions @SessionsCleaned = @P1 output
select @P1

------------- #2 -----------
exec CleanExpiredCache

------------- #3 -----------
exec CleanBatchRecords @MaxAgeMinutes = 10

------------- #4 -----------
exec CleanOrphanedPolicies

------------- #5 -----------
declare @P1 int
set @P1=0
declare @P2 int
set @P2=0
exec CleanOrphanedSnapshots @SnapshotsCleaned = @P1 output, @ChunksCleaned = @P2 output
select @P1, @P2

------------- #6 -----------
exec CleanExpiredJobs
 
RunningRequestsScavengerCycle Specifies how often orphaned and expired requests are canceled. This value is specified in seconds. Valid values range from 0 to maximum integer. The default is 60. -
IsNotificationService Specifies whether the report server dedicates a thread to polling the notification table in the report server database to determine if there are pending notifications. Valid values include True (default) and False. If false, the following command will not run:

exec sp_executesql N'
declare @BatchID uniqueidentifier

set @BatchID = newid()

UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
 
IsEventService Specifies whether events are processed in the event queue. Valid values include True (default) and False. If false, the following command will not run:

declare @BatchID uniqueidentifier

set @BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @BatchID,
[ProcessStart] = GETUTCDATE(),
PollingInterval Specifies the interval, in seconds, between polls of the event table by the report server. Valid values range from 0 to maximum integer. The default is 10. exec sp_executesql N'
declare @BatchID uniqueidentifier

set @BatchID = newid()

UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
 

 

Related Reporting Services tuning links

You can control the amount of commands issued by Reporting Services Management processes by changing parameters in the RSReportServer.config configuration file, as needed. If you realize -- after monitoring your heavily used server -- that Reporting Services management processes do affect its performance, you should consider moving it to a separate server or expanding your existing server.


ABOUT THE AUTHOR:

Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an information technology consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. She has worked exclusively with SQL Server for the last 10 years, consulting for a diverse group of clients. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more.
 

 

This was last published in July 2006

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close