1. 跟蹤標記 (Trace Flag) 1117, 1118 文件增長及空間分配方式

跟蹤標記：1117

功能：

• 預設，同一個文件組下的多個文件，如果某個文件沒有可用空間，且設置了自動增長，則該文件自動增長，其他文件大小保持不變；
• 開啟後，同一文件組下的多個文件，如果某個文件沒有可用空間，且設置了自動增長，文件組下所有文件同時增長，每個文件漲幅取決於自身filegrowth設置；

用途：

主要用於tempdb性能的優化，解釋如下：

1. 通常tempdb被配置為primary文件組下包含多個等大的數據文件，以最大化使用CPU，並且能有效減少對於PFS, SGAM, GAM的競爭(latch contention)；
2. 同一個文件組下的多個文件是等比例填充數據的(proportional fill)，也就是說所有的文件最終幾乎同時被填滿，這時需要文件自動增長空間；
3. 然後就會發現每個文件是逐個根據自身的filegrowth各自增長，這樣多次文件增長反而帶來不必要的開銷，尤其是當filegrowth設置的不夠大時；
4. 開啟跟蹤標記1117後，文件組下所有文件同時增長，當然如果將tempdb的多個數據文件初始化為一個較大的大小，也可以不考慮這個選項，因為文件不需要增長；

備註：

在SQL Server 2016里，跟蹤標記1117已經不再生效，功能被如下SQL語句所代替

ALTER DATABASE [Database_Name] 
MODIFY FILEGROUP [FileGroup_Name] AUTOGROW_ALL_FILES;
--自定義文件組，預設為AUTOGROW_SINGLE_FILE

跟蹤標記：1118

功能：

• 預設，對於新建的表或索引，會先在混合區(mixed extent)分配空間，等到表或索引占用空間超過8個頁後，才會在統一區(uniform extent)分配空間；
• 開啟後，對於新建的表或索引，直接使用統一區(uniform extent)分配空間；

用途：

主要用於tempdb性能的優化，解釋如下：

1. 表或索引被創建時，首先必須得使用PFS和SGAM頁在混合區分配空間；
2. 在大量使用臨時表的場景，對於PFS和SGAM頁的競爭(latch contention)會更加明顯；
3. 開啟跟蹤標記1118後，避開對SGAM頁的使用，在統一區分配新建對象的空間，也許有人會擔心全部使用統一區後GAM頁的競爭，GAM頁管理的是區(extent)，而SGAM雖然管理的也是區(extent)，但是一個區可以被多個對象使用，所以相比之下GAM頁的壓力要小很多；

備註：

在SQL Server 2016里，跟蹤標記1118已經不再生效，功能被如下SQL語句所代替

ALTER DATABASE [Database_Name] 
SET MIXED_PAGE_ALLOCATION OFF
--自定義資料庫，預設即為OFF，不使用混合區

小結：

對於tempdb的優化，通常會配置多個數據文件，以及開啟1118跟蹤標記，而1117更像是在文件增長方式上，對這兩者的補充。

從SQL Server 2016開始：

(1) 對於1117,1118這兩個跟蹤標記不再生效，但有了文件組/資料庫選項，粒度更小，而不用像之前那樣，開啟全局的跟蹤標記(對所有資料庫生效)，更為方便控制；

(2) 對於tempdb，文件增長：預設為文件組內全部增長；空間分配：預設為不使用混合區；

use tempdb
select * from sys.filegroups
--is_autogrow_all_files
--1

select * from sys.databases where name = 'tempdb'
--is_mixed_page_allocation_on
--0

(3) 對於用戶新建的資料庫，文件增長：預設為文件組內單個文件增長，和以前一樣；空間分配：預設為不使用混合區；

use DBA
select * from sys.filegroups
--is_autogrow_all_files
--0

select * from sys.databases where name = 'DBA'
--is_mixed_page_allocation_on
--0

(4) 另外在安裝SQL Server 2016時，還根據CPU核數動態設置了tempdb的文件數，所以到了SQL Server 2016，tempdb的優化選項幾乎全部採用預設即可。

參考：

SQL Server 2016 – Trace Flag 1117 Changes

http://www.sqlservergeeks.com/sql-server-2016-trace-flag-1117-changes/

Working with tempdb in SQL Server 2005

https://technet.microsoft.com/en-us/library/cc966545.aspx

kb/328551

https://support.microsoft.com/en-us/kb/328551

kb/2154845

https://support.microsoft.com/en-us/kb/2154845

Trace Flags (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms188396.aspx

Trace Flags 1117, 1118, and Tempdb Configuration

https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/

《Fast Track Data Warehouse 2.0 Architecture.docx》