Blocking situations in a Microsoft SQL Server database
Last update: 29 May 2020 (cf)
Summary
Based on the user behaviour and the load on a specific Fabasoft production environment it can be possible that you notice situations in your Microsoft SQL Server database where a long running query blocks other SQL statements. This can lead to blocked threads of the Fabasoft COO service that is using this database and in worst case to a system outage until the long running transaction is finished.
Information
This behaviour can be triggered by the fact that some SQL statements lock a row or the whole database table (due to lock escalation of Microsoft SQL Server). This means that other queries have to wait until the first statement is finished. Fabasoft Support noticed this behaviour in Fabasoft Folio or Fabasoft eGov-Suite installations with Microsoft SQL Server versions >=2005.
Solution
Fabasoft recommends identifying and optimizing long running transactions (e.g. with Fabasoft app.telemetry) that are leading to such a situation. Nevertheless there is a possibility to change the isolation level in Microsoft SQL Server to "READ_COMMITTED_SNAPSHOT" to reduce the impact of long running queries. Please find more information about isolation levels on the following Microsoft web pages:
Attention
All changes to the database presuppose a consistent backup of the whole database and stopped Fabasoft COO-Services. Fabasoft has not made regression tests (especially due to performance and stability) with the lock level described in this article. If you experience problems when testing this lock level in your installation please contact Fabasoft Support.
Applies to
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
- Microsoft SQL Server 2008 R2