Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

This is a followup question from: https://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically

I'm still having deadlock/timeout situations in the ASP.NET application when running large reports simultaneously although READ_COMMITTED_SNAPSHOT ON.

So I have two questions:

• How can i check if the Transaction Isolation Level Snapshot is working as expected/at all?

• I'm assuming that the foreign keys (in the tables of the Web-Application to the report-tables) are responsible for deadlocks. I found this interesting article:

Note SQL Server acquires shared locks when validating foreign keys,
even if the transaction is using read committed snapshot (read
committed using row versioning) or snapshot isolation level. Be
mindful of this when examining deadlock graphs from transactions when
these transaction isolation levels are used. If you see shared locks,
check to see whether the locks are taken on an object that is
referenced by a foreign key.

How can I check if the FK are really responsible for the Deadlock/Timeout situations, does that mean i could delete those foreign keys to prevent deadlocks(what would be an acceptable effort)?

Note: I'm only reading from the tables which cause deadlocks.

Any thoughts on this topic are greatly appreciated.

Edit Here is a Deadlock-Graph. Maybe somebody could help me to understand what causes the deadlock. It seems that it occured without any reports running only caused by the web-application, when two transactions want to write the same table(one update and one insert, the insert is as Stored-Procedure). Why does it aquire page locks and how to enable row locks only? The Insert-SP uses already TRANSACTION ISOLATION LEVEL REPEATABLE READ.

I have a strong suspicion that two triggers(one update and one insert) are responsible for the deadlocks. Here is the insert-trigger:

```
CREATE TRIGGER [dbo].[CreateRMAFiDates]
ON [dbo].[RMA]
AFTER INSERT

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation levels will help nothing, either the cause of the deadlock is different.

The only way to make progress is to capture the deadlock graph.

As for the other question, how can you check if you operate under snapshot isolation: look in sys.dm_tran_active_snapshot_database_transactions.

StackExchange Database Administrators Q#6905, answer score: 16