mssql sqlserver 加快数据库回滚的速度–ACCELERATED_DATABASE_RECOVERY用法说明


摘要:
下文通过举例的方式,讲述sqlserver中加速数据库回滚时间的方法,如下所示:
实验环境:sql server 2019


当一个长事务被意外终止或停止时,回滚此事务需要大量的时间,下文讲述sqlserver2019中通过开启

ACCELERATED_DATABASE_RECOVERY
属性,来提升sqlserver事务回滚时间的方法

例: 测试方案一

---1.创建数据库 

CREATE DATABASE [maomao];
GO
USE [maomao]
GO
SET STATISTICS TIME ON
GO

---2.开启事务,并运行以下脚本
BEGIN TRANSACTION
 

DROP TABLE IF EXISTS testTable;
 
---生成新数据表,并插入数据
SELECT TOP 10000000 a1.* 
INTO testTable 
FROM sys.all_objects AS a1
CROSS JOIN sys.all_objects AS a2
CROSS JOIN sys.all_objects AS a3
GO

 
/*
SQL Server Execution Times:
CPU time = 1632000 ms, elapsed time = 26108 ms.
*/

---3回滚事务
ROLLBACK
GO 
/*
SQL Server Execution Times:
CPU time = 386000 ms, elapsed time = 12603 ms.
*/ 

例:
测试方案2:
设置 ACCELERATED_DATABASE_RECOVERY属性为ON

 ALTER DATABASE [maomao] 
  SET ACCELERATED_DATABASE_RECOVERY = ON;
  GO 


---2.开启事务,并运行以下脚本
BEGIN TRANSACTION
 

DROP TABLE IF EXISTS testTable;
 
---生成新数据表,并插入数据
SELECT TOP 10000000 a1.* 
INTO testTable 
FROM sys.all_objects AS a1
CROSS JOIN sys.all_objects AS a2
CROSS JOIN sys.all_objects AS a3
GO

 
/*
SQL Server Execution Times:
CPU time = 1632000 ms, elapsed time = 26108 ms.
*/

---3回滚事务
ROLLBACK
GO 
/*
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
*/ 


从以上的两个测试用例上,我们可以看出设置ACCELERATED_DATABASE_RECOVERY属性后,rollback性能得到了质的飞跃。