Deadlocked Query

SSAS and SSRS
1

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
      FROM Numbers
     WHERE Numbers.n <= DATEDIFF(dd, @StartDate, @EndDate) + 1)
,response_dates AS
   (SELECT
		DISTINCT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Date
		,COUNT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Count
	FROM 
		Response_Master_Incident
	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
				JOIN Unit_Names
					ON Unit_Names.Alias = Activity_Log.Radio_Name
				JOIN Vehicle
					ON Vehicle.UnitName_ID = Unit_Names.ID
				JOIN Division
					ON Vehicle.HomeDivisionID = Division.ID
				JOIN Jurisdiction
					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)
	)
 SELECT a.Dates, 
	ISNULL(Response_Count, 0) AS Response_Count
		FROM all_dates a 
			LEFT JOIN response_dates r 
				ON a.Dates = r.Response_Date
2

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:

3

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.

4
  1. what are the stats for response_dates AS sub query?
  2. change
SELECT TOP 1 * FROM Activity_Log
				JOIN Unit_Names
					ON Unit_Names.Alias = Activity_Log.Radio_Name

to

SELECT TOP 1 1 FROM Activity_Log
				JOIN Unit_Names
					ON Unit_Names.Alias = Activity_Log.Radio_Name

and see what happens?

5

Still deadlocked.

6

do these tables have indices

Activity_Log on Master_Incident_ID ?
Response_Master_Incident on ID

7

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.

8
  1. how about n in Numbers.n. is that indexed and how about all the other columns you are joining on for that matter are they all indexed?
  2. how many rows does this return
SELECT DATEADD(dd, Numbers.n - 1, @StartDate) AS Dates
      FROM Numbers
     WHERE Numbers.n <= DATEDIFF(dd, @StartDate, @EndDate) + 1)

:thinking:

9
  1. Yes, Numbers is indexed, there's only one column, and it's PK
  2. Using April's dates, 30 records
10

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.

1 Like
11

I meant to follow up with this. Per vendor, they have tasks running every 30 minutes at the bottom and middle of the hour. These tasks were running in tandem with my queries.

After adjusting them about 10 minutes apart from these tasks, the began to run without deadlocking.