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]
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 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.
[TABLE]
There are many ways to tune Reporting Services. Here are some general tips:
[TABLE]
You can modify some parameters in the Report Server Configuration File (RSReportServer.config) to reduce or change th
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
e 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.
[TABLE]
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.
