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


SSRS 2012 Report Issue On Server


SSRS 2012 Report Issue On Server

Author
Message
nick947
nick947
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 491
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
Rob Taylor
Rob Taylor
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3712 Visits: 1616
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
nick947
nick947
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 491
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42891 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
nick947
nick947
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 491
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42891 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
nick947
nick947
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 491
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
mysorian
mysorian
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 101
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.
Rob Taylor
Rob Taylor
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3712 Visits: 1616
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42891 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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