Troubleshooting Custom Report Issues

Hi Beth!

Whenever I run into head scratchers, I like to start with this nifty little tool:  http://statisticsparser.com/.  It was written by a guy named Richie Rump, who now works for Brent Ozar.

Basically, before you execute your stored procedure in SSMS, you set statistics IO on and set statistics time on, then after it executes you copy the message results and paste it into this web page, and ‘Shazam’ it formats all the gobblety gook that makes my eyes cross into a very readable format, which is handy for scrolling to see where my reads may be skyrocketing.

But as you say, if sometimes it runs fast and sometimes slow, it may not have anything to do with reads.  So perhaps something is causing blocks?  You might also try querying the plan cache.  This post may help:  https://dba.stackexchange.com/questions/90313/sql-server-2012-stored-procedure-runs-very-slow-sometimes

Also, check out Brent Ozar’s free tools.  I can’t recommend them highly enough.  One of them is specifically for the plan cache:  https://www.brentozar.com/blitzcache/

Good luck!

Nancy

Nancy Sheleheda
Sr. Director of Application Development and Support Services
Pittsburgh Cultural Trust
803 Liberty Avenue, Pittsburgh, Pa 15222
412-456-1387
Sheleheda@trustarts.org | TrustArts.org

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Beth Hawryluk
Sent: Monday, September 18, 2017 12:11 AM
To: Sheleheda, Nancy <Sheleheda@trustarts.org>
Subject: [Tessitura Technical Forum] Troubleshooting Custom Report Issues

Hi all,

I've written a few custom reports and for the most part they all work pretty well. I have one report though that is having some serious speed issues. To be fair, it's a pretty crazy report with many calculations and I wouldn't expect it to render immediately. That said, the generation time for the report take anywhere from 2 seconds to 15+ minutes  with the exact same parameters and running it back to back. Literally. I've executed it in SSMS and after it completes I'll run it again exactly the same and it'll take a completely different amount of time. Funny enough, it often goes from running very quickly to running very slowly (which is the opposite of what I'd expect).

It is a complete gamble any time anyone runs the report. They either get it immediately or it times out before the report even renders for them. In the meantime, I've been running it for them directly out of SSMS and giving them the output that way. It's not ideal for anyone.

Since the speed issue also affects me in SSMS, I'm thinking that the issue must be with the stored procedure itself and not with the SSRS report face. I'm not sure how to make it more efficient though. I've tried looking at the Execution Plan but all it's telling me is to put in indexes where they already exist.

Do any of the reporting writing gurus have advice or tips for how they improve efficiencies in their own codes? I'm happy to show you what i've been working on offline if you contact me directly. I know it's not perfect but I'm trying! Still have lots to learn.

Thanks for your thoughts!

Beth

bhawryluk@winspearcentre.com

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hi Beth!

Whenever I run into head scratchers, I like to start with this nifty little tool:  http://statisticsparser.com/.  It was written by a guy named Richie Rump, who now works for Brent Ozar.

Basically, before you execute your stored procedure in SSMS, you set statistics IO on and set statistics time on, then after it executes you copy the message results and paste it into this web page, and ‘Shazam’ it formats all the gobblety gook that makes my eyes cross into a very readable format, which is handy for scrolling to see where my reads may be skyrocketing.

But as you say, if sometimes it runs fast and sometimes slow, it may not have anything to do with reads.  So perhaps something is causing blocks?  You might also try querying the plan cache.  This post may help:  https://dba.stackexchange.com/questions/90313/sql-server-2012-stored-procedure-runs-very-slow-sometimes

Also, check out Brent Ozar’s free tools.  I can’t recommend them highly enough.  One of them is specifically for the plan cache:  https://www.brentozar.com/blitzcache/

Good luck!

Nancy

Nancy Sheleheda
Sr. Director of Application Development and Support Services
Pittsburgh Cultural Trust
803 Liberty Avenue, Pittsburgh, Pa 15222
412-456-1387
Sheleheda@trustarts.org | TrustArts.org

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Beth Hawryluk
Sent: Monday, September 18, 2017 12:11 AM
To: Sheleheda, Nancy <Sheleheda@trustarts.org>
Subject: [Tessitura Technical Forum] Troubleshooting Custom Report Issues

Hi all,

I've written a few custom reports and for the most part they all work pretty well. I have one report though that is having some serious speed issues. To be fair, it's a pretty crazy report with many calculations and I wouldn't expect it to render immediately. That said, the generation time for the report take anywhere from 2 seconds to 15+ minutes  with the exact same parameters and running it back to back. Literally. I've executed it in SSMS and after it completes I'll run it again exactly the same and it'll take a completely different amount of time. Funny enough, it often goes from running very quickly to running very slowly (which is the opposite of what I'd expect).

It is a complete gamble any time anyone runs the report. They either get it immediately or it times out before the report even renders for them. In the meantime, I've been running it for them directly out of SSMS and giving them the output that way. It's not ideal for anyone.

Since the speed issue also affects me in SSMS, I'm thinking that the issue must be with the stored procedure itself and not with the SSRS report face. I'm not sure how to make it more efficient though. I've tried looking at the Execution Plan but all it's telling me is to put in indexes where they already exist.

Do any of the reporting writing gurus have advice or tips for how they improve efficiencies in their own codes? I'm happy to show you what i've been working on offline if you contact me directly. I know it's not perfect but I'm trying! Still have lots to learn.

Thanks for your thoughts!

Beth

bhawryluk@winspearcentre.com

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!