首页 > 开发 > 综合 > 正文

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

2024-07-21 02:50:48
字体:
来源:转载
供稿:网友
Tempdb--关于表变量的一点疑问和测试

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

测试方式:

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

1. 测试用户表

--=================================================--测试tempdb上的表USE tempdbGOCREATE TABLE TB1(    ID INT)GOSELECT * FROM SYS.dm_io_virtual_file_stats(2,1)SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)DECLARE @ID INTSET @ID=0WHILE(@ID<10000)BEGININSERT INTO TB1SELECT @IDSET @ID=@ID+1ENDSELECT * 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 INTSET @ID=0WHILE(@ID<10000)BEGININSERT INTO @TB1SELECT @IDSET @ID=@ID+1ENDSELECT * 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 INTSET @ID=0WHILE(@ID<10000)BEGININSERT INTO #TB1SELECT @IDSET @ID=@ID+1ENDSELECT * 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

存储过程重新编译

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

照例是妹子镇贴:


发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表