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.
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.
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?