Reporting Services TempDB lock blocking report generation

Question

Two days ago one of our reports in Reporting Services started to take much longer to generate. Its generation time had increased from 4 seconds to 2 minutes.

Investigation found:

• The Stored Procedure ran in 1 second and the query plan was acceptable

• The report ran as expected in 4 seconds on a dev machine in Visual Studio

As soon as the report was published to the production server it ran much, much slower.

Using Activity monitor showed that the report was being blocked with a lock "LCK_M_S"

I turned on trace 1222 to check for deadlocks but no deadlocks were reported.

The only information I could get on what was going on was by the report "Top Transactions by Locks Count" for the ReportServer database. It listed locking transactions on the ReportServerTempDB.

This pattern was repeatable with the affected report for two days, you would run it, see the blocking process in the Activity monitor and the report taking the much longer 2 minutes.

Just as we were looking further into the problem, it suddenly disappeared, nobody in the team had changed anything, code or data, and now the report is back to running in 3-4 seconds.

What happened?

Without dev intervention the report which was repeatedly taking 2 minutes to run and had been for 2-3 days suddenly returns to running in 4 seconds and the blocking process seen in Activity monitor is gone?

Answer 1

A LCK_M_S is a blocking problem not a deadlocking problem. So you are just waiting on something else. You need to determine what process is blocking you.

So, "it suddenly disappeared" because that block either completed or failed and rolled back. This is normal. What sounds abnormal from your notes or your perspective is the length of the block.

In addition to finding the blocking process you could also consider using a different transaction isolation level. For example, although not without their own problems, you might consider using "READ COMMITTED SNAPSHOT" or "SNAPSHOT" isolation levels for your report. This would allow it to read the data as of the start of the transaction and would avoid most blocking situations.