仙仙熊SQL筆記: SQL Server enable trace flag 1118 with alter database

env: Windows Server 2016
SQL Server 2016 SP2

上次介紹利用Alter database啟用trace flag 1117 "SQL Server enable trace flag 1117 with alter database"，這次要介紹使用"Alter database"啟用trace flag 1118

Trace flag:1118
移除伺服器上大部分的單一頁面配置，以減少 SGAM 頁面的競爭情況。建立新物件時，根據預設，前八頁會從不同的範圍 (混合範圍) 進行配置。之後若需要更多頁面時，將會從相同的範圍 (統一範圍) 加以配置。 SGAM 頁面可用以追蹤這些混合範圍，因此若出現多個混合頁面配置，它會很快地成為瓶頸。這個追蹤旗標會在建立新物件時，從相同的範圍配置所有八個頁面，進而將掃描 SGAM 頁面的需求降到最低。如需詳細資訊，請參閱此 Microsoft 支援服務文章 (機器翻譯)。

注意：從 SQL Server 2016 (13.x) 開始，此行為由 ALTER DATABASE 的 SET MIXED_PAGE_ALLOCATION 選項控制，追蹤旗標 1118 沒有任何作用。如需詳細資訊，請參閱 ALTER DATABASE SET 選項 (Transact-SQL)。

範圍：只限全域

1.建立測試DB
command:
CREATE DATABASE [TBTEST02]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TBTEST01', FILENAME = N'E:\SQLData\TBTEST02.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TBTEST01_log', FILENAME = N'E:\SQLLog\TBTEST02_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

2.檢查測試DB否預設啟用trace flag 1117
command:
SELECT
[NAME],
is_mixed_page_allocation_on
FROM SYS.databases
WHERE [name] ='TBTEST02'

查詢後可以發現"is_mixed_page_allocation_on"為0

3.再次檢查測試DB與tempdb
command:
SELECT
[NAME],
is_mixed_page_allocation_on
FROM SYS.databases
WHERE [name] IN ('TBTEST02','tempdb')

查詢後可以發現"is_mixed_page_allocation_on"為0

4.建立測試表並建立測試資料
command:
USE [TBTEST02]
GO
CREATE TABLE test01
(
Name CHAR(8000)
) ON [PRIMARY]
GO

USE [TBTEST02]
GO
INSERT INTO [TBTEST02].dbo.test01 VALUES ('Larry222222222222')
GO 20

5.檢查測試DB的extent分布
command:
USE [TBTEST02]
GO
SELECT
DB_NAME(database_id) as DBName,
OBJECT_NAME(object_id) as ObjectName,
extent_page_id, page_type_desc
FROM sys.dm_db_database_page_allocations(DB_ID('TBTEST02'),
OBJECT_ID('test01'),NULL,NULL,'DETAILED')
--WHERE OBJECT_NAME(object_id) = 'test02'
go

extent_page_id : 320

所有的8kb pages放置在相同的extent

extent_page_id : 328

extent已經填滿，下一個extent已經配置

extent_page_id : 336

extent沒有填滿，下一個extent尚未配置

6.建立新的測試DB
command:
CREATE DATABASE [TBTEST03]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TBTEST03', FILENAME = N'E:\SQLData\TBTEST03.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TBTEST03_log', FILENAME = N'E:\SQLLog\TBTEST03_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

7.建立測試表
command:
USE [TBTEST02]
GO
CREATE TABLE test01
(
Name CHAR(8000)
) ON [PRIMARY]
GO

8.利用"Alter databaase"啟用trace flag 1117功能
command:
ALTER DATABASE [TBTEST03] SET MIXED_PAGE_ALLOCATION ON

9.增加測試資料
command:
USE [TBTEST03]
GO
INSERT INTO [TBTEST03].dbo.test01 VALUES ('Larry222222222222')
GO 20

10.檢查測試DB的extent分布
command:
USE [TBTEST03]
GO
SELECT
DB_NAME(database_id) as DBName,
OBJECT_NAME(object_id) as ObjectName,
extent_page_id,
page_type_desc,
is_mixed_page_allocation
FROM sys.dm_db_database_page_allocations(DB_ID('TBTEST03'),
OBJECT_ID('test01'),NULL,NULL,'DETAILED')
--WHERE OBJECT_NAME(object_id) = 'test02'
go

extent_page_id : 336

所有的8kb pages放置在相同的extent

extent_page_id : 344

extent已經填滿，下一個extent已經配置

extent_page_id : 352

extent沒有填滿，下一個extent尚未配置