Database Blocking and SQL Timeouts

This topic is an entry point for diagnosing database blocking, lock waits, and SQL timeouts in Secret Server. 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 Secret Server 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 Secret Server Database Maintenance.
• Background operation load. Secret Server runs scheduled background operations from every node that has the background worker role. See Secret Server Clustering and Quartz Trigger Jobs.