March 4, 2013 at 7:42 pm
Hello All,
We currently have report server and database engine installed on same machine. The problem we are facing with is when we run reports they are taking lot of time than expected. We checked executionlog and we don't see any issue for data retrieval and rendering time. We are planning to move reportserver to a new server.
So I wanted to know cons and pros of having reportserver on separate server?
Or shall we extend memory on existing server and dedicate a part of RAM to reportserver?
what are minimum suggested RAM and CPU for Report Server?
March 4, 2013 at 11:37 pm
The answers to those questions definitely depend on your environment.
What version of SQL Server is it?
Is this machine a production application database? Or a datawarehouse?
If you have investigated the logs and the execution times are all fast then moving the Reporting Services to a different server is not going to help you. You really need to look at why the reports are taking a long time.
Take one really long report as an example, and look at its records in the execution log tables and tell me what you see there. Are the reports running on subscription? Could caching their execution help? Are they many many pages long? Are they slow to render on screen (could be an interactive size setting issue) or slow to export to another format?
I recommend finding the cause of the issue rather than changing your infrastructure in the hope that the problem will go away.
March 4, 2013 at 11:43 pm
You mentioned data retrieval and render time, how about processing time?
I am curious to know if any optimizations have been made towards SSRS settings.
How does the data retrieval compare when run as a report as opposed to as a query from within sql server (run the proc or script that generates your report sets)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 5, 2013 at 2:53 am
davoscollective (3/4/2013)
The answers to those questions definitely depend on your environment.What version of SQL Server is it?
2008 r2 enterprise
Is this machine a production application database? Or a datawarehouse?
DW
Take one really long report as an example, and look at its records in the execution log tables and tell me what you see there.
Please see stats for 5 reports ordered by 1st 3 columns.
TimeDataRetrieval| TimeProcessing|TimeRendering|Source|Status|ByteCount|RowCount
849933| 53 | 129 | Live| rsSuccess|79685|281
704452| 207| 469 | Live| rsSuccess|189061|921
553014| 126| 0 | Live| rsProcessingAborted|0|0
476747| 73 | 500 | Live| rsSuccess|383306|65
434059| 138| 348 | Live| rsSuccess|226447|61
Are the reports running on subscription? No
Could caching their execution help?
Caching might help. But the issue I'm facing with is I have reports with lof of parameters and as far as I know I can only cache it for given set of parameter values. If any user runs for different set even with only changing one parameter value i will run into the issue im facing right now.
Are they many many pages long?No
Are they slow to render on screen (could be an interactive size setting issue) or slow to export to another format? I guess not most of the rendering time i noticed are 0. What are the ideal rendering, processing, dataretrieval times?
And also I have around 1000 users of which 150 of them are actively using.
Thanks for the help.
March 5, 2013 at 12:19 pm
The slowest part of your reports is by far the data retrieval. This means that your tsql to get the data is the biggest impact.
That can boil down to either bad logic, complex business rules, poorly running tsql, or any combination of these items.
To improve report performance, you need to tune your queries.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 5, 2013 at 4:30 pm
I have to echo SQLRNNR here. The queries are taking a long time for not many rows, but of course row count is not necessarily an indicator of complexity.
Optimising the queries could be solved by faster T-SQL, for example if there are slow parts that are not optimised for set-based logic. It might be that when the report queries were first created that your DW was much smaller and they ran fine, but they haven't scaled well as your DW has grown. It might be that you need good indexes or better indexes if you have them already. It might be that the queries can't make use of the indexes because of the way they are written.
If all the report queries are written in a similar style then you might benefit from taking the slowest one and getting some advice on it and then optimising the rest of them in order of slowness. If it's a huge amount of work then you might even benefit from getting in a specialist to help.
The conclusion is that you won't benefit from moving SSRS to another server because the queries are still going to run on the DW database engine.
March 5, 2013 at 7:57 pm
OK thanks both for the help.
how do I identify that that index I added was working(initially) when there was less data and it is not after DW has grown over the time?
can you point me some place where I can learn like books or online content and fix my issues?
March 5, 2013 at 8:07 pm
This is a very deep topic so I can't really do it justice here, but as a starting point, grab the query from the report, run it on SSMS with the "show actual plan" option and you will see in the execution plan if it is doing index seeks in there.
At least it will tell you which part of the query is most expensive, but reading query plans in depth is not straight forward. Grant Fritchey (aka scaryDBA - works for Redgate who run SQL Server Central) has an excellent book on the topic.
You can also tune the query in SSMS but trying changes and verifying success (they run quicker and the execution plan confirms more efficiently).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply