是什麼導致此查詢/執行計劃的 CPU 使用率高？

我有一個支持 .NET Core API 應用程序的 Azure SQL 數據庫。瀏覽 Azure 門戶中的性能概覽報告表明，我的數據庫伺服器上的大部分負載（DTU 使用）來自 CPU，並且一個查詢具體是：

正如我們所見，查詢 3780 負責伺服器上幾乎所有的 CPU 使用率。

這在某種程度上是有道理的，因為查詢 3780（見下文）基本上是應用程序的整個關鍵，並且經常被使用者呼叫。這也是一個相當複雜的查詢，需要許多連接才能獲得所需的正確數據集。查詢來自一個 sproc，它最終看起來像這樣：

-- @UserId UNIQUEIDENTIFIER

SELECT
   C.[Id],
   C.[UserId],
   C.[OrganizationId],
   C.[Type],
   C.[Data],
   C.[Attachments],
   C.[CreationDate],
   C.[RevisionDate],
   CASE
       WHEN
           @UserId IS NULL
           OR C.[Favorites] IS NULL
           OR JSON_VALUE(C.[Favorites], CONCAT('$."', @UserId, '"')) IS NULL
       THEN 0
       ELSE 1
   END [Favorite],
   CASE
       WHEN
           @UserId IS NULL
           OR C.[Folders] IS NULL
       THEN NULL
       ELSE TRY_CONVERT(UNIQUEIDENTIFIER, JSON_VALUE(C.[Folders], CONCAT('$."', @UserId, '"')))
   END [FolderId],
   CASE 
       WHEN C.[UserId] IS NOT NULL OR OU.[AccessAll] = 1 OR CU.[ReadOnly] = 0 OR G.[AccessAll] = 1 OR CG.[ReadOnly] = 0 THEN 1
       ELSE 0
   END [Edit],
   CASE 
       WHEN C.[UserId] IS NULL AND O.[UseTotp] = 1 THEN 1
       ELSE 0
   END [OrganizationUseTotp]
FROM
   [dbo].[Cipher] C
LEFT JOIN
   [dbo].[Organization] O ON C.[UserId] IS NULL AND O.[Id] = C.[OrganizationId]
LEFT JOIN
   [dbo].[OrganizationUser] OU ON OU.[OrganizationId] = O.[Id] AND OU.[UserId] = @UserId
LEFT JOIN
   [dbo].[CollectionCipher] CC ON C.[UserId] IS NULL AND OU.[AccessAll] = 0 AND CC.[CipherId] = C.[Id]
LEFT JOIN
   [dbo].[CollectionUser] CU ON CU.[CollectionId] = CC.[CollectionId] AND CU.[OrganizationUserId] = OU.[Id]
LEFT JOIN
   [dbo].[GroupUser] GU ON C.[UserId] IS NULL AND CU.[CollectionId] IS NULL AND OU.[AccessAll] = 0 AND GU.[OrganizationUserId] = OU.[Id]
LEFT JOIN
   [dbo].[Group] G ON G.[Id] = GU.[GroupId]
LEFT JOIN
   [dbo].[CollectionGroup] CG ON G.[AccessAll] = 0 AND CG.[CollectionId] = CC.[CollectionId] AND CG.[GroupId] = GU.[GroupId]
WHERE
   C.[UserId] = @UserId
   OR (
       C.[UserId] IS NULL
       AND OU.[Status] = 2
       AND O.[Enabled] = 1
       AND (
           OU.[AccessAll] = 1
           OR CU.[CollectionId] IS NOT NULL
           OR G.[AccessAll] = 1
           OR CG.[CollectionId] IS NOT NULL
       )
)

如果您關心，可以在此處的 GitHub 上找到此數據庫的完整原始碼。來自上述查詢的來源：

• https://github.com/bitwarden/core/blob/master/src/Sql/dbo/Stored%20Procedures/CipherDetails_ReadByUserId.sql
• https://github.com/bitwarden/core/blob/master/src/Sql/dbo/Functions/UserCipherDetails.sql
• https://github.com/bitwarden/core/blob/master/src/Sql/dbo/Functions/CipherDetails.sql

在這幾個月裡，我花了一些時間在這個查詢上，盡我所知調整執行計劃，最終得到它的目前狀態。具有此執行計劃的查詢在數百萬行（< 1 秒）中速度很快，但如上所述，隨著應用程序大小的增長，正在消耗越來越多的伺服器 CPU。

