After a recent SSRS upgrade from SQL 2008 to SQL 2008R2 Gold ( no patches) , we began to get complaints from users that reports were running slow and Internet Explorer was hanging up when certain reports were executed.

We ran some of our standard SSRS reports, and there were no unusual report failures. We looked at the SSRS logs and there was nothing which could explain the problem either.

We could reproduce the problem by running one of our SSRS reports – it seemed to run forever, when normally it would run in less than 10 seconds.

It seemed that every time we touched the report – things would hang up.  While the report was running, sometimes your laptop would go unresponsive as well. If you managed to click on any other tab in IE, the title bar for IE would include (Internet Explorer Unresponsive), and the entire  IE window would grey out.

Running the same report, with the same data, in Report Builder 3 was fine.

We ran Fiddler to see what we might learn by following the network traffic.  The times between send and receives were pretty fast. Summing up the times accounted for only a small portion of the 1:40 that it took to run the report.  However we did notice a pattern – there was a large wait on the client side. Fiddler looked like:

                Send -> receive

                Send -> receive

                Send -> receive

                <Long Wait>

                Send -> receive

 The sends come from the IE client, and the receive is the response from SSRS Reportserver.The client would send a request, get a quick response, then sometimes just wait.  Since the unexplained time was after a receive, and before a send, we know that the client is the problem. If the client would simply do the next send – all would be good.

 Running Task Manager on the client during the report run , we would see one or two processors using nearly 100% during the wait, then returning to normal. Below is task manager during the unresponsive period.

 

After a while we realized that the problem occurs when the parameters refresh. It took 1:40 to refresh the parameter list. There were several parameters, and simply choosing a different item from the parameter list would send us into this 1:40 wait.

 I pulled out the query which generates the parameter lists, and ran it in Management Studio – and it is fast, fast, fast.

 Running the report in the development environment also did not exhibit the same behavior, even though it was on the same version of SSRS as the problem prod environment.  There were lots of reports in prod that did not have this problem. We typically run 25,000 to 35,000 reports per day in production. Most were fine, but several reports, only in production, exhibited this behavior.

 Continuing to work, we discovered that the number of values in the parameter lists in the trouble prod reports were between 8K and 11k. The same report which did not exhibit this problem in dev, had  a much shorter list of values in its parameter lists. Changing the data source on the report in dev, to point to the production database – the dev report took forever to refresh, just like prod.  Now we know the problem is related to having a large number of values in the parameter list.  Checking other reports with the same problem, they all had a large parameter lists.

Microsoft has a KB article about this problem (http://support.microsoft.com/kb/2522708) and http://support.microsoft.com/kb/2506799/LN.  it is fixed in CU7 for SQL Server 2008 R2. This CU is not included in SP1, which is in beta right now. However, I assume it will be included in SP2 and later, when they come out.

 The KB article describes the problem to apply to “The report contains a large multi-select drop-down parameter list.”   Our reports with single select drop-downs also showed this problem, so it does not apply ONLY to multi-select.

Another thing to note is that the initial load of the parameters when the report first comes up in IE is fine. However when you change one of the parameter values, and the parameters refresh – you wait, wait, wait.

How do I know if I have this problem?

·         Report was created in an earlier version of SSRS than SQL 2008 R2

·         Initial report load, and execution time same as pre-upgrade.

·         Parameter refresh takes a long time, during which CPU utilization goes near 100%

·         You are running SQL Server 2008 R2 pre-CU7

We installed CU 7 and the 1:40 refresh time dropped to about 10 seconds, and processor utilization dropped – problem fixed. CU7 can be found at http://support.microsoft.com/kb/2489376.

 

Warning: I believe that installing SQL 2008 R2 CU7 breaks intellisense in Visual Studio 10. If so, there is a patch which fixes this also.