我們生產 SQL Server 的主要性能問題，我將如何解決這個問題？

這個問題基本上是這個問題的後續問題：

Strange performance problem with SQL Server 2016

我們現在使用這個系統進行了生產。儘管自從我上一篇文章以來，這個 SQL Server 中添加了另一個應用程序數據庫。

這些是系統統計資訊：

• 128 GB RAM（SQL Server 最大記憶體 110 GB）
• 4 核 @2.6 GHz
• 10 GBit 網路連接
• 所有儲存都是基於 SSD 的
• 程序文件、日誌文件、數據庫文件和 tempdb 位於伺服器的不同分區上
• 視窗伺服器 2012 R2
• VMware 版本 HPE-ESXi-6.0.0-Update3-iso-600.9.7.0.17
• VMware Tools 版本 10.0.9，內部版本 3917699
• Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 版權所有 (c) Microsoft Corporation Standard Edition (64-bit) o​​n Windows Server 2012 R2 Standard 6.3 (Build 9600:) （管理程序）

我們的系統現在存在主要的性能問題。非常高的 CPU 使用率和執行緒數：

等待活動監視器的統計資訊（我知道它不是很可靠）

sp_blitzfirst 的結果：

sp_configure 的結果：

高級伺服器設置（不幸的是只有德語）

MAXDOP 設置由我更改。

我知道這可能不是 SQL Server 本身的問題。它可能是虛擬化（vmware）、網路相關（我已經測試過）或應用程序本身的問題。我只是想進一步確定它。

高 ASYNC_NETWORK_IO 會導致 sqlserver 程序的執行緒數高嗎？我想它會產生許多工人，因為執行緒無法關閉。那正確嗎？

我將提供您需要的任何其他資訊。提前感謝您的支持！

編輯：

的結果sp_Blitz @OutputType = ‘markdown’, @CheckServerInfo = 1

優先級 1：備份：

• 備份到數據庫所在的同一驅動器 - 過去兩週在驅動器 E:\ 上完成了 5 次備份，數據庫文件也位於該驅動器上。如果該陣列發生故障，這將帶來嚴重的風險。

優先級 1：可靠性：

• 超過 2 週的最後一個良好的 DBCC CHECKDB

  • babtec_prod - 上次成功的 CHECKDB：2017-08-20 00:01:01.513
  • D3PR - 上次成功的 CHECKDB：從不。
  • DEMO77 - 上次成功的 CHECKDB：2016-02-23 20:31:38.590
  • FINP - 上次成功的 CHECKDB：2017-04-23 22:01:19.133
  • GridVis_EnMs - 上次成功的 CHECKDB：2017-05-18 22:10:48.120
  • master - 上次成功的 CHECKDB：從不。
  • 模型
  • 數據庫
  • PROD77 - 上次成功的 CHECKDB：2016-02-23 21:33:24.343

優先級 10：性能：

• 查詢儲存已禁用 - 此數據庫上尚未啟用新的 SQL Server 2016 查詢儲存功能。

  • babtec_prod
  • D3PR
  • 展示77
  • 芬普
  • GridVis_EnMs

優先級 50：DBCC 事件：

• DBCC DROPCLEANBUFFERS - 使用者 schorsch 在 2017 年 9 月 21 日上午 11:57 和 2017 年 9 月 21 日上午 11:57 之間執行了 DBCC DROPCLEANBUFFERS 1 次。如果這是一個生產盒，請知道發生這種情況時您正在清除記憶體中的所有數據。什麼樣的怪物會這樣做？
• DBCC SHRINK% - 使用者 schorsch 在 2017 年 9 月 21 日晚上 11:51 和 2017 年 4 月 9:02 之間執行文件收縮 6 次。那麼，呃，他們是試圖修復腐敗，還是導致腐敗？
• 總體事件 - 在 2017 年 9 月 19 日下午 1:40 到 2017 年 4 月 3:20 之間發生了 287 場 DBCC 事件。這不包括 CHECKDB 和其他通常良性的 DBCC 事件。

優先級 50：性能：

• 文件增長緩慢 PROD77 - 2 個增長每個花費超過 15 秒。考慮將文件自動增長設置為較小的增量。

優先級 50：可靠性：

• 頁面驗證不是最優的 babtec_prod - 數據庫$$ babtec_prod $$具有用於頁面驗證的 TORN_PAGE_DETECTION。SQL Server 可能更難辨識儲存損壞並從中恢復。考慮改用 CHECKSUM。

優先級 100：性能：

• 一個查詢的多個計劃 - 計劃記憶體中的單個查詢存在 3576 個計劃 - 這意味著我們可能存在參數化問題。

優先級 110：性能：

