October 24, 2007 at 1:42 pm
I just recently published an application to our intranet that is utlizing reporting services and I am seeing a massive increase in our average disk queue length. Is anyone familiar with this happening? We currently have our transactions logs and database all sharing the same disk which I know isn't recommended. Currently we've had over 100+ users hitting the reporting application that was created. Prior to know we were using a SQL Server 2000 report server, but for this app we decided to publish on our 2005 instance. Everything was smooth up until deployment. If anyone has any suggestions I would greatly appreciate it.
October 24, 2007 at 3:03 pm
I would start w/ the T-SQL in the report. Look for the ususal suspects, missing indexes, statistics, etc. Profile the T-SQL, look for high reads/writes. Try running an update statistics with a fullscan on all of your indexes.
Tommy
Follow @sqlscribeOctober 24, 2007 at 6:48 pm
SSRS's backend databases are a killer on disk I/O in general, so I am not surprised by this. A lot has to do with how the reports are built, how much data, etc. as the reports are cached in ReportServerTempDB. You might profile against ReportServer and ReportServerTempDB and look at the I/O stats. If you're seeing a lot of I/O due to ReportServerTempDB, it's likely due to the cached reports and temporary data that is being stored.
You might take a look at the following Microsoft whitepaper (Word document):
Planning for Scalability and Performance with Reporting Services
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply