December 16, 2008 at 8:18 am
I am not sure where is the best place to make this post. I'll start here and move it as recommended.
I am running into a problem each morning where my server is brought to its knees by Reporting Services. I believe the problem is being caused by too many reports being ran; both by SSRS Subscriptions and users running reports directly. When this occurs, the CPU is at 100%. Disk usage is fairly high as well.
Almost all of the reports being ran come from the same report, just different parameters set by the users. When I run this report by itself it will run in about 45-60 seconds. While running it will max out a CPU for brief periods but mostly run aroun 10-15%. Exporting to Excel will max out a CPU for the duration of the export which takes about 60 seconds.
Before the users start using the system, all indexes are rebuilt and statistics are updated via a SSIS package. These two steps provided a significant increase in performance.
I have all of the SSRS Subcriptions using the same Shared Schedule. My hope was that SSRS would run the reports serially and thus avoid the problem I am having. Instead it appears that all the reports (between 6 and 10 reports depending upon the day) are trying to run at once. One to two hours later very few of the reports have completed.
Any thoughts on what I can do?
Rob
December 16, 2008 at 9:03 am
Tune the query is the first thing that comes to mind. Make sure you've got the right indexes in place on the tables and that the query is using them appropriately. Is the query against a reporting system or are you also having to contend with inserts & updates as the report runs? If not, make the database read only. That will eliminate locking and probably increase performance some (not tons, but every little bit helps). The query seems to be the key. You need to make it faster.
Have you looked at execution plans? Have you checked wait states to see what the various processes are waiting for because it's not just the CPU.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply