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 12»»

SSRS 2012 Report Issue On Server Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:56 AM
Points: 20, Visits: 412
I am having an issue with one of my reports. When I run the report in BIDS 2010 there are no problems, the report runs in a few seconds. However, once I deploy it to our server running SSRS 2012 I run the report and loading animation is the only thing that shows for over 10 minutes before I close my window. I thought at first this might be an issue with parameter sniffing, so to combat that I used the recompile hint and temporary variables in the stored procedure. I am out of ideas of what this could be. Any help would be appreciated.

Thanks,
Nick
Post #1486775
Posted Wednesday, August 21, 2013 11:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 1,163, Visits: 1,187
nick947 (8/21/2013)
I am having an issue with one of my reports. When I run the report in BIDS 2010 there are no problems, the report runs in a few seconds. However, once I deploy it to our server running SSRS 2012 I run the report and loading animation is the only thing that shows for over 10 minutes before I close my window. I thought at first this might be an issue with parameter sniffing, so to combat that I used the recompile hint and temporary variables in the stored procedure. I am out of ideas of what this could be. Any help would be appreciated.

Have you queried the ReportServer.dbo.ExecutionLog to see if it is the TimeDataRetrieval that's way out of wack?

I just recently had run into a similar problem and it turned out to be a param sniffing issue -- it sounds you've already went down that path though.

Good luck,
Rob
Post #1486812
Posted Wednesday, August 21, 2013 12:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:56 AM
Points: 20, Visits: 412
Here's the data from execution log. I am not sure what time processing does, but looking at time data retrieval seems to tell me that there might still be a parameter sniffing problem here.

TimeDataRetrieval 	TimeProcessing	TimeRendering
304629 601695 29
309608 601517 19
310099 601629 26
309907 601502 18
309623 601661 20
309794 601533 20
30028 60111 0
312148 602043 29


Rob,
Do you know any other tricks to get past parameter sniffing?

Nick

Edit: For formatting
Post #1486846
Posted Wednesday, August 21, 2013 1:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 11,157, Visits: 12,897
The execution log also shows the parameters that are being passed to the report. Looking at that might help determine if parameter sniffing is the problem.

When run on the server it seems like the report is pretty consistent except for one run, so the row count might be helpful there as well.

Have you looked at the execution plan(s) for the query in question?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1486883
Posted Thursday, August 22, 2013 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:56 AM
Points: 20, Visits: 412

I am passing the same parameter for each of the runs (This is a yearly report).

You were right. For that one row with the lower times was actually an abort by the user.

I have looked at the execution plan and nothing shocking is jumping out other then some convert issues at the top of the query. However, I have never run into these before.

Nick
Post #1487364
Posted Thursday, August 22, 2013 10:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 11,157, Visits: 12,897
If you are passing the same parameter value for each run then parameter sniffing is not the issue.

Still it looks like the processing time on the report server is the biggest bottleneck. You may want to look at the memory usage on the report server to see if there is a bottleneck there.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1487401
Posted Friday, August 23, 2013 2:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 5:56 AM
Points: 20, Visits: 412
I am unfamiliar with troubleshooting ssrs. . . Do you know of any tutorials or references that might help me decide if it is a memory issue?


Nick
Post #1488009
Posted Thursday, September 5, 2013 7:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:39 PM
Points: 40, Visits: 79
This is just an experiment I want you to try.

Save your report to the file system. Pick up the report in Report Builder. Now deploy it to the server. Do you see the same thing? I am not suggesting this will solve your problem, but just take a look.

I have run into similar problem recently. I am suspecting the database drivers. Have a look here,

http://jayaramkrishnaswamy.sys-con.com/node/2777798

Make sure you look up the last link on the above article.
Post #1492019
Posted Friday, September 6, 2013 6:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 1,163, Visits: 1,187
nick947 (8/21/2013)

Do you know any other tricks to get past parameter sniffing?

Nick, here's a link explaining parameter sniffing and how to work around it:
http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices-150-Parameter-Sniffing.htm

It is probably worth trying to change your query so that you assign the parameters to internally defined variables and then use those internal variables in the rest of your query (as opposed to using the parameters directly).

HTH,
Rob
Post #1492202
Posted Friday, September 6, 2013 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 11,157, Visits: 12,897
Can you post the execution plan (.sqlplan, not just an image) and the query?

Looking again at the values you posted from the execution log there doesn't really seem to be a large discrepancy in execution times except for the aborted run.

To really see if it is bad parameter sniffing (parameter sniffing isn't necessarily a bad thing), you can look at the properties of an actual plan. If you have the graphical plan open and right-click on the Root Operator on the left side and select properties, take a look at the Parameter List and it will show the Compiled Value and Run Time Value. IF you get wildly different performance when the Actual is different than the Compiled than it is probably bad parameter sniffing. You also need to look at the distribution of data based on the parameters to see if there are a wide range of ranges.





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1492266
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse