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》