德瑞克：SQL Server 學習筆記: [SQL Server] AUTOGROW_ALL_FILES Option: All files in the filegroup grow.

SQL Server 2016 新增強：

使用 ALTER DATABASE 語法中的 AUTOGROW_ALL_FILES 來設定當 FileGroup 中的某個檔案達到自動成長閾值時， FileGroup 中的所有檔案都會自動同時成長。
此為 FileGroup 層級的屬性。
減少 allocation contention(配置競爭) 問題，提升效能。
預設 tempdb 有啟用 AUTOGROW_ALL_FILES。

先前版本，必須要額外啟用 Trace Flags 1117。

預設值是 AUTOGROW_SINGLE_FILE，當 FileGroup 中的某個檔案達到自動成長閾值時，只有該檔案會成長。
從 SQL Server 2016 (13.x) 開始，追蹤旗標 1117 沒有任何作用。

在 SSMS 17.8.1 版本亦支援 AUTOGROW_ALL_FILES 屬性與產生 T-SQL Script。

效能規劃建議

設計檔案資料時，啟用 Autogrowth，建議採用 MB 方式來成長，不使用 Percent 方式。
確保具備相同的大小，進而減少 allocation contention(配置競爭) 問題，提升效能。

FileGroup 內含多個資料檔案

SQL Server 對於 FileGroup 內的所有檔案，預設是使用比例性的填滿策略。
依據每個檔案中的可用空間，使用比例性的填滿策略，將資料採取循環寫入到檔案內。
如果檔案的可用空間不足，將觸發自動成長事件來增長檔案。
如此一來，在同一個 FileGroup 內的資料檔案，就有可能大小不一的問題。導致資料的數據分布不均衡。
若要資料能均勻分布到各個資料檔案，資料檔案應該具備相同的大小，進而減少 allocation contention(配置競爭) 問題，提升效能。

啟用 Trace Flags 1117

功用

當大量使用 tempdb 資料庫時，將造成 allocation contention(配置競爭)，拖延回應時間。
啟用 Trace Flags 1117，

影響到全部資料庫的每一個資料檔案。
當檔案群組中的某個檔案達到自動成長閾值時，檔案群組中的所有檔案都會成長。
減少 allocation contention(配置競爭) 問題，提升效能。

注意

從 SQL Server 2016 (13.x) 開始，追蹤旗標 1117 沒有任何作用。

改由 ALTER DATABASE 的 AUTOGROW_SINGLE_FILE 和 AUTOGROW_ALL_FILES 選項控制

缺點

Trace Flags 1117 卻是 global(全域) 層級的旗標。
一旦啟用，影響到全部的資料庫，導致耗用更多的硬碟空間。
改用 FileGroup 層級的 AUTOGROW_ALL_FILES，可依據資料存取特性，各別設定。

以下範例中

啟用 AUTOGROW_ALL_FILES 後，多耗用 34 % 的資料檔案空間。

AUTOGROW_ALL_FILES Option: All files in the filegroup grow.

01. 建立資料庫：FG_Move，並啟用 AUTOGROW_ALL_FILES 屬性。

參考以下說明：

Syntax
ALTER DATABASE [dbname] MODIFY FILEGROUP [filegroup] { AUTOGROW_ALL_FILES | AUTOGROW_SINGLE_FILE } 

For example:
ALTER DATABASE [FG_Move] MODIFY FILEGROUP [G4_ALL_FILES] AUTOGROW_ALL_FILES
GO

AUTOGROW_SINGLE_FILE

適用於： SQL Server ( SQL Server 2016 (13.x) 至 SQL Server 2017)
當檔案群組中的某個檔案達到自動成長閾值時，只有該檔案會成長。這是預設值。

AUTOGROW_ALL_FILES

適用於： SQL Server ( SQL Server 2016 (13.x) 至 SQL Server 2017)
當檔案群組中的某個檔案達到自動成長閾值時，檔案群組中的所有檔案都會成長。

FileGroup: G3_SINGLE_FILE 與 G4_ALL_FILES

兩者都包含有 4 個 Data Files，初始值: 10 MB，每次增量成長: 10 MB
但 FileGroup: G4_ALL_FILES 有啟用 AUTOGROW_ALL_FILES 資料庫屬性。

-- figure 01_Create_DB_FG_Move_AUTOGROW_ALL_FILES

02. SSMS 17.8.1 版本已經支援 AUTOGROW_ALL_FILES 屬性與產生 T-SQL Script。

使用 SSMS 切換資料庫屬性： AUTOGROW_ALL_FILES | AUTOGROW_SINGLE_FILE 。

-- figure 02_Autogrow_All_Files_SSMS

03. 觀察: 是否有啟用 AUTOGROW_ALL_FILES

使用 sys.filegroups 的 is_autogrow_all_files
適用於： SQL Server ( SQL Server 2016 (13.x) 至目前版本)。

1 = 當自動成長臨界值，而檔案群組中的所有檔案成長的檔案群組符合中的檔案。
0 = 時自動成長臨界值，只有該檔案成長的檔案群組符合中的檔案。這是預設值。

-- figure 31_is_autogrow_all_files

