Sql-Server

計劃記憶體大小和保留記憶體

  • March 27, 2020

執行包含實際執行計劃的查詢時，根運算符 ( SELECT) 告訴我記憶體計劃大小為 32KB。

一個連接sys.dm_exec_cached_plansand的查詢sys.dm_os_memory_objects，查看有問題的計劃，表示pages_in_bytesand的值為max_pages_in_bytes32768 (32KB)，與記憶體的計劃大小相匹配。

我不明白的sys.dm_exec_cached_plans.size_in_bytes是 49152 (48KB) 中的值代表什麼。我已經閱讀了所有這些專欄的 BOL，尤其是size_in_bytes其中說：

“記憶體對象消耗的字節數。 "

我無法理解最後一點拼圖，以了解它的真正含義。

我知道所有操作員（不是說用於排序和散列的額外記憶體授權）都需要一定數量的固定記憶體來儲存狀態、進行計算等，這些記憶體與優化計劃一起儲存在記憶體中，但是在哪裡呢？

所以，我的問題是：

  • size_in_bytes真正的意思是什麼
  • 為什麼它比“記憶體計劃大小”更高？
  • 為所有運算符/迭代器保留的固定記憶體量在哪裡，是“記憶體計劃大小”（在我的範例中為 32Kb）還是其他任何地方？

我知道它們是具有不同功能的不同 DMV，但它們是相關的。sys.dm_exec_cached_plans列連接sys.dm_os_memory_objects中的已編譯（記憶體）計劃memory_object_address。我在這裡發布問題的原因是我正在尋求幫助，了解如何解釋 DMV 及其列。

如果size_in_bytes是記憶體計劃大小，為什麼 SQL Server 在實際執行計劃中說另一個值？

新查詢，新號碼：

  • 實際計劃

    • 記憶體計劃大小 16KB
    • 編譯記憶體 96KB

  • 車管所：

    • sys.dm_exec_cached_plans.size_in_bytes24KB
    • sys.dm_os_memory_objects.pages_in_bytes, .max_pages_in_bytes16KB。

另外，請注意，此查詢不需要任何額外的記憶體授予來進行排序和散列操作。

微軟 SQL Server 2012 - 11.0.5343.0 (X64)

DMV的size_in_bytes欄位sys.dm_exec_cached_plans，至少就“編譯計劃”而言，大於 XML 計劃中節點的CachedPlanSize屬性的QueryPlan原因是因為編譯計劃與查詢計劃不同。編譯計劃由多個記憶體對象組成，其組合大小等於size_in_bytes欄位。因此，您在文件中找到的“*記憶體對象消耗的字節數”的描述是準確的；*只是根據 DMV 的名稱很容易誤解“記憶體對象”的含義，並且“計劃”一詞具有多種含義。

編譯計劃是一個容器，其中包含與查詢批處理相關的各種資訊（即不僅僅是一條語句），其中一個（或多個）是查詢計劃。編譯計劃有一個頂層記憶體對象MEMOBJ_COMPILE_ADHOCsys.dm_os_memory_objects ，它是通過memory_object_address兩個 DMV 中的欄位連結的行。此記憶體對象包含符號表、參數集合、相關對象的連結、訪問器記憶體、TDS 元數據記憶體以及可能的其他一些項目。編譯的計劃在使用相同會話設置執行相同批次的會話/使用者之間共享。但是，某些相關對像不會在會話/使用者之間共享。

編譯計劃還具有一個或多個依賴對象，可以通過將plan_handle(in sys.dm_exec_cached_plans) 傳遞到sys.dm_exec_cached_plan_dependent_objectsDMF 來找到這些對象。依賴對像有兩種類型：可執行計劃（記憶體對象 = MEMOBJ_EXECUTE）和游標（記憶體對象 = MEMOBJ_CURSOREXEC）。將有 0 個或多個 Cursor 對象，每個游標一個。還會有一個或多個 Executable Plan 對象，每個執行同一批次的使用者一個，因此 Executable Plans不是使用者之間共享。可執行計劃包含執行時參數和局部變數資訊、執行時狀態（例如目前執行的語句）、在執行時創建的對象的對象 ID（我假設這指的是表變數、臨時表、臨時儲存過程等） ，以及可能的其他項目。

多語句批處理中的每個語句都包含在編譯語句（記憶體對象 = MEMOBJ_STATEMENT）中。每個編譯語句的大小（即pages_in_bytes）除以 1024 應該與XML 計劃中節點的CachedPlanSize="xx"值相匹配。<QueryPlan>編譯語句通常會有一個（可能更多？）關聯的執行時查詢計劃（記憶體對象 = MEMOBJ_XSTMT）。最後，對於作為查詢的每個執行時查詢計劃，都應該有一個關聯的查詢執行上下文（記憶體對象 = MEMOBJ_QUERYEXECCNTTXFORSE）。

關於編譯語句，單語句批處理沒有單獨的編譯語句（即MEMOBJ_STATEMENT）或單獨的執行時查詢計劃（即MEMOBJ_XSTMT）對象。這些對像中的每一個的值都將儲存在主編譯計劃對象（即MEMOBJ_COMPILE_ADHOC）中，在這種情況下，該pages_in_bytes主對象的值除以 1024 應該與XML 計劃節點中的CachedPlanSize大小匹配。<QueryPlan>但是，在多語句批處理中，這些值將不相等。

