[SQL SERVER]Enhancement page allocations of user database

2018-06-24

747
0
SQL_SERVER
2020-12-31

以前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