揭秘Sql2014新特性-tempdb性能提升

一直以來,在高負載,複雜的生産環境中,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')      

View Code

                                                      圖1-3

Sq2014 code 執行Sql2008R2 SP2中腳本

測試結果如圖 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還是一直在進步.雖說很多不如意,但這裡我為微軟點個贊.

Involuntary DBA