你的位置:首页 > 数据库

[数据库]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 tempdbselect * from sys.filegroups--is_autogrow_all_files--1select * from sys.databases where name = 'tempdb'--is_mixed_page_allocation_on--0

(3) 对于用户新建的数据库,文件增长:默认为文件组内单个文件增长,和以前一样;空间分配:默认为不使用混合区

use DBAselect * from sys.filegroups--is_autogrow_all_files--0select * 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》