March 28, 2016 at 4:01 pm
Hopefully someone here can give me some theories on whats up with some ssrs instability ive faced lately...
This has happened across several servers I administer for customers with heavy reporting uses. I'll give details for a specific example that happened today as its details are fresh in my mind and hopefully someone can give me some ideas on where to look...
somewhere in the 7am window on sunday, report performance went from 5-8 seconds on avg for a particular report to 90 seconds. It wasnt table stats, it wasnt memory pressure, or anything like that. the 'solution' was to restart ssrs. As I went through the SSRS history, I noticed that almost all reports were affected in the same way. Their avg durations all returned to normal after the issue was reported to me and I restarted SSRS as a 'maybe that will fix it cause I dont see anything else wrong' shot in the dark.
The server has 2014sp1, 64b of ram, 48max memory for sql server and had 13gb available at the time of the restart today. the SSRS process was at about 1.3gb. this was a full 24 hours after the problem began. perf instantly returned to 'good' with a restart. These reports were still completing, just in 10x or more their normal duration. after the restart, ssrs climbed to 3gb+ of memory and was flying through them. ssrs memory wasnt high around the time of the problem starting.
Nothing apparent in error logs, no cpu pressures on the server, IO throughput was very steadily acceptable, nothing in ssrs logs to give me any clues...EXCEPT...I notice that SSRS went through its every 12 hour AppDomain unload routines around the time this problem arose. It did the same 3 more times before the problem was reported to us. Could that be related?
Im scheduling an SSRS Restart daily now, and have some monitoring on report history of critical reports so we can at least get a quicker heads up if ths happens again.... however... what would cause this pattern of ALL reports behaving badly until an SSRS restart, then they work fine for weeks on end? The timing of this doesnt correlate with any patterns, subscriptions, resource spikes or anything else I can identify.
any help or suggestions anyone could offer would be fantastic.
March 28, 2016 at 4:56 pm
one other point, even our simple 'test' reports that retreive the simplest top result from a lookup table are problematic in these 'slow' periods before an SSRS restart is performed, then they render almost instantly.
March 30, 2016 at 7:20 am
SSRS running slow is quite often an indicator that it needs more memory.
Appdomains unloaded is a clear indicator that it needs more memory.
Reduce max server memory to 40 GB and see how that affects things.
March 30, 2016 at 7:41 am
The AppDomain Unload messages in the SSRS Log happen on a pretty steady 12 hour cycle as per the 720 minute default detailed here:
https://msdn.microsoft.com/en-us/library/bb934330(v=sql.120).aspx
the one that I correlated with our overall slowdown happened at 722am, the next happened at 722pm that day, and again at 722am the following day...
And at the time of that slowdown, our collected perfmon metrics show over 12gb available and free, and 1.3gb Private Working Set for the SSRS process.
Id expect if this was memory pressure a higher memory footprint for the service and low free/available server memory...
Also, this all cropped up in a relatively low use period for this particular server/customer...
thanks for the reply, much appreciated to have some other perspective!
March 30, 2016 at 7:48 am
and also, not to discount your suggestion... please dont take it that way...
I dont expect that we will be able to determine any 'success' on reducing sql max to 40gb since we've now scheduled a daily ssrs restart. Im expecting that will keep this problem from arising again, and prevention is more impt at this point than leaving the status quo to see IF we can learn more from this if/when it happens again...
March 30, 2016 at 7:59 am
What version(s) of SQL Server and what architecture (32 or 64 bit)?
What are the memory settings for your SSRS instance? (see https://technet.microsoft.com/en-us/library/ms159206%28SQL.100%29.aspx)
You could also try setting the “appdomainmanager” trace level to 4 (verbose) in the RS config file to get detailed messages.
The issue with memory may not be related to how much free memory there is but rather how much memory the appdomain has to use.
March 30, 2016 at 8:06 am
answering your questions, will review the link you gave and respond separately once Ive digested it...
This is a 2014SP1 x64 enterprise server. SSRS Memory setting are left with default values for now.
Weve had similar issues with this customer (before we recently upgraded them) and others on 2008R2 SP2 and SP3 as well...
thx!
March 31, 2016 at 6:15 am
If Im understanding that correctly, assuming default memory conditions, it wont hit a 'working set maximum' until it consumes all available memory on the server...
"By default, the report server sets WorkingSetMaximum to the amount of available memory on the computer. This value is detected when the service starts."
That would have been a high value, given the service starts automatically and hadnt been restarted since last server reboot...
and the 'High Memory Pressure' condition wouldnt have been detected till we were within 60% of that total.
which makes me think that if this service was under tremendous memory pressure, why didnt SSRS it grab as much of the available memory as it was needing? There was plenty left available on the server in this timeframe. and report were still processing and being accepted, they showed a success status in the history, just high durations... and there werent any any out of 12hr cycle app domain unloads in the log file either...
I thnk we have the problem 'solved' at this point, just still trying to wrap my head around what caused it and how/why/if the correlation between one of the routine 12hr app doamin unload events might have caused it.
thanks again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply