在思考表变量与临时表之间区别时,表变量不会受事务回滚的影响,那么是否意味着表变量无需写入日志呢?

测试方式:

分别对tempdb上的用户表/临时表/表变量 进行10000次插入,查看日志写入次数,使用SYS.dm_io_virtual_file_stats可以查看数据库文件的读入和写入次数,也可以使用process monitor来捕获在tempdb的日志文件上的操作

1. 测试用户表

--=================================================
--测试tempdb上的表
USE tempdb
GO
CREATE TABLE TB1
(
ID INT
)
GO
SELECT * FROM SYS.dm_io_virtual_file_stats(2,1)
SELECT * FROM SYS.dm_io_virtual_file_stats(2,2) DECLARE @ID INT
SET @ID=0
WHILE(@ID<10000)
BEGIN
INSERT INTO TB1
SELECT @ID
SET @ID=@ID+1
END
SELECT * FROM SYS.dm_io_virtual_file_stats(2,1)
SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)

2. 测试表变量

SELECT * FROM SYS.dm_io_virtual_file_stats(2,1)
SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)
DECLARE @TB1 TABLE
(
ID INT
)
DECLARE @ID INT
SET @ID=0
WHILE(@ID<10000)
BEGIN
INSERT INTO @TB1
SELECT @ID
SET @ID=@ID+1
END SELECT * FROM SYS.dm_io_virtual_file_stats(2,1)
SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)

3.测试临时表

SELECT * FROM SYS.dm_io_virtual_file_stats(2,1)
SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)
CREATE TABLE #TB1
(
ID INT
)
DECLARE @ID INT
SET @ID=0
WHILE(@ID<10000)
BEGIN
INSERT INTO #TB1
SELECT @ID
SET @ID=@ID+1
END SELECT * FROM SYS.dm_io_virtual_file_stats(2,1)
SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)

部分截图:

Tempdb--关于表变量的一点疑问和测试-LMLPHP

Tempdb--关于表变量的一点疑问和测试-LMLPHP

测试结果:

对边两次SYS.dm_io_virtual_file_stats的结果,可以发现日志上发生41次写操作,而process monitor也验证了41次写操作,每次写操作写入61kb数据(再次证明每次日志最大写入量)

无论是tempdb上的用户表还是表标量以及临时表,相同的操作所引发的日志写入次数是一样的,写入的总日志大小也大致相同。

--================================================================

在MS网站上找到如下:

涉及表变量的事务仅维持表变量上更新的持续时间。因此,使用表变量时,需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分,所以事务回滚并不影响它们。

原文地址:http://support.microsoft.com/kb/305977/zh-cn

强烈推荐各位读一读这一篇

--=================================================================

查看操作临时表的日志:

Tempdb--关于表变量的一点疑问和测试-LMLPHP

(注:表变量在tempdb中以临时表的方式出现)

--==================================================================

个人猜想1:

既然MS把表变量的操作也记录在日志中,那么肯定有其保存的原因,在上面的测试过程中,表变量单独运行,但在很多事务中,表变量会参与其他用户对象的修改操作(如关联更新),因此需要将其变化记录到日志中,以便后续使用。

个人猜想2:

MS在SQL SERVER底层的一些操作上,把表变量和临时表当成同一类型来处理,因此调用了和临时表相同的方法来处理表变量,因此导致表变量也写入日志。

--==================================================================

关于tempdb上的redo和undo

由于tempdb在每次实例重启后重新创建,不需要对tempdb上的数据做持久化处理,也不会发生redo操作,因此在记录日志时,不需要记录after image,只记录before image用于事务回滚操作。

PS:在tempdb上不会每次commit都触发写日志操作(本文上述测试中执行10000次事务,但只发生了41次写)。

--==================================================================

相关链接:

Understanding data vs log usage for spills in tempdb

Working with tempdb in SQL Server 2005

存储过程重新编译

--===================================================================

照例是妹子镇贴:

Tempdb--关于表变量的一点疑问和测试-LMLPHP

05-11 14:45