asked
Deadlocks When Expiring SSRS Dataset Cache
We are currently on SQL Server 2014, V12.0.5203.0. We use SSRS as our core reporting tool. Most of our report datasets are set up as shared datasets which are cached. The cached datasets are linked to shared schedules. We expire caches linked to shared schedules programatically as a final step in our ETL, which runs several times per day. The code to expire the caches looks like this (passing in the appropriate schedule id): EXEC dbo.AddEvent @EventType = 'SharedSchedule' , @EventData = @ScheduleID; This sort of setup is described well [here][1] (we're not pre-loading the cache using the NULL delivery provider, but everything else is similar). After the event is added using the code above, we're noticing many deadlocks in the report server tempDB. Here is a sample deadlock graph: ![alt text][2] From what I can gather, the "shared schedule event" triggers several calls to the FlushReportFromCache sproc (possibly 1 call for each shared dataset??). This sproc has a delete and an update statement in it. The deadlock info consistently shows the delete statement in this sproc as the deadlock victim, and the update statement as the winner of the deadlock situation: ![alt text][3] When all is said and done, we end up with stale data in the cache due to the failed deletes, but the shared schedule shows that it was successfully expired. How can we avoid these deadlocks while still programatically expiring the cache at the end of an ETL run? [1]:
http://www.sqlblog.nl/dynamic-refresh-ssrs-cache-using-sql [2]: /storage/temp/4017-deadlock.jpg [3]: /storage/temp/4018-deadlock2.jpg
I'm having the same problem. Did you manage to get this resolved?
We opened a support case with Microsoft, and they told us to call the FlushReportFromCache directly for each dataset, rather than using AddEvent. We have been testing this solution out and it works much better, but we still occasionally get deadlocks between the FlushReportFromCache sproc and a sproc called "CleanOrphanedSnapshots". CleanOrphanedSnapshots is an internal process that runs on its own schedule... I've passed this new deadlock info on to MS support and am still waiting for a solution from them. I'll let you know what they come back with!
Thank you. In 2016, it looks like MS uses the same add event logic when you create a managed cache refresh plan via the gui in SSRS.
18 People are following this question.
