Database Blocking and SQL Timeouts

This topic is an entry point for diagnosing database blocking, lock waits, and SQL timeouts in Verify Privilege Vault. It gathers the symptoms administrators and database administrators report most often and points to the topic that covers each one.

Symptoms

• Queries time out after 30 to 60 seconds, and Verify Privilege Vault pages or launchers fail or hang.
• Prolonged lock waits appear in SQL Server, with a single wait type accounting for most of the blocking.
• Connection or login timeouts occur when connecting through an Always On availability group listener.
• Symptoms cluster at a fixed time of day, or coincide with a rise in request volume or with index maintenance.

Identify What Is Blocking

Start by finding which objects are accumulating lock waits. The query for this is in the Analyzing Blocking section of SQL Server Performance Improvement, which also covers snapshot isolation, the READ_UNCOMMITTED isolation mode, and the SLEEP_BPOOL_FLUSH wait type.

Common Causes

• Isolation level. Writers blocking readers is usually addressed by enabling snapshot isolation. See SQL Server Performance Improvement.
• Synchronous commit on an Always On availability group. If HADR_SYNC_COMMIT is the leading blocker wait type, see SQL Server Always On Availability Groups for the commit-mode recommendation, and the HADR_SYNC_COMMIT Considerations section of SQL Server Performance Improvement for its interaction with index maintenance.
• Index and transaction log maintenance. Index rebuilds, transaction log growth, and availability group log behavior are covered in Verify Privilege Vault Database Maintenance.
• Background operation load. Verify Privilege Vault runs scheduled background operations from every node that has the background worker role. See Verify Privilege Vault Clustering and Quartz Trigger Jobs.