SQL Server中事务对性能的影响

之前改一些死锁问题时调整了事务的范围，改之后出现了几个性能问题，调查发现原先有个SP的循环是放在事务内部的，现在没有事务了，时间从30s变成了70s。

做个对比的小实验
create table testTran (id int, Name varchar(8))
go

– 13 sec
– Autocommit transaction
declare @i int
set @i = 0
while @i < 100000
begin
insert into testTran values (1,'test')
set @i = @i+1
end
GO

---

– 1 sec
BEGIN transaction
declare @i int
set @i = 0
while @i < 100000
begin
insert into testTran values (1,'test')
set @i = @i+1
end
COMMIT;

第一个需要13s，第二个只要1s。第一次每个insert语句都是一个独立的事务，所以共有100000个事务，第二个用了显式的事务，只有一个

每次事务中要申请，释放锁，结束时要写事务日志，这些都是开销

两个解决方法

1. 在循环外面加上事务
2. 使用DELAYED DURABILITY， 开启后，第一段循环只要2s。

详细内容参考：
ALTER DATABASE dbname SET DELAYED_DURABILITY = DISABLED | ALLOWED | FORCED;
https://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014