Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL report returning slowly Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 12:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 1:56 PM
Points: 156, Visits: 517
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!
Post #1408563
Posted Thursday, January 31, 2013 3:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 8:51 AM
Points: 132, Visits: 581
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.
Post #1414368
Posted Friday, February 1, 2013 8:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 27, 2014 8:47 PM
Points: 316, Visits: 1,483
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



Post #1414711
Posted Thursday, February 7, 2013 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:11 AM
Points: 4, Visits: 103
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/

Post #1417248
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse