August 21, 2013 at 9:32 am
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
August 21, 2013 at 11:04 am
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
August 21, 2013 at 12:16 pm
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 TimeProcessingTimeRendering
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
August 21, 2013 at 1:09 pm
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
Consultant - Straight Path Solutions
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
August 22, 2013 at 9:44 am
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
August 22, 2013 at 10:28 am
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
Consultant - Straight Path Solutions
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
August 23, 2013 at 2:03 pm
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
September 5, 2013 at 7:48 pm
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.
September 6, 2013 at 6:52 am
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:
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
September 6, 2013 at 8:18 am
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
Consultant - Straight Path Solutions
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
September 6, 2013 at 10:00 am
robert.gerald.taylor (9/6/2013)
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:
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
Hi Nick,
I have had to do what Rob suggested with the internal parameters. That worked for us.
So you have parameters passed to the stored procedure, then you declare variables at the top of the stored procedure and assign the parameter values to them, and use the local variable in the code...
example:
Alter Procedure dbo.My_Stored_Proc (@Var1 int, @Var2 varchar(50))
AS
Declare @Local_Var1 INT, @Local_Var2 varchar(50)
Set @Local_Var1 = @Var1
Set @Local_Var2 = @Var2
/****
code block
*****/
WHERE database_field1 = @Local_Var1
and database_field2 = @Local_Var2
Hope that helps,
Sarah
September 6, 2013 at 10:54 am
mysorian (9/5/2013)
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.
I have no idea why this worked, but it did. My execution time went from 15 minutes + to 15 seconds. I am not sure why but I am fine with the outcome. Thanks for this off the wall idea.
Nick
September 6, 2013 at 12:32 pm
What does it mean to "pick up the report in Report Builder"?
Does that mean open the .rdl file in Report Builder?
Thanks for the clarification,
Sarah
September 6, 2013 at 12:41 pm
nick947 (9/6/2013)
I have no idea why this worked, but it did. My execution time went from 15 minutes + to 15 seconds. I am not sure why but I am fine with the outcome. Thanks for this off the wall idea.
I'm glad that did the trick for you; I've had a similar experience recently.
Rob
September 6, 2013 at 1:36 pm
sarah.sickinger (9/6/2013)
What does it mean to "pick up the report in Report Builder"?Does that mean open the .rdl file in Report Builder?
Thanks for the clarification,
Sarah
I pulled the .rdl out of my solution and save it to my desktop. Then I opened it in RB 3.0 (which was a very slow experience). I tried to publish through RB but that was not what I wanted, so I made sure I could run it in RB (I had to change the shared dataset). Finally, I used the upload feature in the folder I wanted and ran the report.
Nick
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply