SQL Server speedup create index in alwayson

env: Windows Server 2106

        SQL Server 2016 SP2

 

SQL Server 2012開始推出Alwayson功能，這個功能強化原本Database Mirroring，做到讀寫分離的目的，但伴隨而來的是維護所引伴隨而來的Latency問題。

因此要在大資料的環境要加速索引的建置速度降低Alwayson latency時間。

 

我們測試的環境是單機的虛擬機，8 Cores，DB只有一個Data file。SQL Server有開啟Trace Flag 1117, 1118。

Trace Flag 1117:

當檔案群組中的某個檔案達到自動成長閾值時，檔案群組中的所有檔案都會成長。

注意： 從 SQL Server 2016 (13.x) 開始，此行為由 ALTER DATABASE 的 AUTOGROW_SINGLE_FILE 和 AUTOGROW_ALL_FILES 選項控制，追蹤旗標 1117 沒有任何作用。

Trace Flag 1118:

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

注意： 從 SQL Server 2016 (13.x) 開始，此行為由 ALTER DATABASE 的 SET MIXED_PAGE_ALLOCATION 選項控制，追蹤旗標 1118 沒有任何作用。

DBCC TRACEON - Trace Flags (Transact-SQL)

 

1.環境1: TRACE FLAG 1117, 1118。8 Cores。Create Index with online。

command:

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_External] ON [dbo].[testtb01]
(
[External] DESC
)
INCLUDE ( [Id]) WITH (DATA_COMPRESSION = PAGE, Online=on)
GO

SET QUOTED_IDENTIFIER ON
CREATE NONCLUSTERED INDEX [IX_Aggregated] ON [dbo].[testtb01]
(
[Aggregated] DESC
)
INCLUDE ([Id]) WITH (DATA_COMPRESSION = PAGE, ONLINE=ON)
GO

結果:

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
1	DBTEST01	DBTEST01	testtb01	18	IX_External	768101126	40078	56
2	DBTEST01	DBTEST01	testtb01	11	IX_Aggregated	768101126	46985	36

 

2.環境2: TRACE FLAG 1117, 1118。8 Cores。Create Index with online。

使用TempDB排序建立。

command:

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_External] ON [dbo].[testtb01]
(
[External] DESC
)
INCLUDE ( [Id]) WITH (DATA_COMPRESSION = PAGE, Online=ON, SORT_IN_TEMPDB=ON)
GO

SET QUOTED_IDENTIFIER ON
CREATE NONCLUSTERED INDEX [IX_Aggregated] ON [dbo].[testtb01]
(
[Aggregated] DESC
)
INCLUDE ([Id]) WITH (DATA_COMPRESSION = PAGE, ONLINE=ON, SORT_IN_TEMPDB=ON)
GO

結果:TempDB使用40GB，速度沒有明顯提升。可能環境沒有優化造成。

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
1	DBTEST01	DBTEST01	testtb01	18	IX_External	768101126	40078	62
2	DBTEST01	DBTEST01	testtb01	11	IX_Aggregated	768101126	46985	38

 

3.環境3: TRACE FLAG 1117, 1118。8 Cores。Create Index with online。

這次追加Trace Flag 610

Trace Flag 610:

控制以最低限度方式插入索引資料表的行為。從 SQL Server 2016 開始不需要此追蹤旗標，因為預設會針對索引資料表開啟最低限度記錄功能。在 SQL Server 2016 中，當大量載入作業導致系統配置新的頁面時，如果符合最低限度記錄的其他所有先決條件，則依序填滿新頁面的所有資料列會以最低限度方式記錄。插入到現有頁面 (未配置新頁面) 以維持索引順序的資料列仍會完整記錄，這些是載入期間因頁面分割而移除的資料列。此外也必須開啟索引的 ALLOW_PAGE_LOCKS (預設為「開啟」) 以啟用最低限度記錄作業，因為配置期間需要頁面鎖定，也因此只會記錄頁面或範圍配置。

command:

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_External] ON [dbo].[testtb01]
(
[External] DESC
)
INCLUDE ( [Id]) WITH (DATA_COMPRESSION = PAGE, Online=on)
GO

SET QUOTED_IDENTIFIER ON
CREATE NONCLUSTERED INDEX [IX_Aggregated] ON [dbo].[testtb01]
(
[Aggregated] DESC
)
INCLUDE ([Id]) WITH (DATA_COMPRESSION = PAGE, ONLINE=ON)
GO

結果:建立索引時間降低，但是非常有限。

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
1	DBTEST01	DBTEST01	testtb01	18	IX_External	768101126	40078	52
2	DBTEST01	DBTEST01	testtb01	11	IX_Aggregated	768101126	46985	32

 

4.環境4: TRACE FLAG 1117, 1118, 610。8 Cores。Create Index with online。

增加Update statistics 8000000 rows, 使用TempDB排序建立。

command:

Update Statistics花費3分36秒

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_External] ON [dbo].[testtb01]
(
[External] DESC
)
INCLUDE ( [Id]) WITH (DATA_COMPRESSION = PAGE, Online=ON, SORT_IN_TEMPDB=ON)
GO

SET QUOTED_IDENTIFIER ON
CREATE NONCLUSTERED INDEX [IX_Aggregated] ON [dbo].[testtb01]
(
[Aggregated] DESC
)
INCLUDE ([Id]) WITH (DATA_COMPRESSION = PAGE, ONLINE=ON, SORT_IN_TEMPDB=ON)
GO

結果:整體時間有明顯下降。

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
1	DBTEST01	DBTEST01	testtb01	18	IX_External	768101126	40078	38
2	DBTEST01	DBTEST01	testtb01	11	IX_Aggregated	768101126	46985	34

 

