1

We have a report stored procedure that runs in about 1 minute in production; however, when executed from the Sharepoint it populates, it rus for over 20 minutes, with exactly the same parameters. We've done everything to avoid parameter sniffing problems: used WITH RECOMPILE on the procedure declaration; added OPTION OPTIMIZE FOR UNKNOWN on the parameters within queries; even created local variables and assigned the parameter values to them and used them throughout the procedure. None of these has had any effect. I know that the 1-base minute run time is a big red flag, and we're working on pre-aggregating this data into our BI platform, but that's months down the liine and this is needed for month-end close every month.

Other relevant information: Sharepoint 2013 on SQL Server 2012 Data Source is a SQL Server 2008 R2 database Report definition created in Visual Studio 2008

1

Your Sharepoint db instance will have a ReportingService_weirdsequenceofcharacters database. It stores report execution and performance metrics. There is a view called ExecutionLog2 in that database which has 3 Times. Use them to find whether the bottleneck is sql, report processing, or rendering. You should also be able to compare these values to the values from your production report server (if you have one). A non-sharepoint report server database will typically be named ReportServer.

TimeDataRetrieval

The number of milliseconds spent interacting with data sources and data extensions for all data sets in the main report and all of its subreports. This value includes:

  • Time spent opening connections to the data source
  • Time spent reading data rows from the data extension

Note: If a report has multiple data sources/data sets that can be executed in parallel, TimeDataRetrieval contains the duration of the longest DataSet, not the sum of all DataSets durations. If DataSets are executed sequentially, TimeDataRetrieval contains the sum of all DataSet durations.

TimeProcessing

The number of milliseconds spent in the processing engine for the request. This value includes:

  • Report processing bootstrap time
  • Tablix processing time (e.g. grouping, sorting, filtering, aggregations, subreport processing), but excludes on-demand expression evaluations (e.g. TextBox.Value, Style.*)
  • ProcessingScalabilityTime (see AdditionalInfo column)

TimeRendering

The number of milliseconds spent after the Rendering Object Model is exposed to the rendering extension. This value includes:

  • Time spent in renderer
  • Time spent in pagination modules
  • Time spent in on-demand expression evaluations (e.g. TextBox.Value, Style.*). This is different from prior releases, where TimeProcessing included all expression evaluation.
  • PaginationScalabilityTime (see AdditionalInfo column)
  • RenderingScalabilityTime (see AdditionalInfo column)

http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.