適合 SQL Server 執行個體的最佳做法

本頁提供如何將執行 Microsoft SQL Server 的 Google Compute Engine 執行個體最佳化的資訊。想瞭解如何設定高效能 SQL Server 執行個體,請查看教學課程

設定 Windows

本節說明在 Compute Engine 上執行時,如何針對 SQL Server 效能最佳化 Microsoft Windows 作業系統的設定主題。

設定 Windows 防火牆

最佳做法:使用 Windows Server 進階防火牆,並指定您用戶端電腦的 IP 位址。

Windows 進階防火牆是 Windows Server 中的重要安全元件。當您設定 SQL Server 環境以從其他用戶端電腦連線至資料庫時,請設定防火牆允許連入流量通過:

netsh advfirewall firewall add rule name="SQL Access" ^
dir=in action=allow ^
program="%programfiles%\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" ^
remoteip=[LOCAL_SUBNET]

當您使用這項防火牆規則時,建議指定用戶端電腦的 IP 位址。指定以逗號分隔的 IP 位址清單,在取代 [LOCAL_SUBNET]remoteip 參數中不含空格。另外請注意,視您使用的 SQL Server 版本而定,program 參數的路徑可能會變更。

SQL Server 應用程式映像檔包含 SQL Server 的 Windows 防火牆規則。這項規則的限制不多,因此可考慮在系統實際上線前予以停用。

微調網路連線

最佳做法:使用作業系統的預設網路設定。

大多數作業系統的預設網路設定,是為連線到普通快網路的小型電腦而設定。這類設定通常已足夠。此外,保守的預設值可確保網路流量不會對網路和連線電腦帶來太大衝擊。

在 Compute Engine 上,虛擬機器執行個體會連上由 Google 設計的高容量和高效能網路。執行 Compute Engine 執行個體的實體伺服器經過高度最佳化,可以運用這種網路容量。您執行個體上的虛擬驅動程式也經過最佳化,因此預設值對大多數用途來說已經足夠。

安裝防毒程式

最佳做法:依循 Microsoft 的防毒軟體指引。

如果您使用 Windows,則應執行某種防毒軟體。惡意軟體和軟體病毒對連上網路的任何系統來說都是重大風險,而防毒軟體是您可用來保護資料的簡單緩解步驟。不過,如果防毒軟體未正確設定,可能會對您的資料庫效能有負面影響。 Microsoft 提供如何選擇防毒軟體的建議

針對效能和穩定性最佳化

本節說明如何將 Compute Engine 上的 SQL Server 效能最佳化,以及協助順暢執行的操作活動。

將資料檔案和記錄檔移至新磁碟

最佳做法:使用另外的 SSD 永久磁碟存放記錄檔和資料檔案。

根據預設,掛接為「C:」磁碟的開機永久磁碟上安裝的所有項目,會隨附在預先設定的 SQL Server 映像檔中。請考慮加裝第二個 SSD 永久磁碟,並將記錄檔和資料檔案移到新的磁碟。

使用本機 SSD 以改善 IOPS

最佳做法:建立具有一或多個本機 SSD 的新 SQL Server 執行個體,以儲存 tempdb 和 Windows 分頁檔。

本機 SSD 技術屬於暫時性質,因此不適合用來存放關鍵的資料庫和重要的檔案。不過 tempdb 和 Windows 分頁檔都是暫時的檔案,因此適合移到本機 SSD,還可大幅減少 SSD 永久磁碟的 IO 作業數量。相關設定詳情請前往這裡

平行查詢處理

最佳做法:將 max degree of parallelism 設為 8

建議的 max degree of parallelism 預設設定要符合伺服器上的 CPU 數。不過,如果將一個查詢分成 16 或 32 個片段,全在不同的 vCPU 上執行,然後再整合成單一結果的時間,會比單一 vCPU 執行查詢的時間還長。在實務上,預設值 8 的運作良好。

最佳做法:監控 CXPACKET 等待並逐漸增加 cost threshold for parallelism

這項設定須和 max degree of parallelism 一起設定。每一單元代表在考慮進行平行執行計劃之前,以序列執行計劃進行查詢時需要的 CPU 和 IO 作業組合。預設值為 5。雖然我們不會明確建議變更預設值,但最好持續注意,而且如有必要,可在負載測試時逐漸增加 5。可能需要增加值的一項重要指標是出現 CXPACKET 等待。雖然出現 CXPACKET 等待不一定表示這項設定應該變更,但這是好用的第一個指標。