04. 觀察：全部資料庫的每個 FileGroup 是否有啟用 AUTOGROW_ALL_FILES 屬性。

-- figure 32_Get is_autogrow_all_files in all databases

05. 觀察: 資料庫內每個 FileGroup 的相關資訊

-- figure 03_List all filegroup and file of related information in database

-- figure 04_List all filegroup and file of related information in database

FileGroup: G3_SINGLE_FILE 與 G4_ALL_FILES

兩者都包含有 4 個 Data Files，初始值: 10 MB，每次增量成長: 10 MB
但 FileGroup: G4_ALL_FILES 有啟用 AUTOGROW_ALL_FILES 資料庫屬性。

06. 建立資料表與新增資料列。

建立 T1G3 與 T1G4 在 FileGroup: G3_SINGLE_FILE 與 G4_ALL_FILES 上。
每筆資料列的長度在 8000 byte 以上。
新增 7000 筆資料列。

-- figure 05_Create_Talbe_T1G3_T1G4_Insert_Rows

08. 新增資料列後，再度觀察：資料庫內每個 FileGroup 的相關資訊。

FileGroup: G3_SINGLE_FILE

各個 Data File 的成長量不一，分別是：20 , 10, 10, 20 (MB)。總使用量是： 60 MB。
Data_Size 非平均分布，分別是：20, 10, 10, 15.25 (MB)。總使用量是： 55.25 MB。

FileGroup: G4_ALL_FILES

各個 Data File 成長量相同，都是：20 (MB)。總使用量是： 80 MB。

相較之下，多耗用 20 MB，多耗用 34 % 的資料檔案空間。

Data_Size 採取平均分布，都是：13.8125 (MB)。總使用量是：55.25 MB。

-- figure 11_List all filegroup and file of related information in database

09. 觀察：資料表的使用空間，包含 Data 與 Index。

-- figure 21_Get size of tables(include index and data)

10. 觀察： Index 的 fragmentation。

T1G3 資料表的 Fragment_count: 656

預設值，AUTOGROW_SINGLE_FILE

T1G4 資料表的 Fragment_count 增加為 : 898

有啟用 AUTOGROW_ALL_FILES

-- figure 22_Get fragmentation for each index

11. 觀察 Report: Index_Physical_Statistics

T1G3 資料表的 Fragment_count: 656

預設值，AUTOGROW_SINGLE_FILE

T1G4 資料表的 Fragment_count 增加為 : 898

有啟用 AUTOGROW_ALL_FILES

-- figure 23_Index_Physical_Statistics

File and Filegroup Fill Strategy (檔案與檔案群組填滿策略)

以下為預設的 AUTOGROW_SINGLE_FILE 屬性。

檔案群組針對每個檔案群組內的所有檔案，使用比例性的填滿策略。

當資料寫入檔案群組時， SQL Server Database Engine 不會將所有資料都寫入第一個檔案，直到該檔案滿了為止；
而是在檔案群組內的每一個檔案中，寫入與檔案可用空間等比例的量，然後再寫入下一個檔案。

例如，
若檔案 f1 有 100 MB 可用空間、檔案 f2 有 200 MB 可用空間，系統就會從檔案 f1 配置 1 份範圍，再從檔案 f2 配置 2 份範圍，其餘依此類推。

如此一來，兩個檔案大約會在相同的時間填滿，而達成簡易等量配置。

檔案群組中的所有檔案都填滿之後， SQL Server Database Engine 會以循環配置的方式，自動一次擴充一個檔案，以容納更多資料，但前提是資料庫必須設定為自動成長。

例如，
檔案群組由 3 個檔案組成，全部都設為自動成長。

當檔案群組中所有檔案的空間都用完時，只會先擴充第 1 個檔案。
當第 1 個檔案已滿，不能再寫入更多資料到檔案群組時，再擴充第 2 個檔案。
當第 2 個檔案已滿，不能再寫入更多資料到檔案群組時，再擴充第 3 個檔案。
如果第 3 個檔案已滿，不能再寫入更多資料到檔案群組時，再重新擴充第 1 個檔案，依此類推。

Sample Code

20180701_AUTOGROW_ALL_FILES Option
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads
https://support.microsoft.com/en-us/help/2964518/recommended-updates-and-configuration-options-for-sql-server-2012-and

SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases
https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-changes-in-default-behavior-for-autogrow-and-allocations-for-tempdb-and-user-databases/

AUTOGROW_ALL_FILES and READ_ONLY aren't either updated by DDL or preserved after a database restarts in SQL Server 2016
https://support.microsoft.com/en-us/help/3163924/autogrow-all-files-and-read-only-aren-t-either-updated-by-ddl-or-prese

ALTER DATABASE (Transact-SQL) File and Filegroup Options
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-2017

Database Files and Filegroups
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases
https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/

Recommendations to reduce allocation contention in SQL Server tempdb database
https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d

DBCC TRACEON - Trace Flags (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

德瑞克：SQL Server 學習筆記

2018-07-01

[SQL Server] AUTOGROW_ALL_FILES Option: All files in the filegroup grow.

沒有留言:

張貼留言