Sql-Server

操作員在溢出級別 2 的執行期間使用 tempdb 溢出數據

  • January 22, 2020

我正在努力使用警告Operator usedtempdb最小化查詢計劃的排序操作成本to spill data during execution with spill level 2

我發現了幾篇與溢出級別 1 執行期間的溢出數據相關的文章，但不是級別 2。級別 1 似乎是由於統計數據過時造成的，那麼級別 2 呢？我找不到任何與level 2.

我發現這篇與排序警告相關的文章非常有趣：

永遠不要忽略 SQL Server 中的排序警告

我的 Sql 伺服器？

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 2016 年 6 月 17 日 19:14:09 版權所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows NT 6.3 (Build 9600:) (Hypervisor)

我的硬體？

執行以下查詢以查找硬體：

– 來自 SQL Server 2012 的硬體資訊

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_kb/1024 AS [Physical Memory (MB)], affinity_type_desc, 
virtual_machine_type_desc, sqlserver_start_time
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

在此處輸入圖像描述

目前分配的記憶體

SELECT
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;

在此處輸入圖像描述

當我在一年範圍內執行查詢時，我沒有收到任何警告，如下圖所示：

在此處輸入圖像描述

但是，當我只執行 1 天範圍時，我會收到以下警告on the sort operator：

在此處輸入圖像描述

這是查詢：

   DECLARE @FromDate SMALLDATETIME = '19-OCT-2016 11:00'
   DECLARE @ToDate   SMALLDATETIME = '20-OCT-2016 12:00'




   SELECT      DISTINCT
               a.strAccountCode ,
               a.strAddressLine6 ,
               a.strPostalCode ,
               CASE    WHEN a.strCountryCode IN ('91','92') THEN 'GB-Int'
                       ELSE a.strCountryCode
                       END AS [strCountryCode]
   FROM        Bocss2.dbo.tblBAccountParticipant AS ap
   INNER JOIN  Bocss2.dbo.tblBAccountParticipantAddress AS apa ON ap.lngParticipantID = apa.lngParticipantID
                                                               AND apa.sintAddressTypeID = 2
   INNER JOIN  Bocss2.dbo.tblBAccountHolder AS ah ON ap.lngParticipantID = ah.lngParticipantID
   INNER JOIN  Bocss2.dbo.tblBAddress AS a ON apa.lngAddressID = a.lngAddressID
                                           AND a.blnIsCurrent = 1
   INNER JOIN  Bocss2.dbo.tblBOrder AS o ON ap.lngParticipantID = o.lngAccountParticipantID
                                       AND o.sdtmOrdCreated >= @FromDate
                                       AND o.sdtmOrdCreated < @ToDate

OPTION(RECOMPILE)

查詢計劃在這裡

使用 pastetheplan 的查詢計劃

問題：1）在查詢計劃中我看到了這個：

StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70"

為什麼是70？我正在使用 sql server 2014

2）我如何擺脫那個排序運算符（如果可能的話）？

  1. 我看到頁面壽命預期非常低，除了向該伺服器添加更多記憶體之外，還有什麼其他的東西我可以看看我是否可以阻止這個警告？

乾杯

在 Shanky 和 ​​Paul White 的回答之後更新

我已經根據下面的腳本檢查了我的統計數據，它們似乎都是正確和更新的。

這些都是此查詢中使用的所有索引和表。

DBCC SHOW_STATISTICS ('dbo.tblBAddress','IDXF_tblBAddress_lngAddressID__INC')
GO
DBCC SHOW_STATISTICS  ('dbo.tblBOrder','IX_tblBOrder_sdtmOrdCreated_INCL')
GO
DBCC SHOW_STATISTICS ('dbo.tblBAccountHolder','PK_tblAccountHolder')
GO
DBCC SHOW_STATISTICS ('dbo.tblBAccountParticipant','PK_tblBAccountParticipants')
GO
DBCC SHOW_STATISTICS ('dbo.tblBAccountParticipantAddress','IDXF_tblBAccountParticipantAddress_lngParticipantID')
GO

這是我得到的回報：

在此處輸入圖像描述

在此處輸入圖像描述

這是部分結果，但我已全部重新訪問。

對於統計更新，我目前有Ola Hallengren

索引優化作業 - 計劃每週執行一次 - 星期日

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'USER_DATABASES,-%Archive', 
@Indexes = 'ALL_INDEXES' , 
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@PageCountLevel=1000,
@StatisticsSample =100
,@UpdateStatistics = 'Index', 
@OnlyModifiedStatistics = 'Y',
@TimeLimit=10800, 
@LogToTable = 'Y'

儘管統計資訊似乎已更新，但在我執行以下腳本後，我不再收到有關排序運算符的警告。

UPDATE STATISTICS [Bocss2].[dbo].[tblBOrder]  WITH FULLSCAN
--1 hour  04 min 14 sec

UPDATE STATISTICS [Bocss2].[dbo].tblBAddress  WITH FULLSCAN
-- 45 min 29 sec

UPDATE STATISTICS  [Bocss2].[dbo].tblBAccountHolder WITH FULLSCAN
-- 26 SEC

UPDATE STATISTICS  [Bocss2].[dbo].tblBAccountParticipant WITH FULLSCAN
-- 4 min

UPDATE STATISTICS  [Bocss2].[dbo].tblBAccountParticipantAddress WITH FULLSCAN
-- 7 min 3 sec

2級呢？我找不到與 2 級相關的任何內容。

根據這個 Old MS Doc，Tempdb 溢出中的數字表示需要對數據進行多少次傳遞才能對數據進行排序。所以 Spill 1 意味著它必須經過 1 次才能對數據進行排序，2 意味著它必須經過 2 次。

引用部落格：

如果涉及排序操作的查詢生成溢出級別值為 2 的排序警告事件類，則查詢的性能可能會受到影響，因為需要多次傳遞數據才能對數據進行排序。在下面的範例中，我們看到溢出級別值為 1，這意味著通過數據一次就足以完成排序。

為什麼是70？我正在使用 sql server 2014

這是因為圖片中數據庫的兼容級別不是 120（表示 2014 數據庫的兼容級別），因為它不是 120，查詢將使用舊的基數估計 (CE) 模型進行處理，稱為CardinalityEstimationModelVersion="70". 我相信您知道從 SQL Server 2014 開始我們有了新的 CE。

我如何擺脫那個排序運算符（如果可能的話）？

您正在使用的 distinct 命令導致排序操作。正在排序的數據不適合記憶體，因此它會溢出到 tempdb，當這種情況發生時，執行計劃中會給出帶有黃色驚嘆號的排序警告。排序警告並不總是一個問題。

您可以在執行計劃中看到，估計要排序的行數為 1，但在執行時遇到了 16,353 行。為排序保留的記憶體量基於輸入的預期（估計）大小，並且在執行期間不能增長（在這種情況下）。

查詢的小記憶體授權 (1632KB) 也在並發執行的記憶體消耗運算符（排序和*“優化”循環連接）之間共享。在您的計劃中，這意味著 33.33% (544KB) 在讀取行時可用於排序（輸入記憶體部分）。這沒有足夠的記憶體來對 16,353 行進行排序，因此它會溢出到tempdb*。單級溢出不足以完成排序，因此需要第二級溢出（有關溢出級別的更多詳細資訊，請參閱末尾的參考資料）。

排序屬性

對在 SQL Sentry Plan Explorer 中查看的屬性進行排序

更新統計數據可能有助於解決基數估計問題。您可能會遇到升序鍵問題，尤其是在 table 上tblBOrder。從該表中選擇一個帶有您問題的文字日期的簡單選擇，現在可能會估計一行。

我已經看到頁面壽命預期非常低，除了向該伺服器添加更多記憶體之外，還有什麼其他的東西我可以看看我是否可以防止這個警告？

PLE 是 I/O 活動量的指示，它是否增加了？因此，這種情況經常發生還是僅在您執行某些查詢時發生或僅在今天發生。避免膝跳反應，首先我們需要確保您確實面臨記憶體壓力或一些產生過多 I/O 的惡意查詢導致這種情況。無論如何，您已經為 SQL Server 分配了 97 G 記憶體。

有關溢出水平和上升關鍵問題的更多資訊，請參閱：

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

相關問答

Sql-Server

左外連接 - 查詢計劃中的排序操作 - 調整這個簡單查詢的任何方法？

  • February 20, 2017
檢視問答
Sql-Server

如何僅添加最後一天的數據來更新統計資訊？

  • October 25, 2016
檢視問答
Sql-Server

SQL 連接的性能

  • March 23, 2022
檢視問答
Sql-Server

如果 CTE 在查詢中定義並且從未使用過，它會發出聲音嗎？

  • December 3, 2021
檢視問答
Sql-Server

TOP 如何（以及為什麼）影響執行計劃？

  • November 27, 2021
檢視問答
Sql-Server

非聚集索引更新未顯示在執行計劃中

  • August 12, 2021
檢視問答