共計 3037 個字符，預計需要花費 8 分鐘才能閱讀完成。
如何進行數據庫性能調優，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
前言
微軟工程師的一個工程師曾經對性能調優有一個非常形象的比喻：剝洋蔥。我也非常認可，讓我們來一層一層撥開外面它神秘的面紗。
六大因素
下面祭出的是我們在給客戶分析數據庫性能問題最常用的圖。
看完這個圖，你是不是對性能調優有了個基本的概念了。通常來講，我們會依照下面的順序來進行分析：
硬件能力
系統規模
數據庫內部因素
軟件環境
這 4 個的順序可以有所調整或者交換，但是對于系統的性能優化一定要從全局出發。切勿一來就深入到某一個 SQL 語句的優化，因為可能你花費大量的時間把一個 SQL 從 20s 優化到 1s，但是整個系統的卡慢仍然存在。
實戰案例
不廢話了，開整開整，直接上干貨。
時間：2018 年 1 月某天
事件：某醫院客戶 下午 4 點 突然出現大面積的卡慢。整個系統出現嚴重問題，信息中心電話打爆，醫院工程師手足無措。
萬幸的是我們給數據庫裝了 lsquo; 攝像頭 rsquo;，下面就從監控錄像來看看發送了什么。然后加以解決。
硬件能力
CPU
在問題發生時間段內 CPU 使用率在 20% 以下，正常。
Memory
從下面的圖像顯示，內存使用正常。
頁生命周期
可用內存
IO
IO 隊列平均值很低，15.48 左右有個瞬時的高點，可留意這段時間有沒有批量的寫入。
總的來看，硬件資源是足夠的。
系統規模
問題發生時，每秒的批請求數并不是一個上升趨勢，反而有所下降。這是因為系統的擁堵，等待，影響了系統的吞吐量。
數據庫內部因素
等待
慢語句
從會話和慢語句的趨勢圖可以看到，問題發生的時間和客戶描述完全吻合，我們可以斷定本身事故的確是慢在數據庫。
什么導致的慢
檢查者個時間段運行中的語句，可以發現下午 15.58 左右，數據庫中開始出現越來越多的 CMEMTHREAD 等待。
一直到 1900 頁 16.08 分的時候，出現了 *** 達 100 個并發同時出現 CMEMTHREAD 等待。
什么是 CMEMTHREAD 等待
微軟官方的描述：在任務正在等待線程安全的內存對象時發生。當多個任務嘗試從同一個內存對象分配內存導致爭用時，等待時間可能會增加。
這個描述很晦澀，感覺還是完全不知道等待類型是怎么回事，應該怎么處理這類問題。
實際上，從官方描述來看是內存爭用的問題，但是實際上這個問題的關鍵在于多個任務的爭用，實際上是并發的執行的問題。
場景
出現在數據庫編譯或重編譯時，將即席執行計劃 ad hoc plans 插入到計劃緩存中的時候
NUMA 架構下，內存對象是按照節點來分區的
內存對象有三種類型的（Global，Per Numa Node，Per CPU）。SQL Server 將允許對內存對象進行分段，以便只有同一節點或 CPU 上的線程具有相同的底層 CMemObj，從而減少來自其他節點或 cpu 的線程交互，從而提高性能和可伸縮性。減少內存的并發爭用。
SELECT type, pages_in_bytes, CASE WHEN (0x20 = creation_options 0x20) THEN Global PMO. Cannot be partitioned by CPU/NUMA Node. TF 8048 not applicable. WHEN (0x40 = creation_options 0x40) THEN Partitioned by CPU.TF 8048 not applicable. WHEN (0x80 = creation_options 0x80) THEN Partitioned by Node. Use TF 8048 to further partition by CPU ELSE UNKNOWN END from sys.dm_os_memory_objects order by pages_in_bytes desc如果你發現，Partitioned by Node 的內存開銷是排在前面的，可以使用 TRACE FLAG 8048 來減少 CMEMTHREAD 等待。
從圖中可以看到，客戶的 Partitioned by Node 是比較靠后的，排在 14 位。
3. 補丁
這類場景是最常見的。如果在系統中發現出現大量的 CMEMTHREAD 等待，優先考慮數據庫是不是已經安裝 *** 的補丁。
2008 r2: FIX: SQL Server 2008 R2 performs poorly when most threads wait for the CMEMTHREAD wait type if the threads use table variables or temp tables to load or update rowsets
2012 ,2014 當您執行許多特殊查詢在 SQL Server 2012 年或 SQL Server 2014 CMEMTHREAD 等待。
軟硬件環境
目前數據庫的版本是 11.0.5556.0 而前面提到的補丁，安裝后的版本是：11.0.5623.0
代碼設計
是什么語句產生了等待。
都是類似下面的語句，*** 時，并發超過 100。
SELECT
* INTO #Tmp from TB where 1=2
特點如下：
1. 語句簡單 開銷都小于 5 不會產生并行。
2. 都采用了 select into #temptable 的形式。
就像上面分析的一樣，CMEMTHREAD 等待是一個并發問題，而不是一個內存問題。在其他方案行不通的時候，我們可以通過調整此類語句的寫法，減少 CMEMTHREAD 等待.
業務模型及架構
目前系統是單機運行的狀態，這其實是很少見的。存在少量 OLAP 和 OLTP 業務混合的情況。后續我們會給客戶規劃 讀寫分離 或者負載均衡的解決方案。
解決方案
安裝 *** 的補丁
至少需要安裝前面發的解決等待問題的 FIX。建議是直接安裝到目前為止 *** 的 2012 SP4 補丁。
修改參數
optimize for ad hoc workloads 從 0 修改為 1。針對將即席執行計劃 ad hoc plans 插入到計劃緩存中的時候場景，減少 ad hoc 查詢占用的內存。
增加 TEMPDB 數據文件的個數
select * into #temptable 會產生大量的閂鎖爭用，防止在 CMEMTHREAD 等待消除后，出現大量的 pagelatch 閂鎖爭用。我經歷過很多案例，解決了前面的一個擁堵之后，后面有產生了新的等待，導致性能更差了。請記住，優化是一個長期的，循序漸進的過程。
遷移 TEMPDB 數據文件的位置
目前部分 tempdb 文件放在 S，一般分放在 D 盤。建議都遷移到 S 盤 (存儲上面)，增加 tempdb 的響應速度。如果可能的話，使用 SSD 來 *** 化 tempdb 的性能，將會是不錯的選擇。
優化程序的代碼
修改代碼通常都是放在 *** 面的，因為要牽涉的情況比較多。前面的手段 80% 的情況下，都可以解決問題。剩下的 20%，我們需要，檢查程序中的邏輯，看看這些的語句都是什么業務產生的。什么條件會觸發這類業務. 對應下面類似的語句都使用存儲過程，或者參數化后的方式，減少編譯和重編譯的次數。另外此類語句都會并發創建臨時表，可能通過調整 tempdb 的設置，加快此類語句的執行速度，減少同一時間此類語句的并發數量。
優化效果
經過前面的幾個優化手段，第二天開始，沒有再出現過一次 CMEMTHREAD 的等待。
等待
慢語句
關于如何進行數據庫性能調優問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。
