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.