Slow performance of Reporting Services, very fast in QueryAnalyser

Question

We're using SQL Server 2005 with Reporting Services.

We have a number of reports, each containing a relatively simple SQL query - by "relatively" I mean that we do have a few joins, but nothing worse than that. We do not call any stored procedures in our queries - this is not a case of parameter sniffing.

When executing one of these reports (let's call it report A) through Reporting Services, it takes an extremely long time to complete - on the order of tens of minutes or even hours. When executing the corresponding SQL query in Query Analyzer, it completes in a few seconds.

The number of rows returned from the database can be as few as 1 - yet, the report never completes.

The other reports are working fine.

Looking in the ExecutionLog table on the Reporting Services, I can see that most of the time is in TimeDataRetrieval (and we're talking millions of seconds here...) - those times the report actually completes. If the report is manually aborted, TimeDataRetrieveal is zero and TimeProcessing is absurdly high instead.

I've looked into the logs of Reporting Services, but everything looks normal.

Now, before you start suggesting "lock" - well, our queries do have the nolock hint turned on.

As it stands, I've reached the limit of my imagination trying to find the error. Any thoughts, insights would be gladly appreciated.

/Christoffer

Answer 1

While running the report, try co catch execution plan using the SQL Profiler. Have a look if you don't have any CONVER_IMPLICIT operators for example and table/index scans in general.

http://msdn.microsoft.com/en-us/library/ms190233.aspx

The conversions prevent indexes from being used and may happen if you pass parameters which have different type than the columns you compare them against.

You might try to add OPTION(RECOMPILE) to the query of the report, it is possible that your report is a victim of parameter sniffing.

Check if your queries use scalar user defined functions. They may be real killers of performance. If you have them, it may be possible to convert them to table valued functions.

Answer 2

I ended up stripping the query, basically one statement at a time, until I found the culprit. One of the joins in the query joined in an ever growing table (millions of rows), using a "with (nolock index(x))" hint.

Removing the index hint in Query Analyzer got me the same result as in Reporting Services - a very slow query. This is not surprising in itself - but indeed it seemed as if the query when run through RS did not use the hint.

I then tried running the report in RS again, using the SET FORCEPLAN ON statement. And... it worked - execution time is now a few seconds, as it should be. As I understand it, the FORCEPLAN option forces the SQL Server to process the joins in the order indicated, AND to take any hints into consideration.

Does anybody have any insights as to why the query through RS would ignore the hint, when the Query Analyzer obviously takes it into account?

Answer 3

A quick update: It would seem that any query that takes more than 30 seconds to execute, will automatically cause a time-out in Reporting Services. While SET FORCEPLAN ON solved the issues for some of the reports, there were still problem reports that would time-out. These queries still worked fine within Query Analyzer, although the execution time was larger than 30 seconds. After eliminating all other possible problems with time-out settings - at the SQL server, in Reporting Services, rsserver.config, in the IIS - I found that there still was a time-out that I could not modify.

My suspicion is that it has to do with the ADO.NET default time-out, and that Microsoft has left us with no way of modifying this value.

Finally, I ended up having a deeper look at the query, rearranging it to the best of my ability, and managed to strip some seconds from the execution time. Now, finally, the reports are running as they should.

This unmodifiable time-out worries me, however - what will happen if the SQL server has a higher load than normal when the query is executed?

Answer 4

I have faced the same situation.

In Management Studio the results came after twenty seconds but when I run the report in visual studio it locked the system.

In SQL profiler I've traced the query and realised that it transforms my query as :

    exec sp_executesql N'
                ....................
                ....................' 
, @dateparameter1 = '2010-06-01 00:00:00'
, @datepamareter2 = '2010-06-02 00:00:00'
, @stringparameter=null

I've examined the execution plan and realized that I was a victim of parameter sniffing.

I've reorganized my query as it was told here, and it works fine now..