SQL Server 執行個體最佳做法

您可以採用多項最佳做法,讓執行 Microsoft SQL Server 的 Compute Engine 執行個體發揮最大效能。如要瞭解如何設定高效能 SQL Server 執行個體,請參閱建立高效能 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 永久磁碟來存放記錄檔和資料檔案。

根據預設,預先設定的 SQL Server 映像檔中會包含掛接為「C:」磁碟的開機永久磁碟中的所有已安裝項目。請考慮加裝第二個 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 週期來壓縮資料,並消除讀寫大型區塊所需的額外磁碟 I/O。一般來說,系統使用的磁碟 I/O 越少,系統的效能就會越好。如需評估及啟用資料表和索引壓縮功能的操作說明,請前往 MSDN 網站

啟用緩衝集區延伸

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

「緩衝集區」是系統儲存「乾淨分頁」的位置。簡單來說,「緩衝集區」就是儲存資料副本的位置,而其呈現結果與磁碟內部如出一轍。記憶體中的資料發生變更時即稱為「中途分頁」,您必須將中途分頁推送至磁碟才能儲存變更。如果您的資料庫大於可用記憶體容量,緩衝集區就會接收到壓力而有可能捨棄乾淨分頁。如果乾淨分頁遭到捨棄,系統下次需要存取已遭捨棄的資料時,就必須從磁碟讀取相關資料。

緩衝集區延伸功能可讓您將乾淨分頁推送至本機 SSD,這樣就不需要捨棄任何分頁。這項功能的運作方式與虛擬記憶體相同,藉由「互換」讓您在本機 SSD 中存取乾淨分頁,這樣會比前往一般磁碟擷取資料來得快。

儘管採用這項技術可享的速度仍然低於使用足夠的記憶體,不過如果您的可用記憶體容量偏低,還是能提高一些總處理量。如要進一步瞭解緩衝集區延伸功能及查看一些基準化結果,請前往 Brent Ozar 的網站

後續步驟

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

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

這個網頁
Compute Engine 說明文件