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.
TABLE OF CONTENTS
[IMAGE] Monitoring report server with SQL Profiler
[IMAGE] Performance-tuning Reporting Services
[IMAGE] Modifying the Report Server Configuration File
[IMAGE][IMAGE] Monitoring report server[IMAGE] Return to Table of Contents
After running the trace, I received the results shown in Figure 1. I filtered the report server database only.
[IMAGE]
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.
[IMAGE]
As another ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
[IMAGE][IMAGE] Performance-tuning Reporting Services[IMAGE] Return to Table of Contents
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.
[IMAGE][IMAGE] Report Server Configuration File[IMAGE] Return to Table of Contents
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.
