For whatever reason, this query keeps deadlocking in SSRS, but runs in less than one second in SQL Management Studio.
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset DataSet1. (rsErrorReadingNextDataRow)
Transaction (Process ID 111) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Here is the query:
;WITH all_dates AS
(SELECT DATEADD(dd, Numbers.n - 1, @StartDate) AS Dates
WHERE Numbers.n <= DATEDIFF(dd, @StartDate, @EndDate) + 1)
DISTINCT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Date
,COUNT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Count
WHERE Response_Master_Incident.Time_First_Unit_Assigned IS NOT NULL
AND CAST(Response_Master_Incident.Response_Date AS DATE) >= @StartDate
AND CAST(Response_Master_Incident.Response_Date AS DATE) <= @EndDate
AND EXISTS (
SELECT TOP 1 * FROM Activity_Log
ON Unit_Names.Alias = Activity_Log.Radio_Name
ON Vehicle.UnitName_ID = Unit_Names.ID
ON Vehicle.HomeDivisionID = Division.ID
ON Division.JurisdictionID = Jurisdiction.ID
WHERE Jurisdiction.Name = @JurisdictionName
AND Activity_Log.Master_Incident_ID = Response_Master_Incident.ID
AND Activity_Log.Activity = 'On Scene'
GROUP BY CAST(Response_Master_Incident.Response_Date AS DATE)
ISNULL(Response_Count, 0) AS Response_Count
FROM all_dates a
LEFT JOIN response_dates r
ON a.Dates = r.Response_Date
The Deadlocking issue can be tricky. Single sessions (i.e single queries) don't deadlock on their own. There must be at least 2 sessions involved in a deadlock. i.e. you need to find out what this session is deadlocking with.
Jeff showed me a link how you can set up something to record deadlock information:
Ok, I walked through the steps, and I have no idea what to do with the data I'm given.
I see the deadlocked transaction, the query text (I'm missing the parameter values, but whatever), a bunch of other data that I have no idea what to do with.... but I'm still no closer to resolving why this is happening.
The Response_Master_Incident table has the ID column indexed and set as PK.
As far as the Activity_Log table goes, I'm going to show my ignorance here: I'm unsure how non-clustered indexes work, but I see a few indices that have Master_Incident_ID included. So I guess it's indexed, but I'm not sure if it's been done properly.
The deadlock graph should show you which processes are involved in the deadlock. You need to identify which process has the resources that this report is trying to access - and then determine what needs to be done to eliminate the issue.
If you can share that information - it could help to identify what resource is causing the problem.
In the meantime - try cycling the SSRS service. It could be getting a deadlock in SSRS and not within your actual query.