• 沒有聚集索引的活動表

  • babtec_prod - 的

  $$ babtec_prod $$數據庫有堆 - 沒有聚集索引的表 - 正在被主動查詢。

  • D3PR - 的

  $$ D3PR $$數據庫有堆 - 沒有聚集索引的表 - 正在被主動查詢。

  • DEMO77 - 的

  $$ DEMO77 $$數據庫有堆 - 沒有聚集索引的表 - 正在被主動查詢。

  • FINP–

  $$ FINP $$數據庫有堆 - 沒有聚集索引的表 - 正在被主動查詢。

  • GridVis_EnMs - 的

  $$ GridVis_EnMs $$數據庫有堆 - 沒有聚集索引的表 - 正在被主動查詢。

  • PROD77 - 的

  $$ PROD77 $$數據庫有堆 - 沒有聚集索引的表 - 正在被主動查詢。

優先級 150：性能：

• 外鍵不受信任

  • babtec_prod - 的

  $$ babtec_prod $$數據庫具有可能被禁用的外鍵，數據已更改，然後再次啟用該鍵。僅僅啟用該鍵不足以讓優化器使用該鍵——我們必須使用 WITH CHECK CHECK CONSTRAINT 參數來更改表。

  • D3PR - 的

  $$ D3PR $$數據庫具有可能被禁用的外鍵，數據已更改，然後再次啟用該鍵。僅僅啟用該鍵不足以讓優化器使用該鍵——我們必須使用 WITH CHECK CHECK CONSTRAINT 參數來更改表。

• 沒有聚集索引的非活動表

  • D3PR - 的

  $$ D3PR $$數據庫有堆 - 沒有聚集索引的表 - 自上次重新啟動以來沒有被查詢過。這些可能是不小心留下的備份表。

  • GridVis_EnMs - 的

  $$ GridVis_EnMs $$數據庫有堆 - 沒有聚集索引的表 - 自上次重新啟動以來沒有被查詢過。這些可能是不小心留下的備份表。

• 表 babtec_prod 上的觸發器 -

$$ babtec_prod $$數據庫有 26 個觸發器。

優先級 170：文件配置：

• C盤系統數據庫

  • master - master 數據庫在 C 驅動器上有一個文件。將系統數據庫放在 C 驅動器上可能會在伺服器空間不足時導致伺服器崩潰。
  • 模型 - 模型數據庫在 C 驅動器上有一個文件。將系統數據庫放在 C 驅動器上可能會在伺服器空間不足時導致伺服器崩潰。
  • msdb - msdb 數據庫在 C 驅動器上有一個文件。將系統數據庫放在 C 驅動器上可能會在伺服器空間不足時導致伺服器崩潰。

優先級 170：可靠性：

• 最大文件大小集

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_data_01 的最大文件大小設置為 61440MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_data_idx_01 的最大文件大小設置為 61440MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_firm_01 的最大文件大小設置為 61440MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_firm_idx_01 的最大文件大小設置為 61440MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_log_01 的最大文件大小設置為 61440MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_phys_01 的最大文件大小設置為 61440MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_phys_idx_01 的最大文件大小設置為 61440MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_sys_01 的最大文件大小設置為 20480MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_usr_01 的最大文件大小設置為 20480MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_wort_01 的最大文件大小設置為 20480MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

  • D3PR - 的

  $$ D3PR $$數據庫文件 d3_wort_idx_01 的最大文件大小設置為 20480MB。如果空間不足，即使有可用的驅動器空間，數據庫也會停止工作。

優先級 200：資訊：

• 備份壓縮預設關閉 - 最近發生了未壓縮的完整備份，並且未在伺服器級別打開備份壓縮。備份壓縮包含在 SQL Server 2008R2 及更高版本中，即使在標準版中也是如此。我們建議預設打開備份壓縮，以便壓縮臨時備份。

• 排序規則是 Latin1_General_CS_AS FINP - 使用者數據庫和 tempdb 之間的排序規則差異可能會導致衝突，尤其是在比較字元串值時

• 排序規則是 SQL_Latin1_General_CP1_CI_AS - 使用者數據庫和 tempdb 之間的排序規則差異可能會導致衝突，尤其是在比較字元串值時

  • 展示77
  • 產品77

• 已配置連結伺服器 - BWIN2\INFOR 配置為連結伺服器。在與 sa 連接時檢查其安全配置，因為任何查詢它的使用者都將獲得管理員級別的權限。

優先級 200：監控：

