Deadlock scenario, where is this S lock coming from? I am encountering the below deadlock scenario. Please see the attached object definitions and execution plans. The plans were pulled out of cache from a system where this deadlock does occur.

I understand what all of this data is telling me but what I am confused about is why the spUpdateUserAccount stored procedure is taking an S lock on IX_Person_TaxPreparer_rowCreated. This lock appears unnecessary and there is no reference to IX_Person_TaxPreparer_rowCreated in the execution plan for spUpdateUserAccount.

...

Full question, with attachments, posted on SQLPerformance
I'll repost my response on twitter here:

Based on the deadlock XML you posted, which lists a trancount of 3 in the session that invokes spUpdateUserAccount, a prior statement or batch in that transaction likely put the lock on IX_Person_TaxPreparer_rowCreated.

I'm presuming this lock is held because of the foreign key constraint in place.

The locks are held for the duration of the transaction, so your second statement can't complete til the first commits the open transaction.