SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL report returning slowly


SQL report returning slowly

Author
Message
Steve-443559
Steve-443559
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 602
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!
richykong
richykong
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 621
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.
peterzeke
peterzeke
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1962 Visits: 1766
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 [table]" rather than explicitly listing the fields to be returned by the stored proc.

--pete



joy.mullins
joy.mullins
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 117
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/

:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search