From version 11.20 onwards, Syncsort™ Capacity Management and Athene enable snapshot isolation level on the SQL Server database. As a result, Data Summarizer improvements increase the workload on SQL Server.
Prior to 11.20, Athene exclusively used a lock-based model to manage concurrency. While this was effective in the past, it is not suitable to handle these much higher workloads, as it increases the probability of deadlocks occurring. Deadlocks are the result of contention between different transactions, and they result in a serious error whereby one of the contending transactions is aborted.
The solution to this is to use Snapshot Isolation, which is implemented using multi-version concurrency control and so does not acquire locks. For this reason, the Syncsort™ Capacity Management database has been updated to allow Snapshot Isolation. A limited number of transactions, ones which have a higher tendency to deadlock, will request this new isolation level.
Snapshot Isolation has the following impacts on SQL Server:
-
Additional 14-bytes-per-record being updated or deleted, which is used for row versioning.
-
Creates a copy of any record being updated or deleted in TEMPDB before the record gets modified.
The impact of these additional steps are as follows:
-
Rate of growth of the database’s transaction log is increased
While it is impossible to predict the transaction log growth rate for a particular installation of Syncsort™ Capacity Management (it is dependent on a multitude of factors that are unique to each customer), it is worth noting that Precisely’s QA Team measured the increase at 40% for the particular workloads that they were testing. The increase in transaction log growth means that it is particularly important to regularly backup the database in order to prevent the transaction log from consuming all of the system’s available disk space.
-
Performance Degradation
Precisely has seen a 5% decrease in the rate of throughput when Integrator data is processed by the Control Center during testing, however it may have a larger impact on other data types.
If you experience a significant performance degradation in Control Center due to the Snapshot Isolation being turned on, it can be turned off. However, turning this off will produce a small risk of deadlocks when Control Center and Data Summarizer are both acting on Integrator tables.
If deadlocks occur, Control Center is usually chosen as the deadlock victim, however, to prevent data loss, and retries breaking the deadlock up to three times. However, if a deadlock occurs for all three retries, there could be some data loss for the Integrator Template affected.
-
Additional utilization of TEMPDB
As TEMPDB is more heavily utilized by Syncsort™ Capacity Management than in previous releases, this may impact the use of the other databases on the SQL Server that also use TEMPDB.