5.環境5: TRACE FLAG 1117, 1118, 610。8 Cores。Create Index with online。

增加Update statistics 8000000 rows, 使用TempDB排序建立。

在建立索引後再執行一次 Update statistics

command:

Update Statistics花費3分56秒

SET QUOTED_IDENTIFIER ONSET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_External] ON [dbo].[testtb01]
(
[External] DESC
)
INCLUDE ( [Id]) WITH (DATA_COMPRESSION = PAGE, Online=on)
GO

Update Statistics花費4分13秒

SET QUOTED_IDENTIFIER ON
CREATE NONCLUSTERED INDEX [IX_Aggregated] ON [dbo].[testtb01]
(
[Aggregated] DESC
)
INCLUDE ([Id]) WITH (DATA_COMPRESSION = PAGE, ONLINE=ON)
GO

結果:與執行一次Update statistics差不多，但是整體效益沒有提升。

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
1	DBTEST01	DBTEST01	testtb01	18	IX_External	768101126	40078	38
2	DBTEST01	DBTEST01	testtb01	11	IX_Aggregated	768101126	46985	36

 

6.環境6: TRACE FLAG 1117, 1118, 610。8 Cores。Create Index with online。

降低Update statistics 4000000 rows。

command:

Update Statistics花費3分12秒

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_External] ON [dbo].[testtb01]
(
[External] DESC
)
INCLUDE ( [Id]) WITH (DATA_COMPRESSION = PAGE, Online=on)
GO

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_Aggregated] ON [dbo].[testtb01]
(
[Aggregated] DESC
)
INCLUDE ([Id]) WITH (DATA_COMPRESSION = PAGE, ONLINE=ON)
GO

結果:整體效益沒有提升。

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
1	DBTEST01	DBTEST01	testtb01	18	IX_External	768101126	40078	35
2	DBTEST01	DBTEST01	testtb01	11	IX_Aggregated	768101126	46985	38

 

7.環境7: TRACE FLAG 1117, 1118, 610。8 Cores。Create Index with online。

僅對Clister Index執行Update statistics 8000000 rows。

command:

Update Statistics花費4秒

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_External] ON [dbo].[testtb01]
(
[External] DESC
)
INCLUDE ( [Id]) WITH (DATA_COMPRESSION = PAGE, Online=on)
GO

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_Aggregated] ON [dbo].[testtb01]
(
[Aggregated] DESC
)
INCLUDE ([Id]) WITH (DATA_COMPRESSION = PAGE, ONLINE=ON)
GO

結果:狀況變差了。

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
1	DBTEST01	DBTEST01	testtb01	18	IX_External	768101126	40078	60
2	DBTEST01	DBTEST01	testtb01	11	IX_Aggregated	768101126	46985	38

 

8.環境8: TRACE FLAG 1117, 1118, 610。8 Cores。Create Index with online。

僅對Clister Index執行Update statistics 2000000 rows。

這次測試所引建立索引: IX_Aggregated

command:

Update Statistics花費27秒


SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_External] ON [dbo].[testtb01]
(
[External] DESC
)
INCLUDE ( [Id]) WITH (DATA_COMPRESSION = PAGE, Online=on)
GO

結果:與測試環境7相似，需要1小時。

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
1	DBTEST01	DBTEST01	testtb01	18	IX_External	768101126	40078	59

 

9.環境9: TRACE FLAG 1117, 1118, 610。8 Cores。Create Index with online。

接續環境8，嘗試把系統overlapping統計資訊移除。

command:

DROP STATISTICS [dbo].[testtb01].[_WA_Sys_00000008_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_00000011_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_00000003_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_0000000E_49C3F6B7]


SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_Aggregated] ON [dbo].[testtb01]
(
[Aggregated] DESC
)
INCLUDE ([Id]) WITH (DATA_COMPRESSION = PAGE, ONLINE=ON)
GO

結果:在"IX_Aggregated"表現沒有很大的差異。

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
2	DBTEST01	DBTEST01	testtb01	11	IX_Aggregated	768101126	46985	35

 

10.環境10: TRACE FLAG 1117, 1118, 610。8 Cores。Create Index with online。

依據環境8, 9的狀況，重新測試移除系統統計資訊，再Update statistics 4000000 rows

command:

DROP STATISTICS [dbo].[testtb01].[_WA_Sys_00000003_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_00000004_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_00000005_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_00000006_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_00000008_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_0000000C_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_0000000D_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_0000000E_49C3F6B7]
DROP STATISTICS [dbo].[testtb01].[_WA_Sys_00000011_49C3F6B7]

Update Statistics花費1分26秒

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_External] ON [dbo].[testtb01]
(
[External] DESC
)
INCLUDE ( [Id]) WITH (DATA_COMPRESSION = PAGE, Online=on)
GO

SET QUOTED_IDENTIFIER ON
SET STATISTICS PROFILE ON
CREATE NONCLUSTERED INDEX [IX_Aggregated] ON [dbo].[testtb01]
(
[Aggregated] DESC
)
INCLUDE ([Id]) WITH (DATA_COMPRESSION = PAGE, ONLINE=ON)
GO

結果:時間與環境3差不多。

執行次序	servername	db_date	table_name	idx_id	index_name	row_count	data_used_mb	Exec(Mins)
1	DBTEST01	DBTEST01	testtb01	18	IX_External	768101126	40078	41
2	DBTEST01	DBTEST01	testtb01	11	IX_Aggregated	768101126	46985	40

結論:

在環境沒有特別優化，特別是TempDB在比較慢的磁碟，並且要在Alwayson環境建立索引且有時間急迫行的狀況，可以參考做以下的步驟提升速度。

1.enable trace flag 610

2.update statistics 4000000 rows