[SQL SERVER]Enhancement page allocations of user database

以前Tempdb我都會啟用TF 1117,1118 改善資源競爭問題

降低tempdb競爭是一件我覺得很重要的事情,畢竟tempdb只有一個,而且是大家都一定會使用的,

但真實世界中,偏偏我看到tempdb是最常被忽略的一個環節,

就如同我很常使用”公廁”來比喻tempdb,大家都可以上,但上完就馬上離開,

沒有人想管理”公廁”,現在TF 1117,1118也可以套用在 user database。

SQL 2016開始,針對user database我們可以使用 alter database 來達到TF 1117,1118效果,

TF1118簡單來說,一律都分配混和範圍,且分享給所有objects,

TF1117主要就是,相同filegroup中所有檔案,一起成長,這可以避免hot file情況,

我個人會對user database 都一律使用,因為我認為優點效益(提供更好的效能和可擴充性)遠大於缺點,

下面我簡單測試TF1117。

 

TF 1118

ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION { ON | OFF }

alter database ricoTF set mixed_page_allocation on

TF 1118 測試可參考我之前文章了解制式範圍和混和範圍

 

TF 1117

ALTER DATABASE <dbname> MODIFY FILEGROUP <filegroup> { AUTOGROW_ALL_FILES | AUTOGROW_SINGLE_FILE }

--enable
alter database ricoTF MODIFY FILEGROUP [primary]  AUTOGROW_ALL_FILES
--check
select is_autogrow_all_files 
from ricoTF.sys.filegroups

目前ricoTF的檔案資訊如下
SELECT name,type_desc, [Current Size in MB]=(size*8)/1024
, [Growth Size in MB]=(growth*8)/1024
FROM ricoTF.sys.database_files 

 

使用預設 AUTOGROW_SINGLE_FILE 並讓檔案成長

drop table if exists 	 mytest
create table mytest
(
c1 int
,c2 varchar(10)
)	 

;with mybase
as
(
 select top 1000000 t1.column_id,left(t2.name,10) as 'objname' 
 from sys.all_columns t1
 cross join sys.all_objects t2
)
insert into mytest with(tablock)
select  *
from   mybase

SELECT name,type_desc, [Current Size in MB]=(size*8)/1024
, [Growth Size in MB]=(growth*8)/1024
FROM ricoTF.sys.database_files

可以看到只有ricoTF 檔案成長而已。

 

現在,改成AUTOGROW_ALL_FILES後,來看看效果是否會如預期

drop database	  ricoTF
				   create database ricoTF on primary
				   (name='ricoTF',filename='E:\sql2k17data\ricoTF.mdf',size=8mb,maxsize=unlimited,filegrowth=8mb)
				   ,(name='ricoTF02',filename='E:\sql2k17data\ricoTF02.ndf',size=20mb,maxsize=unlimited,filegrowth=8mb)				  
				   log on
				   (name='ricoTF_Log',filename='E:\sql2k17data\ricoTF_Log.ldf',size=50mb,maxsize=unlimited,filegrowth=50mb)

alter database ricoTF MODIFY FILEGROUP [primary]  AUTOGROW_ALL_FILES

果然兩個檔案一起成長。

Ps:我這裡是要讓大家看到差異,實務上,所有檔案的初始和成長大小都建議相同。

 

參考

SQL Server 2016 Trace Flags 1118 and 1117 for Page Allocations

SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases

SQL2016-TempDB增強

TRACE FLAG 1117– AUTO GROW EQUALLY IN ALL DATA FILE

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads

Trace Flags 1117, 1118, and Tempdb Configuration

降低tempdb競爭

了解制式範圍和混和範圍