該size_in_bytes值可以通過sys.dm_os_memory_objects匯總 DMV 中的條目（上面以粗體標出的項目）得出，所有這些條目都與dm_os_memory_objects.page_allocator_address該編譯計劃相關。獲取正確值的技巧是首先獲取特定編譯計劃的memory_object_addressfrom ，然後使用它根據其欄位從中獲取相應的MEMOBJ_COMPILE_ADHOC行。然後，從該行中獲取值，並使用它來獲取具有相同值的所有行。（請注意，此技術不適用於其他記憶體對像類型：Parse Tree、Extended Proc、CLR Compiled Proc和CLR Compiled Funcsys.dm_exec_cached_plans``sys.dm_os_memory_objects``memory_object_address``page_allocator_address``sys.dm_os_memory_objects``sys.dm_os_memory_objects``page_allocator_address.)

使用從memory_object_address獲得的值sys.dm_exec_cached_plans，您可以通過以下查詢查看已編譯計劃的所有組件：

DECLARE @CompiledPlanAddress VARBINARY(8) = 0x00000001DC4A4060;

SELECT obj.memory_object_address, obj.pages_in_bytes, obj.type
FROM   sys.dm_os_memory_objects obj
WHERE  obj.page_allocator_address = (
                              SELECT planobj.page_allocator_address
                              FROM   sys.dm_os_memory_objects planobj
                              WHERE  planobj.memory_object_address = @CompiledPlanAddress
                             )
ORDER BY obj.[type], obj.pages_in_bytes;

下面的查詢列出了所有已編譯的計劃sys.dm_exec_cached_plans以及每個批次的查詢計劃和語句。上面的查詢通過 XML 作為MemoryObjects欄位合併到下面的查詢中：

SELECT cplan.bucketid,
      cplan.pool_id,
      cplan.refcounts,
      cplan.usecounts,
      cplan.size_in_bytes,
      cplan.memory_object_address,
      cplan.cacheobjtype,
      cplan.objtype,
      cplan.plan_handle,
      '---' AS [---],
      qrypln.[query_plan],
      sqltxt.[text],
      '---' AS [---],
      planobj.pages_in_bytes,
      planobj.pages_in_bytes / 1024 AS [BaseSingleStatementPlanKB],
      '===' AS [===],
      cplan.size_in_bytes AS [TotalPlanBytes],
      bytes.AllocatedBytes,
      (SELECT CONVERT(VARCHAR(30), obj.memory_object_address, 1)
              AS [memory_object_address], obj.pages_in_bytes, obj.[type]
              --,obj.page_size_in_bytes
       FROM   sys.dm_os_memory_objects obj
       WHERE  obj.page_allocator_address = planobj.page_allocator_address
       FOR XML RAW(N'object'), ROOT(N'memory_objects'), TYPE) AS [MemoryObjects]
FROM   sys.dm_exec_cached_plans cplan
OUTER APPLY sys.dm_exec_sql_text(cplan.[plan_handle]) sqltxt
OUTER APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qrypln
INNER JOIN sys.dm_os_memory_objects planobj
       ON planobj.memory_object_address = cplan.memory_object_address
OUTER APPLY (SELECT SUM(domo.[pages_in_bytes]) AS [AllocatedBytes]
            FROM   sys.dm_os_memory_objects domo
            WHERE  domo.page_allocator_address = planobj.page_allocator_address) bytes
WHERE  cplan.parent_plan_handle IS NULL
AND    cplan.cacheobjtype IN (N'Compiled Plan', N'Compiled Plan Stub')
--AND cplan.plan_handle = 0x06000D0031CD572910529CE001000000xxxxxxxx
ORDER BY cplan.objtype, cplan.plan_handle;

請注意：

  • 該TotalPlanBytes欄位只是對該欄位的重新陳述sys.dm_exec_cached_plans.size_in_bytes，
  • 該AllocatedBytes欄位是通常匹配的相關記憶體對象的總和TotalPlanBytes（即size_in_bytes）
  • 由於執行期間記憶體消耗增加，該AllocatedBytes欄位偶爾會大於TotalPlanBytes(ie )。size_in_bytes這似乎主要是由於重新編譯而發生的（這在顯示的usecounts欄位中應該很明顯1）
  • 該BaseSingleStatementPlanKB欄位應匹配XMLCachedPlanSize中節點的屬性，但僅在使用單個查詢批處理時。QueryPlan
  • 對於具有多個查詢的批次，應該有標記為MEMOBJ_STATEMENTin 的行sys.dm_os_memory_objects，每個查詢一個。這些行的pages_in_bytes欄位應匹配<QueryPlan>XML 計劃的各個節點。

資源：

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

相關問答

Sql-Server

SQL Server 使用的 RAM 超出了應有的範圍

  • January 7, 2022
檢視問答
Sql-Server

高 CPU 和計劃記憶體不穩定

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

數據庫未分配空間 - 正確的測量方法

  • May 22, 2021
檢視問答
Sql-Server

為 SQLHANDLE 檢測到可能的無限重新編譯

  • April 10, 2021
檢視問答
Sql-Server

sys.dm_db_index_physical_stats 非常慢

  • December 4, 2020
檢視問答
Sql-Server

SQL Server 記憶體

  • October 31, 2020
檢視問答