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

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

测试方式：

分别对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)

View Code

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)

View Code

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)

View Code

部分截图：

测试结果：

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

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

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

在MS网站上找到如下：

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

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

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

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

查看操作临时表的日志：

（注：表变量在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--关于表变量的一点疑问和测试,古老的榕树,5-wow.com