SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Installing ReportServer on separate server.


Installing ReportServer on separate server.

Author
Message
Sri - SQL
Sri - SQL
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 91
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?
davoscollective
davoscollective
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 1008
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64773 Visits: 18570
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

Sri - SQL
Sri - SQL
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 91
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.


SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64773 Visits: 18570
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

davoscollective
davoscollective
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 1008
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.
Sri - SQL
Sri - SQL
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 91
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?
davoscollective
davoscollective
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 1008
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).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search