一直以来,在高负载,复杂的生产环境中,tempdb的压力是成为整个实例瓶颈的重要因素之一.微软的工程师们也在各个版本中不断优化它的使用.到了Sql Server2014又有了新的特性使其性能得tempdb的性能有一定提升.这里我将通过实例给大家介绍tempdb在新版本中的实现变化.

我们都知道tempdb的日志无需提前落盘是其快的重要原因之一,日志无需落盘,那么数据又是如何操作的呢?这里先介绍一个重要的概念:主动写(Eager write)

主动写:Sql server引擎在面对最小化日志操作的情形时(blukinsert,select into,etc)会采用主动写的方式将数据页批量刷入(一般32页/次)磁盘,以减小惰性写(lazy write),检查点(checkpoint)操作时所带来的磁盘压力.

熟悉Sqlserver开发的DBA或是开发人员应该知道,在使用tempdb时存在着大量的最小化日志操作,如select * into #xx,而Sql Server由于其引擎主动写的特性使得即便是批量写入的优化方式也会使得由于tempdb的频繁写入使整个磁盘IO面临较大的压力.好在微软的工程师们注意到了这个情况,在Sql2014的tempdb中引擎放松了主动写在tempdb的必要性,在特定操作中数据页可以驻留在内存中而无需主动刷入磁盘(Sqlserver认为短暂的时间窗口),减轻了磁盘的负载,于此同时也使得tempdb的性能更高效.这里我通过一个简单的实例给大家演示.

注:为了演示不同版本的特性,我们需要开启跟踪标记(TF 3917)用以捕捉主动写行为.

我们先来看下sql2008R2 SP2下的情况 如图1-1

select @@VERSION--Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 

dbcc traceON(3604,3917,-1)----catch eager write

select * into aaa from dbo.bigProduct
dbcc traceOFF(3604,3917,-1)-----remember turn off the TF!

图1-1

接下来同样的脚本我们在sql2014中执行如图1-2

Code Sql2014 tempdb does no eager write

select @@VERSION--Microsoft SQL Server 2014 - 12.0.2000.8 (X64)  

dbcc traceON(3604,3917,-1)----catch eager write

select * into #ttt from dbo.bigProduct

dbcc traceOFF(3604,3917,-1)-----remember turn off the TF!

图1-2

通过实例我们可以看到在sql2014中,tempdb中的操作并没有因为select into而触发主动写,使得磁盘写操作得以避免.

注:此特性只实用与tempdb中,用户数据库新版本中依旧有主动写实现特性.感兴趣的朋友可以自行测试

也许有的朋友认为批量写入已经是优化行为,这个tempdb中的小小变动不足缓解其瓶颈问题,这里我们通过一个简单的实例来说明下.

Sql2008R2 SP2 code

注:测试前请重启下sql server实例,使得tempdb计数尽量准确

测试结果如图 1-3

use AdventureWorks
go
create proc p_tempdbtest
as
select * into #ttt
from dbo.bigProduct

declare @t datetime2=sysutcdatetime()
declare @i int
set @i=1
while (@i<100)
begin
exec p_tempdbtest
select @i=@i+1
end
select [extime]=DATEDIFF(S,@t,sysutcdatetime())

SELECT
       d.name AS database_name,
       f.name AS [file_name],
       f.physical_name,
       f.type_desc,
       vf.num_of_reads,
       vf.num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vf
INNER JOIN sys.databases AS d
ON d.database_id = vf.database_id
INNER JOIN sys.master_files AS f
ON f.file_id = vf.file_id
   AND f.database_id = vf.database_id
where f.database_id = db_id('tempdb')

图1-3

Sq2014 code 执行Sql2008R2 SP2中脚本

注:测试前请重启下sql server实例,使得tempdb计数尽量准确

测试结果如图 1-4

图1-4

由于笔者的测试的两台服务器测试机均为4块R5 15K磁盘,可以认为测试过程磁盘IO环境相同.通过测试我们可以看到,新版本中的tempdb的这个规避一定主动写的行为可以使得tempdb很多情形中(select into,create index,etc)写的压力得到极大缓解,提高的单个操作效率的同时也使得整体性能提升.

注:Sql Server的CSS团队声称在sql 2012版本的后续补丁中可能实现此功能.

Sql Server在面对很大内存压力时,惰性写(Lazy Write)同样会将数据页刷入磁盘.、

Tempdb中非临时对象仍然需主动写.(感兴趣朋友可以自行测试)

结语:关系型数据库发展至今,个人认为理念上很难再有大的突破,产品的细节实现更能决体现产品的优秀程度.微软在根据使用的场景对特定库的不同操作依据其特点采用不同的实现方式让我觉得SqlServer还是一直在进步.虽说很多不如意,但这里我为微软点个赞.

作者：shanks_gao

原文地址：Involuntary DBA（http://www.cnblogs.com/shanksgao/p/3949251.html）