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,
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,
在建立索引後再執行一次 Update statisticscommand:
Update Statistics花費3分56秒
SET QUOTED_IDENTIFIER 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_Aggregatedcommand:
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
執行次序
|
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
沒有留言:
張貼留言