March 19, 2018 at 8:11 pm
Hi everyone,
We upgraded to SQL Server 2016 enterprise edition (CU 7 Jan 2018) about 3 weeks ago, from SQL Server 2014 standard, and migrated SQL Server databases, SSIS jobs, SSRS and SSAS cubes. SSRS has a bigger foot print in terms of number of users utilsing the reports amongst other components in our environment, and we have started to see unexpectedly higher resource usage, particularly CPU usage on SSRS, amongst other issues. This box is mainly a reporting server, which is used to run ETL jobs (overnight), SSRS and SSAS cubes. Out of 165 users, SSAS users are about 20, or 22, rest of them are SSRS users.
Our key pain points are
- Higher CPU utilisation for Reporting Services (task manager stats)
- Several reports being executed multiple times (SSRS execution log, log2 and log3)
- Oracle reports not working properly. This is interesting one, after restarting the box, some of the Oracle reports that a log on trigger specified on Oracle side for row level security, works, for few hours, and then the report displays blank report. My Oracle developers tell me that the log on trigger stops getting fired. We have our doubts around connection pooling.
- SSRS becoming resource intensive to the point where it hangs and we have restart the service, after which it behaves for a while until becoming unresponsive, and the cycle goes on.
Interestingly, the previous environment, with the same amount of reports, and almost same amount of users, we did not face any of the issues. The Oracle reports as expected, CPU usage is under control and no outages. We were expecting to see the same behavior after upgrading to 2016 but certainly that's not the case.
My point is, that with same amount of configuration, same reports and same # of users, without using any of the features that enterprise edition offers us, we are facing these issues. Could it be that there are some enterprise edition specific configuration that we have missed? I had a looked at parallelism, MAXDOP settings in previous environment, and they are set to default, out of the box, and same is the case with 2016 box.
Any thoughts and guidance will be highly appreciated.
Thanks
Kaz
April 24, 2018 at 7:42 am
What quantity of RAM is on that server? Loading up SSAS and SSRS on the same box is often a RAM hungry scenario. How large are the databases that SSRS is reporting on? It could be that with new execution plans having been generated, some of them may not be so good. Are your statistics up to date? Is it possible that you were operating okay before, but were unknowingly on the edge? Seen that a number of times, but you might never know unless you had kept stats from prior to the upgrade, and can compare those same stats with current state. You may "appear" to have the same workload, but do you really? Users are often hush-hush about anything that uses a lot of resources, lest they be blamed for a problem. Do you track overall workload in any way? Just a few things to think about...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply