Sql-server – What are options for super volatile tables

You need to give the query processor a more efficient access path to locate StudentTotalMarks records. As written, the query requires a full scan of the table with a residual predicate [StudentID] = [@StudentId] applied to each row:

The engine takes U (update) locks when reading as a basic defence against a common cause of conversion deadlocks. This behaviour means the second execution blocks when trying to obtain a U lock on the row already locked with an X (exclusive) lock by the first execution.

The following index provides a better access path, avoiding taking unnecessary U locks:

CREATE UNIQUE INDEX uq1 
ON dbo.StudentTotalMarks (StudentID) 
INCLUDE (StudentMarks);

The query plan now includes a seek operation on StudentID = [@StudentId], so U locks are only requested on target rows:

The index is not required to be UNIQUE to solve the issue at hand (though the INCLUDE is required to make it a covering index for this query).

Making StudentID the PRIMARY KEY of the StudentTotalMarks table would also solve the access path problem (and the apparently redundant Id column could be removed). You should always enforce alternate keys with a UNIQUE or PRIMARY KEY constraint (and avoid adding meaningless surrogate keys without good reason).

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.}