August 31, 2011 at 2:23 am
I've got a number of reports which for some reason on occasion can run for a very long time. For example a query taking 20 seconds the report will typically take 30 seconds yet every now and then it can take up to 20mins!
I'm going through all the reports and optimising them and adding nolocks to the joins so the queries don't cause problems on our CRM etc, however, it would be good whilst I'm going through these if I could set a per project or global maximum execution time.
I see from here: http://msdn.microsoft.com/en-us/library/ms155782.aspx that one can configure this on a per data set level, which is great, but quite time consuming when you've got a huge number of reports!
I see time out in seconds of 0 on the data sets I've looked at...
Any thoughts or advice greatly appreciated.
Thanks
September 8, 2011 at 8:28 am
The ReportServer.dbo.ConfigurationInfo has a value called "SystemReportTimeout" that by default is set to 30 minutes I believe. This should be what your looking for.
September 8, 2011 at 8:39 am
Thanks, that's very helpful, I've located that value in that table and I'll discuss the options with a colleague to see what we think might be a good value here.
September 8, 2011 at 8:44 am
Use nolock only if management APPROVES getting incorrect reasults. Which they never do.
Change your db to allow snapshop isolation and use that in all your queries & sps.
It increases load on tempdb tho.
For the long running reports check for bad parameter sniffing. You'll either need to use with recompile (on by default on all my reports since this is low volume and cpu is almost idle anyways), or option 2 is to figure out what "bad" parameters are and make a 2nd version of the code for those so that you get correct plans for each set of parameters.
Blocking would be a likely candidate as well here. Snapshop Iso. would very likely take care of that one too.
September 8, 2011 at 8:55 am
Thanks for your ideas here.
I can see occasions where nolock would provide information that's incorrect, however, in our situation they would be rare and they'd be far more desirable than locking up our client database or something like that.
As regards parameter sniffing, yes, I discovered that in a report a few weeks back, cut down the execution time by a huge amount when I used local parameters, I was very happy!
The snapshot option looks interesting, a very quick read of an article suggests that a cut of the required data will be isolated in a temp db and the query executed against this as opposed to the actual tables. It's certainly something I'll read up on more as it does sound like it could offer us some advantages.
September 8, 2011 at 9:07 am
Snapshot will allow you to ALWAYS get the correct results with the same speed as nolock.
Also nolock can actually be locked out of some dml operations. It's not a magic get fast button. Snapshop will not be blocked on those.
Many of the reasons to NOT use nolock => http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
September 8, 2011 at 9:20 am
I wasn't aware of the snapshot side of things but I'll give it a look as I said.
As regarding the nolock situation, I'm aware it has pitfalls and I did read about those before altering a bunch of stored procedures. Firstly I should state that our reports don't contain financial information and that any business critical decisions aren't based on one report or even a series of reports. For us reports are more to do with identifying trends really so if we've got one result too many or one too few on the whole it won't detract from the overall conclusion.
I'd still prefer not to use them though and have not have the pitfalls of nolocks so it does sound like snapshot isolation is something I should read up on and speak to others in my team about, so thanks for the heads up here
September 8, 2011 at 9:24 am
Alright, let me know how it turns out.
Just a FYI, I've never done a report that didn't lead to a business decision (even trending). I know there's only a 0.01% chance of bad data causing bad decision, but somehow, I can't / choose not to, live with that.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply