Our database becomes completely locked, and it seems that many tables not related to each other are locked at once. When this happens, it becomes locked for approximately 2 minutes, and when this happens it is not related to the amount of traffic that our application has at that time.
We have tried to replicate the issue on our test servers by
- increasing the activity on the database (Inserts, Selects, Updates, Deleting),
- increasing the connections to the database simultaneously,
- increasing latch time,
- generating temporary locks on a particular table
But could not arrive to a conclusion were this is coming from.
Is there any type of configuration, or background running tasks of SQL Server, that may cause this (Backups, Transaction Log related stuff etc)?
Is it possible to trace locks, by a date range (For example : Taking a snapshot of the current locks present in the database for a particular time)?