question

smithochris avatar image
smithochris 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
ssrssql server 2014deadlock
deadlock.jpg (54.6 KiB)
deadlock2.jpg (49.2 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TierOneSupport avatar image
TierOneSupport answered
I'm having the same problem. Did you manage to get this resolved?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

smithochris avatar image
smithochris answered
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!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TierOneSupport avatar image
TierOneSupport answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.