CODEWITHSUNDEEP
sql-serverperformancereporting-servicessql-server-profiler
Stuart Ng
Stuart Ng

Reputation: 11

How to debug SSRS multiple calls to SQL Server. The RDL only defines once of a stored procedure

I have a RDL report that returns a raw Excel file with about 60 rows that took 35+ seconds to complete.

The stored procedure itself (say runPNL) only takes 11+ seconds to complete.

Here is the application's architecture:

Appl <----> SSRS <----> SQL Server

Here are the scenarios I debugged:

Situation A:

When the report was generated from "Appl", it took 35+ seconds.

Situation B:

When the report was generated from "SSRS" endpoint, it took 22+ seconds.
(https:///ReportServer/Pages/ReportViewers.aspx........)

Tracing on SQL Server using SQL Profiler:

  1. When the report was pulled from Situation A, the SQL profiler showed that runPNL was invoked 3 times
  2. When the report was pulled from Situation B, the SQL profiler showed that runPNL was invoked 2 times

Tracing on SSRS (using: SELECT TOP 100 * FROM ExecutionLog3 ORDER BY TimeStart DESC):

  1. When the report was pulled from Situation A, the SQL was showing "1" activity instead of 3 times
  2. When the report was pulled from Situation B, the SQL was showing "1" activity instead of 2 times.

What can cause the additional invocations in the SQL Server?

There was no global variable such as TotalPages in the RDL file. If there is, I understand that the stored procedure might be run twice to produce the TotalPages first and data field next.

The query is only referenced once in the RDL file.

Those additional activities were not logged into the ExecutionLog3 table....Why? A bug in SSRS?

So far I did not try anything yet such as configuring the SSRS timeout.

Upvotes: 1

Views: 32

Answers (0)

Related Questions