• 沒有失敗電子郵件的代理作業

  • 作業 syspolicy_purge_history 尚未設置為在失敗時通知操作員。
  • 作業 upd_durchpreis_monatl 尚未設置為在失敗時通知操作員。
  • 作業 upd_fertmengen_woche 尚未設置為在失敗時通知操作員。
  • 作業 upd_liegezeit_monatl 尚未設置為在失敗時通知操作員。
  • 作業 upd_vertreter_diff 尚未設置為在失敗時通知操作員。
  • 作業 UPDATE_CONNECT_IK 尚未設置為在失敗時通知操作員。
  • 作業 Wartung.Cleanup 尚未設置為在失敗時通知操作員。
  • 作業 Wartung.DBCC Check DB 尚未設置為在失敗時通知操作員。
  • 作業 Wartung.Index neu erstellen 尚未設置為在失敗時通知操作員。
  • 作業 Wartung.Statistiken aktualisieren 尚未設置為在失敗時通知操作員。
  • 作業 Wartung.Transactionlog Backup 尚未設置為在失敗時通知操作員。
  • 作業 Wartung.Vollbackup SystemDB 尚未設置為在失敗時通知操作員。
  • 作業 Wartung.Vollbackup UserDB 尚未設置為在失敗時通知操作員。

• No Alerts for Corruption - SQL Server 代理不存在錯誤 823、824 和 825 的警報。這三個錯誤可以通知您有關早期硬體故障的通知。啟用它們可以防止你很多心碎。

• Sev 19-25 沒有警報 - 嚴重級別 19 到 25 的 SQL Server 代理警報不存在。這些是一些非常嚴重的 SQL Server 錯誤。知道這些正在發生可能會讓您更快地從錯誤中恢復。

• 未配置所有警報 - 未配置所有 SQL Server 代理警報。這是一種免費、簡單的方法，可以在監控系統發現之前就收到有關損壞、作業失敗或重大中斷的通知。

優先級 200：非預設伺服器配置：

• 代理 XP - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。
• 數據庫郵件 XP - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。
• 預設全文語言 - 此 sp_configure 選項已更改。它的預設值為 1033，並且已設置為 1031。
• 預設語言 - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。
• 文件流訪問級別 - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。
• max degree of parallelism - 此 sp_configure 選項已更改。它的預設值為 0，已設置為 4。
• 最大伺服器記憶體 (MB) - 此 sp_configure 選項已更改。它的預設值為 2147483647，已設置為 115000。
• min server memory (MB) - 此 sp_configure 選項已更改。它的預設值為 0，已設置為 10000。
• 遠端管理員連接 - 此 sp_configure 選項已更改。它的預設值為 0，並已設置為 1。

優先級 200：性能：

• 並行度的成本門檻值 - 設置為 5，它的預設值。更改此 sp_configure 設置可能會減少 CXPACKET 等待。
• Snapshot Backups Occurring - 在過去兩週內發生了 9 次看起來像快照的備份，這表明 IO 可能正在凍結。

優先級 210：非預設數據庫配置：

• Read Committed Snapshot Isolation Enabled - 此數據庫設置不是預設設置。

  • D3PR
  • 芬普

• 啟用遞歸觸發器 - 此數據庫設置不是預設設置。

  • 展示77
  • 產品77

• 啟用快照隔離 FINP - 此數據庫設置不是預設設置。

優先級 240：等待統計：

• 1 - ASYNC_NETWORK_IO - 225.9 小時等待，每小時平均等待時間 143.5 分鐘，0.2% 信號等待，2146022 個等待任務，平均等待時間 378.9 毫秒。
• 2 - CXPACKET - 43.1 小時等待，27.4 分鐘平均每小時等待時間，1.5% 信號等待，32608391 個等待任務，4.8 毫秒平均等待時間。

優先級 250：資訊：

• SQL Server 在 NT 服務帳戶下執行

  • 我作為 NT Service\MSSQL$INFOR 執行。我希望我有一個 Active Directory 服務帳戶。
  • 我作為 NT Service\SQLAgent$INFOR 執行。我希望我有一個 Active Directory 服務帳戶。

優先級 250：伺服器資訊：

• 預設跟踪內容 - 預設跟踪在 2017 年 9 月 3 日晚上 8:34 和 2017 年 5 月 12:50 之間保存 760 小時的數據。預設跟踪文件位於：C:\Program Files\Microsoft SQL Server\MSSQL13.INFOR\MSSQL\Log

• 驅動器 C 空間 - C 驅動器上有 21308.00MB 可用空間

• 驅動器 D 空間 - D 驅動器上有 280008.00MB 可用空間

• 驅動器 E 空間 - E 驅動器上有 281618.00MB 可用空間

• 驅動器 F 空間 - F 驅動器上有 60193.00MB 可用空間

• 硬體 - 邏輯處理器：4 個。物理記憶體：128GB。

• 硬體-NUMA 配置-節點：0 狀態：ONLINE 線上調度程序：4 離線調度程序：0 處理器組：0 記憶體節點：0 記憶體 VAS 保留 GB：281

• 伺服器上次重啟 - 2017 年 1 月 1 日下午 2:21

• 伺服器名稱 - BWINPDB\INFOR

• 服務

  • 服務：SQL Server (INFOR) 在服務帳戶 NT Service\MSSQL$INFOR 下執行。上次啟動時間：Okt 1 2017 2:22PM。啟動類型：自動，目前正在執行。
  • 服務：SQL Server-Agent (INFOR) 在服務帳戶 NT Service\SQLAgent$INFOR 下執行。上次啟動時間：未顯示。啟動類型：自動，目前正在執行。

• SQL Server 上次重啟 - Okt 1 2017 下午 2:22

• SQL Server 服務 - 版本：13.0.4001.0。更新檔級別：SP1。版本：標準版（64 位）。AlwaysOn 啟用：0。 AlwaysOn Mgr 狀態：2

• 虛擬伺服器 - 類型：（HYPERVISOR）

• Windows 版本 - 您正在執行一個非常現代的 Windows 版本：Server 2012R2 時代，版本 6.3

優先級 254：執行日期：

• 船長的日誌：給某事和某事約會……

編輯：

我已經研究過關於使用 vmware 設置 sql server 的最佳實踐指南，並且我們已經根據本文設置了大部分內容。但是，未啟動超執行緒，並且 NUMA 在 vmware 主機上未啟動。SQL Server 雖然設置為 NUMA。

編輯：

在將並行度的門檻值設置為 50 後，我發出了 RECONFIGURE，我的 MAXDOP 設置也沒有配置。

我還諮詢了我們的 vmware 管理員，好像我被誤導了。我們的 CPU 設置為 2.6GHz 而不是 4.6GHz。我已經更正了上面的資訊。

編輯：

我們嘗試根據這個vmwarekb和指南設置一些網路相關。我們還向 VM 添加了 4 個核心。CPU 使用率保持不變。

回答我自己的問題。ASYNC_NETWORK_IO 實際上並不是真正的問題。我們按照本指南針對延遲敏感的工作負載解決了性能問題：

vSphere VM 中延遲敏感型工作負載的性能調整最佳實踐

我在這裡用黃色標記了我們應用於系統的設置：

我認為影響最大的設置是numa 配置和將延遲敏感度設置為high。這兩者都需要為 VM 顯式分配/保留物理 CPU 核心和 RAM。

我們還向 VM 添加了更多核心，現在需要將我們的 SQL Server 許可證從 Standard 升級到 Enterprise。

正如您上次問這個問題時所討論的那樣，您的首要等待是 ASYNC_NETWORK_IO。SQL Server 正在等待管道另一端的機器消化下一行查詢結果。

我從 sp_Blitz 的等待統計結果中獲得了這些資訊（感謝您將其粘貼）：

1 - ASYNC_NETWORK_IO - 225.9 小時等待，每小時平均等待時間 143.5 分鐘，0.2% 信號等待，2146022 個等待任務，平均等待時間 378.9 毫秒。

不要對 CPU 執行緒進行故障排除 - 這不相關。專注於您的主要等待類型以及可能導致該等待類型的事情。

要進一步解決此問題，請執行sp_WhoIsActive或sp_BlitzFirst（免責聲明：我是其中的作者之一） - 這兩者都會列出目前正在執行的查詢。查看等待資訊列，找到等待 ASYNC_NETWORK_IO 的查詢，並查看它們正在執行的應用程序和伺服器。

從那裡，您可以嘗試：

• 檢查這些應用程序伺服器是否功率不足（例如它們是否在 CPU 上達到極限，或分頁到磁碟）並調整它們
• 與應用程序開發人員合作，查看他們是否正在對結果進行逐行處理（例如對於從 SQL Server 返回的每一行，應用程序會關閉並在請求下一行結果之前進行一些處理）
• 與應用程序開發人員合作以選擇更少的數據（如果他們不需要所有數據，例如更少的行或更少的列 - 有時當人們不小心執行 SELECT * 並帶回比他們需要的更多數據時，您會看到這種情況，或者他們要求當他們只需要前 1000 個時的所有行）

使用 sp_WhoIsActive 進行更新- 在您發布的 sp_WhoIsActive 螢幕截圖中，您有幾個查詢正在 ASYNC_NETWORK_IO 上等待。對於這些，請參閱上述說明。

在其餘的查詢中，查看 sp_WhoIsActive 的“狀態”列 - 其中大多數是“睡眠”。這意味著他們根本沒有工作——他們正在等待管道另一端的應用程序發送他們的下一個命令。它們打開了事務（請參閱“open_tran_count”列），但 SQL Server 無法加快休眠事務的速度。這些查詢已經打開了四十多分鐘（sp_WhoIsActive 中的第一列。*他們只是不再做任何事情。*你必須讓這些人送出他們的事務並關閉他們的連接。這不是性能調整問題。

我們在這裡看到的一切都指向我們正在等待應用程序的場景。

引用自：https://dba.stackexchange.com/questions/187747