SSRS 2016/2017 design
-
Hi everyone,
We are looking to upgrade from SSRS 2014 to SSRS 2017, and open to both standard & non-standard editions. We have prepared a test environment having
- SSRS 2017 standard edition (CU 4)
- 32 GB of memory, 2 quad core Xeon processors
- ReportServer & ReportServerTempDB are on the same box, no other SQL databases are there, dedicated SSRS box
I transferred reports using RS utility from MS, although I had to create a C# utility to replicate folder & security structure as RS was failing to create some folders and subsequently leaving out SSRS objects within the left out folders.
We have observed that SSRS 2017 takes more time in connection opening, data retrieval and render. I have pulled these stats out from execution log3, and I have taken them into Power BI & Excel to generate sums & averages. So far, we haven't reached anywhere as to why SSRS 2017 takes a longer for these stats, either at higher level (all reports) or at the level of problematic reports. UAT with business users have shown that some reports take longer, at times SSRS 2017 took too long to respond and so forth. General feedback has been that SSRS 2017 is a bit slower than SSRS 2014.
Recently, an idea was internally floated to do a scale out deployment of SSRS in order to bring these stats down. I don't see how a scale out deployment will work, as from what I have gathered over the years, scale out is more on a web/http level rather than the database level. In other words, scale out deployment would be multiple SSRS web servers talking to a single report database server.
I wanted to reach out the community here to get some thoughts around what a better design would be and to improve my understanding of scale out deployment, and get some ideas.
Thanks
kaz
-
so the scale out deployment will distribute the reports executions out to the SSRS farm, which is great if you have lots of calls to the reports. I would expect that each node in the farm shows identical statistics for the same report on a per server basis.
the question is the source of the data for the reports,, and the differences in the environment.
are you comparing apples to apples, basically...same server stats and reosurces, same routes to the data? same disk speeds?(ssd vs SAN?)
Is the current 2014 SSRS server also on the data source? (that would eliminate network IO for example) where is the new 2017? on the same network backbone? in the cloud? is it a VM in a heavily ESXI host?
are you comparing wait statistics on the 2014 vs 2017 calls individually? i would be looking for waits related to networking, and async io, stuff like that.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell,
Thanks a lot for your reply. And, excuse me, I forgot to mention the differences, and infrastructure side of things.
My infra team tells me that SSRS 2017 environment is using the same segment as production on our virtual environment, same network backbone, SSD, more grunt (CPU & memory) basically its ready to be promoted as production when we need.
From a data sources point of view, most of the data sources are same, with the only difference being that production (SSRS 2014) is connected to Power BI via the data gateway, reports that are embedded in custom .NET applications and a a few TFS on premises reports and related SSAS cubes (that are never used but came out of the box). This makes SSRS 2014 having more load than SSRS 2017.
Other than the above differences, data sources are same, I have tried to draw a topology below - gray boxes exist only in SSRS 2014.
So far, I haven't really dug deep into core SQL calls, just been looking at SSRS metrics alone, and there, I have gone to individual reports level and have identified few reports that are different. -
And, yes, there are lot of report calls, on demand / manual reports, subscriptions (periodic), PBI calls etc. However; SSRS 2014 seems to be working fine, relatively, with the limitations of standard editions on memory. SSRS 2017 starts behaving differently for the same data sources with all the enhancements it has gotten.
I should also mention that this is the second time we are trying to upgrade from 2014 to a higher version - we tried last time to upgrade to SSRS 2016 enterprise edition, and had to roll back within few days. Turned out that PBI overnight refreshes took forever to complete, and all subsequent requests kept getting queued including from business users. This makes it very sensitive for us :).
Thanks
Kaz
-
Kaz hi!
Did you compare config files of SSRS2014 and SSRS2017? Memory settings and the rest?
-
Hi Andrey,
Thanks for the reply, and apologies for not replying earlier, end of financial year activities have kept me busy.
I have played with config files and memory settings but to no avail. It's almost a puzzle! What I don't understand is, that I am not using any of the new features of SSRS 2016/17. I am just trying to get my regular SSRS reports (paginated reports in 2016/17) to work. Same data sources, same report definitions as in SSRS 2014.
So far, I haven't really dug deep into core SQL calls, just been looking at SSRS metrics alone, and there, I have gone to individual reports level and have identified few reports that are different.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply