January 17, 2013 at 12:20 pm
I have a report running on our production Reporting Web server that is just spinning. When I run the procedure that the report runs directly on SQL Server using SSMS, it returns in a reasonable amt of time. I thought it might have something to do with the amt of rows that the report returns and that seemed to be it as I modified the stored procedure to return just the top 10 rows and it returned relatively quickly. However, our development reporting server runs a copy of the procedure except it pulls the same data from production via linked server and it returns the data quickly. So now I'm thinking that maybe production is running short of memory. How can I tell if lack of memory is a problem on reporting services? And if not, what else could be the bottleneck
Thanks much!
January 31, 2013 at 3:11 pm
Check the ExecutionLog view on the ReportServer database. The view will give you information on duration spent on Data Retrieval, Processing, and Rendering.
Compare the logs from Prod vs Dev and see where the difference is for the specific report. If the processing is taking up a lot on Prod, verify that you don't have resource issues around CPU, RAM, and disk queue. Reportserver caches temporary data in ReportServerTempDB. This allows you to go through pages of reports and re-render the data without having to re-query the database. For reports that return lots of data, the bottleneck might in caching the data.
February 1, 2013 at 8:43 am
We've run into a couple of cases where a report ran slow but the same query in SSMS responded just fine. In those instances the problem was either an issue with parameter sniffing occurring in a given stored proc or that a stored proc was returning "select * from
" rather than explicitly listing the fields to be returned by the stored proc.
--pete
February 7, 2013 at 10:50 am
If your report involves calling a Stored Procedure with parameters, you may want to check out this link about Parameter Sniffing. Option 2 worked for my report.
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply