June 29, 2015 at 12:49 pm
I have a report that has been around for years, first in 2008 then migrated to 2012. It has been on 2012 for at least six months. Users execute it from their application hundreds of times each hour. Twice in the past week it has suddenly jumped from 1 second to 3+ min run times, which times out their application. Nothing has changed from a hardware standpoint nor has the report or stored proc changed.
Upon investigating ExecutionLog table I can see the TimeDataRetrieval jump from averaging 10-30 ms to averaging 180090-180110 ms. TimeDataProcessing and TimeDataRender stay pretty consistant. The increased retrieval time stays at that new level until is restart SSRS. Even while the report is running slow, I can still run same query with same parameter directly on content db in Management Studio milliseconds. Running it directly from SSRS gives same times as from the application.
ReportServer Database is 2012 on ServerA
Content Database is 2008R2 on ServerB
SSRS is 2012 on ServerC
We have a dozens of folders and hundreds of reports in SSRS, yet only this one report is impacted.
If it were a lock on the data, I figure the running the query directly would have same delay and/or the time would drop back on it's own.
While reports were running in the 3+ min, the resources on the ServerC showed less than 50% of memory and CPU being used.
Nothing in any logs I can find to indicate what may have happened.
Am I missing a log? Is there some other table/view that might have info? Any suggestions where to begin troubleshooting?
One time of "if in doubt, restart" I can live with, but twice in less then one week doesn't fly with upper management.
June 29, 2015 at 2:06 pm
Sounds like either "bad" parameter sniffing. You can check the parameter values for the slow runs to normal runs by using ExecutionLog3.Paramters column and see what values are causing the skew.
Updating statistics on the table(s) involved can potentially solve the problem as well. It really depends on the shape of the data in the table(s). If you have data skew, for example in phone book, Smith vs. Xavier, where the best plan for SMith is a scan and the best plan for Xavier is a seek, and you get a seek for Smith because Xavier was the value sniffed at compile time, you'll have issues. Then you can use something like OPTION RECOMPILE or OPTIMIZE FOR in your query to help the query perform consistently.
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
July 9, 2015 at 7:32 am
That doesn't explain how the report can run for over a year without issue, suddenly jump in time and stay at the longer time until SSRS is restarted. As soon as we restart the service, everything is back to normal for weeks.
If it were statistics, we shouldn't see the time drop again after service restart. No action was taken on the database itself.
If it were parameter sniffing, wouldn't it be spiking based on parameter? Or stay around the same time frame?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply