Unable to track down Report Processing Performance Issue...

Question

We're having a very frustrating problem in our production environment.

We have a report that will sometimes return very quickly, and other times not return at all. When the problem is happening, the report will process for 15 minutes or so then the browser will display a "Cannot display webpage" error. This problem is sporadic usually lasting for a few days, then we get a few days of very fast processing, then back to slow. When the report is running fine we can return over 14k records in about 10 seconds.

I'm told by our data team that nothing has occurred on the SQL Servers at the time we see the switch from slow to fast. No index rebuilds, statistics recalcs, etc.

The underlying report query (stored procedure) always runs fast. Even when we are experiencing the problem, I can connect to the production database, with the same user as the report uses, and run the stored procedure with the same parameters and it always returns quickly. We have checked for blocking, and none is happening.

The report does have quite a few parameters. I've seen posts regarding "parameter sniffing" so I created a parameter free version of the report and I still get the same results.

There is nothing complicated about this report. It's a table. No grouping or filtering is done at the report level. There are no subreports. The report does use interactive sorting.

The report can return upwards of 14k records. The total amount of data for this is around 2MB. But like I said earlier, some days the report runs fine and will return even the max amount of records in just a seconds.

The report uses a shared data source that is also used by many other reports that do not experience this issue.

In the report database, we're seeing records being added to the RunningJobs table for these report requests, but we don't see any processing occur after that. It's like the report server is forgetting about them.

Our current line or thought is that the Report Server is not operating properly since these entries in RunningJobs just sit there and are not processed.

Does anyone know why a job might sit in the RunningJobs table? We should be seeing something in the Report Server log files if these jobs are being run, correct? Is there anything else we should be testing?

Our Report Server is at version 9.00.3050.00. We are accessing through the Report Viewer web control.

Answer 1

Maybe try a smaller data set just to see if that changes it.

Any CPU spikage on either server?

Are the run times for the report matching up with what you are experiencing? ExecutionLog table in ReportServer should show this.

Sounds very annoying - I empathize, wish I had better ideas.

Answer 2

I've not used ReportServer (so far), but I've seen similar problems with SQL server, caused just by faulty network switch (or other network hardware). Some packets lost and application is waiting for server indefinitely - and absolutely no activity on server.

Answer 3

have you tried to profile the request using sql profiler? Using that you can determine if the request has completed in sql and the problem is in generating the report or if the query is in fact not completing.

If the query isn't completing when it is slow you could capture the quey plan and compare it to the plan generated when you are not having a problem.

Answer 4

I was able to solve a similar problem that was occuring after we had switched database servers and had updated our connection strings in web.config. My query was running at about 5-6 seconds, but the report was taking so long that it was timing out my Web page (I'm using local reports; I don't have a reporting server.)

I had a suspiscion that my DataTables in my DAL were still looking around for the old server before proceeding onto the new one. I'm probably way off on that idea, but the solution for it still worked!

I refreshed all of the DataTables in my .xsd file by choosing "Configure" in the right-click menu and, keeping all of the default values, clicked Next until I had to click Finish. After going through every DataTable, I rebuilt the Website and now it runs as fast as my query.

I hope this helps!

Answer 5

Verify that you don't have any images on the report with external links. If you do, it's possible that your images could be taking a long time to be server by the image host. I had a very similar situation where the reporting server lost outbound internet access and so the report server was not able retrieve the external image during processing. Unfortunately, SRRS did not just check, get an http error, and return, it waited, and waited. I supsect (but have not verified) that it tried to retrieve the image for each page of the report. I say this because of a correlation between report length and discrepency between old execution times and execution times when the problem existed. It seemed like a 5 to 12 second delay on each page of the report.

If you want more details about how I fixed my issue see my post:

http://stackoverflow.com/questions/2451959/sql-server-reporting-services-fast-timedataretrieval-long-timeprocessing

Kind Regards,

Bernie