CODEWITHSUNDEEP
reporting-services
Serega
Serega

Reputation: 39

Takes several minutes to run SSRS report

I have pretty simple SSRS report, it takes less then 1 second to run stored procedure. The report works just fine when I upload it on local server. However on remote server (SQL Server 2012) it takes 5-7 minutes to run report. Other my reports on remote server work good. Anyone can help me? Thanks in advance!

Upvotes: 1

Views: 2447

Answers (1)

Roman Badiornyi
Roman Badiornyi

Reputation: 1539

You should use SQL Profiler or ReportServer DB to check which process is slow. you can use this SQL for checking:

USE ReportServer
SELECT ReportPath,
       RequestType,
       Format,
       ReportAction,
       TimeDataRetrieval,
       TimeProcessing,
       TimeRendering,
       DATEDIFF(S, TimeStart, TimeEnd) AS TotalSeconds
FROM ExecutionLog2

Where TimeDataRetrieval is a time of stored procedure execution + sending time to ReportServer, TimeProcessing - it's time spent for grouping, sorting etc. in report side and TimeRendering is time spent for rendering.

Then when you know why execution is slow - you can thinking about ways of fixing (fix stored procedure, add indexes, change report structure etc.).

I had such problem which cause parameters sniffing (about parameters sniffing), to prevent this you shouldn't use your input parameters in your query like follows:

CREATE PROCEDURE [dbo].[usp_MySP] @InputValue INT
AS
BEGIN
    DECLARE @SomeValue INT;
    SET @SomeValue = @InputValue;

    SELECT SomeColumn 
    FROM SomeTable
    WHERE SomeColumn = @SomeValue
END

Upvotes: 3

Related Questions