Deadlock with simple queries in sql server 2008

I have an application that must delete and insert registries from a table, the table is storing the historic registries for an access control and is stored with the datetime for the check in the system.

Now this is the issue. I have a threadpool in which each thread works over one day in a time period (normally a month). When the process start, it's giving me some deadlocks. I made a lock trace and in the deadlock graph only says that a process was blocked but no which sentence or transaction was made, and in the deadlock log it say Parallel query worker thread was involved in a deadlock. How can I make a more detailed trace or query in order to know what is making my app and DB blocking?

Thanks

EDIT

here is the XML for the trace I already made in the past days:

```





DELETE FROM dbo.sysssislog
FROM dbo.sysssislog AS s
INNER JOIN dbo.syscollector_execution_log_internal AS l ON (l.package_execution_id = s.executionid)
INNER JOIN #purged_log_ids AS i ON i.log_id = l.log_id

-- Then delete the actual logs

EXEC [dbo].[sp_syscollector_purge_collection_logs]







DELETE FROM dbo.sysssislog
FROM dbo.sysssislog AS s
INNER JOIN dbo.syscollector_execution_log_internal AS l ON (l.package_execution_id = s.executionid)
INNER JOIN #purged_log_ids AS i ON i.log_id = l.log_id

-- Then delete the actual logs

EXEC [dbo].[sp_syscollector_purge_collection_logs]







DELETE FROM dbo.sysssislog
FROM dbo.sysssislog AS s
INNER JOIN dbo.syscollector_execution_log_internal AS l ON (l.package_execution_id = s.executionid)
INNER JOIN #purged_log_ids AS i ON i.log_id = l.log_id

-- Then delete the actual logs

EXEC [dbo].[sp_syscollector_purge_collection_logs]







DELETE FROM dbo.sysssislog
FROM dbo.sysssislog AS s
INNER JOIN dbo.syscollect

Based on what I see in SQL Sentry Plan Explorer PRO*, looks like two different jobs are fighting over who is going to get to delete some rows (click to enlarge):



Maybe there is some overlap because Microsoft does some not-so-optimal things in msdb.dbo.sp_syscollector_purge_collection_logs, for instance letting each invocation of the procedure grab TOP (@delete_batch_size) rows with no ORDER BY or exclusion techniques, meaning two people calling the stored procedure could certainly end up trying to delete rows on the same page, or maybe even the same rows.

So the easiest solution:

Don't have two jobs running the same cleanup procedure.

If you need to run both jobs (again, please explain why?), my first thought would be to set MAXDOP to 1 for the delete queries in that stored procedure (yes, you can modify it, just keep that in source control because your change could be undone by service packs, upgrades, etc).

DELETE ... OPTION (MAXDOP 1);

I don't know for sure that this is the cause; would be interesting to see an actual plan from calling the procedure manually outside the context of the job.

If you don't want to modify the stored procedure, you have at least one other option if you are running Enterprise Edition: Resource Governor. Just have the login the job executes as belong to a workload group with max_dop set to 1. I have instructions for doing so (framed in the context of maintenance jobs, but same concept) in my white paper, Using the Resource Governor.

* Disclaimer: I work for SQL Sentry.

StackExchange Database Administrators Q#69652, answer score: 5