最佳做法:監控不同的等待類型,調整全域平行處理設定,或設定成個別資訊庫層級。

個別資料庫可能有不同的平行處理需求。您可以將這些設定值設為全域通用,並將 Max DOP 設定為個別資料庫層級。您應觀察自己的獨特工作負載、監控等待,然後視情況調整設定值。

SQLSkills 網站提供實用的效能指南,說明資料庫內的等待統計資料。依循這份指南可協助您瞭解在等待什麼,以及如何減少延遲。

處理交易記錄

最佳做法:監控系統中交易記錄的成長。請考慮停用自動成長,並依據您每日平均記錄累積量,將記錄檔設定成固定大小。

其中一個最常被忽視的效能損失和間歇減速原因,是交易記錄的成長未受管理。當資料庫設定為使用 Full 復原模式時,您可以執行還原到任何時點,但交易記錄會成長較快。根據預設,當交易記錄檔已滿,SQL Server 會增加檔案大小以新增更多可用空間好寫入更多交易,並暫停資料庫上的所有活動,直到作業結束為止。SQL Server 每個記錄檔的成長是以本身的檔案大小上限檔案成長設定為基礎。

當檔案已達到本身的大小上限且無法成長,系統會發出 9002 錯誤並將資料庫設成唯讀模式。如果檔案可以成長,SQL Server 可以擴增檔案大小並用光可用空間。檔案成長的預設值為記錄檔目前大小的 10%。對效能來說,這不是好的預設值,因為您的檔案變得越大,就需要更長時間來建立新的可用空間。

最佳做法:排程定期進行交易記錄的備份。

不論大小上限和成長設定如何,排程定期進行交易記錄備份,根據預設會截斷舊的記錄項目,並讓系統重新使用現有的檔案空間。這項簡單的維護工作可以協助避免尖峰流量時刻的效能下降。

虛擬記錄檔最佳化

最佳做法:監控虛擬記錄檔成長,並採取行動防止記錄檔過於分散。

實體交易記錄檔會分散存放在虛擬記錄檔 (VLF) 中。每次實際交易記錄檔必須成長時都會建立新的 VLF。如果您未停用自動成長,而成長發生得太頻繁,就會建立太多 VLF。這項活動可能導致記錄檔過於分散,和磁碟分散存放類似,對效能會有不良影響。

SQL Server 2014 推出較有效率的演算法,可判斷在自動成長期間會建立多少 VLF。通常如果成長小於目前記錄檔大小的 1/8,則 SQL Server 會在新區段中建立一個 VLF。以前的做法會為 64 MB 和 1 GB 之間的成長建立 8 個 VLF,超過 1 GB 的成長則建立 16 個 VLF。您可以使用以下的 TSQL 指令碼檢查資料庫目前有多少 VLF。如果有數千個檔案,請考慮手動縮減和重新調整記錄檔大小。

--Check VLFs substitute your database name below
USE 
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

如要進一步瞭解 VLF,請前往 Brent Ozar 的網站

避免索引過於分散

最佳做法:定期為您最常修改的資料表重組索引。

資料表中的索引可能變得過於分散,導致任何使用這些索引的查詢效能不佳。定期維護排程應包括重新整理最常修改資料表的索引。您可以為資料庫執行下列 T-SQL 指令碼,以顯示其中的索引和相關分散百分比。以下範例結果顯示 PK_STOCK 索引為 95% 分散。請在下列「SELECT」陳述式中,將「[YOUR_DB]」替換成您資料庫的名稱。

SELECT stats.index_id as id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'[YOUR_DB]'), NULL, NULL, NULL, NULL) AS stats
    JOIN sys.indexes AS indx ON stats.object_id = indx.object_id
      AND stats.index_id = indx.index_id AND name IS NOT NULL;

RESULTS
-------------------------------
Id    name          avg_fragmentation_in_percent
-------------------------------
1 ORDERS_I1 0
2 ORDERS_I2 0
1 ORDER_LINE_I1 0.01
1 PK_STOCK95.5529819557039
1 PK_WAREHOUSE0.8