我在下面附上了實際的查詢計劃（不確定是否有任何其他方式可以在堆棧交換中共享它），它顯示了在生產中針對約 400 個結果的返回數據集執行儲存過程。

我希望澄清以下幾點：

• Index Seek on[IX_Cipher_UserId_Type_IncludeAll]佔計劃總成本的 57%。我對計劃的理解是這個成本與IO有關，這使得Cipher表包含數百萬條記錄。但是，Azure SQL 性能報告顯示我的問題源於此查詢的 CPU，而不是 IO，所以我不確定這是否真的是一個問題。另外，它已經在這裡進行了索引搜尋，所以我不確定是否有任何改進的餘地。
• 來自所有連接的雜湊匹配操作似乎是計劃中顯示顯著 CPU 使用率的原因（我認為？），但我不確定如何才能做得更好。我需要如何獲取數據的複雜性質需要跨多個表進行大量連接。ON如果可能的話，我已經在它們的子句中短路了許多這些連接（基於先前連接的結果） 。

在此處下載完整的執行計劃：https ://www.dropbox.com/s/lua1awsc0uz1lo9/CipherDetails_ReadByUserId.sqlplan?dl=0

我覺得我可以從此查詢中獲得更好的 CPU 性能，但我正處於不確定如何進一步調整執行計劃的階段。還可以進行哪些其他優化來降低 CPU 負載？執行計劃中的哪些操作是 CPU 使用率最差的？

您可以在 SQL Server Management Studio 中查看操作員級別的 CPU 和執行時間指標，但我不能說它們對於像您的查詢一樣快完成的查詢有多可靠。您的計劃只有行模式運算符，因此時間指標適用於該運算符以及其下方子樹中的運算符。以嵌套循環連接為例，SQL Server 告訴您整個子樹佔用了 60 毫秒的 CPU 時間和 80 毫秒的執行時間：

大部分子樹時間都花在索引查找上。索引查找也佔用 CPU。看起來您的索引恰好具有所需的列，因此不清楚如何降低該運算符的 CPU 成本。除了尋求之外，計劃中的大部分 CPU 時間都花在了實現連接的雜湊匹配上。

這是一個巨大的過度簡化，但這些雜湊連接佔用的 CPU 將取決於雜湊表的輸入大小和探測端處理的行數。觀察有關此查詢計劃的一些事情：

• 最多 461 個返回的行有C.[UserId] = @UserId. 這些行根本不關心連接。
• 對於確實需要連接的行，SQL Server 無法提前應用任何過濾（除了OU.[UserId] = @UserId）。
• 過濾器在查詢計劃的末尾（從右到左讀取）附近消除了幾乎所有已處理的行：[vault].[dbo].[Cipher].[UserId] as [C].[UserId]=[@UserId] OR ([vault].[dbo].[OrganizationUser].[AccessAll] as [OU].[AccessAll]=(1) OR [vault].[dbo].[CollectionUser].[CollectionId] as [CU].[CollectionId] IS NOT NULL OR [vault].[dbo].[Group].[AccessAll] as [G].[AccessAll]=(1) OR [vault].[dbo].[CollectionGroup].[CollectionId] as [CG].[CollectionId] IS NOT NULL) AND [vault].[dbo].[Cipher].[UserId] as [C].[UserId] IS NULL AND [vault].[dbo].[OrganizationUser].[Status] as [OU].[Status]=(2) AND [vault].[dbo].[Organization].[Enabled] as [O].[Enabled]=(1)

將您的查詢寫成UNION ALL. 前半部分UNION ALL可以包括行 where C.[UserId] = @UserId，後半部分可以包括行 where C.[UserId] IS NULL。您已經在進行兩次索引[dbo].[Cipher]搜尋（一次@UserId用於 NULL，一次用於 NULL），因此UNION ALL版本似乎不太可能變慢。分別寫出查詢將允許您在建構和探測方面儘早進行一些過濾。如果查詢需要處理較少的中間數據，查詢會更快。

我不知道您的 SQL Server 版本是否支持此功能，但如果這沒有幫助，您可以嘗試在查詢中添加列儲存索引，以使您的雜湊連接符合批處理模式。我首選的方法是創建一個帶有 CCI 的空表，然後左連接到該表。與行模式相比，散列連接在批處理模式下執行時效率更高。

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