0

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?

  • You would need to investigate what was holding the share lock and why. – Jon Seigel Mar 18 '14 at 13:46
0

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.

  • I updated my question with more information around how the report 'self healed' – best Mar 20 '14 at 7:57
  • Thanks for the update. Yes, it was being blocked. If you decide to use some other isolation level, it will reduce the likelyhood of repeat. As I mentioned, I prefer the SNAPSHOT isolation levels for such things, since it does row versioning. See: technet.microsoft.com/en-us/library/ms173763.aspx – RLF Mar 20 '14 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.