當您的索引太過分散,可以用簡單的 ALTER 指令碼加以重新整理。以下的程式碼範例會輸出您可為每個資料表索引執行的 ALTER 陳述式。

SELECT
'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE;
GO'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'

從結果集中選擇分散程度最高的資料表,然後以漸進方式執行這些陳述式。請考慮為這項工作建立排程,或在定期維護工作中加入類似的指令碼。

備份

最佳做法:制定備份計劃並定期執行。

要瞭解如何實作健全的備份和維護計劃,Ola Hallengren 的網站提供很好的入門指南。

當進行定期資料庫備份時,小心不要耗用太多永久磁碟 IOPS。請使用本機 SSD 存放備份,然後推到 Cloud Storage 值區。

監控

最佳做法:使用 Stackdriver Monitoring。

您可以安裝 Microsoft Windows 適用的測試版 Stackdriver Monitoring 代理程式,傳送數個監控資料點給 Stackdriver 系統。

只要使用資料收集功能,您就可以微調想要監控的資訊,並傳送這項資訊到內建管理資料倉儲。管理資料倉儲可以在您監控的同一伺服器上執行,或可將資料串流傳送到執行倉儲的另一個 SQL Server 執行個體。

大量載入資料

最佳做法:在將大量資料移到實際工作環境的伺服器之前,先使用另外的資料庫存放和轉換這些資料。

即使您不需定期將大量資料載入系統,可能偶爾也會有這方面的需要。這是需要大量資源的作業,您可能會在進行大量載入時到達永久磁碟 IOPS 限制

有一個簡單的方法可以縮減大量載入作業的磁碟 IO 和 CPU 耗用量,並帶來加速執行批次工作的好處。這裡的解決方案是建立完全分開且採用 Simple 復原模式的資料庫,然後使用這個資料庫來存放和轉換大量資料集,之後才將大量資料集插入實際工作環境的資料庫。如果本機 SSD 硬碟空間足夠,您還可以將這個新的資料庫放在本機 SSD。這可以減少大量作業的資源耗用,並加速完成工作。最後的好處是,您對實際工作環境資料的備份工作不必包含交易記錄的所有大批作業,因此工作較少且執行較快。

驗證設定

最佳做法:測試您的設定以驗證設定發揮預期的作用。

您每次設定新系統時都應規劃驗證設定,並執行一些效能測試。這個預存程序是很適合評估 SQL Server 設定的資源。稍後請花點時間參閱設定標記,然後執行程序。

SQL Server Enterprise Edition 最佳化

SQL Server Enterprise Edition 比 Standard Edition 新增許多功能。如果您將現有授權遷移到 GCP,可以考慮實作一些效能選項。

使用壓縮資料表

最佳做法:啟用資料表和索引壓縮。

壓縮資料表可以讓系統執行變快,似乎是違反一般直覺的說法,但在大多數情況下的確如此。交換條件是使用少量的 CPU 週期來壓縮資料,並消除讀寫大型區塊所需的額外磁碟 IO。一般來說,系統使用的磁碟 IO 越少,系統效能就會越好。估計和啟用資料表和索引壓縮的操作說明請參考 MSDN 網站

啟用緩衝集區延伸

最佳做法:使用緩衝集區延伸功能來加速存取資料。

緩衝集區是系統儲存「乾淨分頁」的地方;簡單的說,就是儲存磁碟鏡像資料的地方。當記憶體中的資料變更了,就稱為「中途分頁」,必須推送到磁碟中以儲存變更。當您的資料庫比可用的記憶體大時,會對緩衝集區造成壓力,且可能捨棄乾淨分頁。乾淨分頁捨棄之後,系統下次要存取已捨棄的資料時,必須從磁碟讀取。

緩衝集區延伸功能讓您能將乾淨分頁推送到本機 SSD,而非加以捨棄。這種運作方式和虛擬記憶體相同,經由「互換」讓您在本機 SSD 上存取乾淨分頁,這會比一般磁碟擷取資料快。

這項技術不會像擁有足夠記憶體一樣快,但當您的可用記憶體不足時,還是能加快一些處理速度。如要進一步瞭解緩衝集區延伸功能及查看一些基準化結果,請前往 Brent Ozar 的網站

後續步驟

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
Compute Engine 說明文件