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

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?