SQL Server 機器上的“正在使用”記憶體

我有一個具有 64GB RAM 的 SQL Server 2012 數據庫伺服器。一個名為 DataPumpService 的程序在同一個機器上執行，並從 2700 個平面文件中載入大約 4 億條記錄。

我想了解是什麼程序消耗了盒子上的所有物理記憶體，所以我轉到資源管理器（在任務管理器下）：

從螢幕截圖中可以看出，In Use 記憶體約為 61GB，但兩個最佔用記憶體的程序的 Committed Bytes 和 Private Bytes 約為 3.3GB。

如何找出導致此伺服器上如此高的 In Use 記憶體的程序？

謝謝。

SQL Server 正在消耗所有這些記憶體。您看不到它正在消耗的記憶體，因為 SQL Server 可能正在使用不屬於伺服器工作集的“鎖定頁面”，而這通常是您在任務管理器中看到的所有內容。

在 MSDN 部落格之一上有詳細的說明。

我們應該找出哪個數據庫正在使用大量記憶體，以及它是執行計劃還是實際的緩衝池。您是否為 SQL Server 設置了最大伺服器記憶體設置？如果沒有，你應該。

從此部落格中查看此查詢

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
  FROM sys.dm_os_performance_counters 
  WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
  AND counter_name = 'Total Pages';

;WITH src AS
(
  SELECT 
      database_id, db_buffer_pages = COUNT_BIG(*)
      FROM sys.dm_os_buffer_descriptors
      --WHERE database_id BETWEEN 5 AND 32766
      GROUP BY database_id
)
SELECT
  [db_name] = CASE [database_id] WHEN 32767 
      THEN 'Resource DB' 
      ELSE DB_NAME([database_id]) END,
  db_buffer_pages,
  db_buffer_MB = db_buffer_pages / 128,
  db_buffer_percent = CONVERT(DECIMAL(6,3), 
      db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

這將為您分解每個數據庫。特定的數據庫是否突出？他們中的任何一個使用比實際數據庫大小更多的記憶體嗎？

然後查看Glenn Barry 的優秀部落格，了解與記憶體相關的查詢，例如這個。發布結果，這樣我們就可以看到 SQL 中是什麼佔用了你的記憶體：

-- SQL Server 2008 and R2 Memory Related Queries
-- Glenn Berry 
-- October 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Instance Level queries

-- Good basic information about memory amounts and state (SQL 2008 and 2008 R2)
SELECT total_physical_memory_kb, available_physical_memory_kb, 
      total_page_file_kb, available_page_file_kb, 
      system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- You want to see "Available physical memory is high"


-- SQL Server Process Address space info (SQL 2008 and 2008 R2)
--(shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb,locked_page_allocations_kb, 
      page_fault_count, memory_utilization_percentage, 
      available_commit_limit_kb, process_physical_memory_low, 
      process_virtual_memory_low
FROM sys.dm_os_process_memory;

-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low


-- Page Life Expectancy (PLE) value for default instance (SQL 2005, 2008 and 2008 R2)
SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = N'SQLServer:Buffer Manager' -- Modify this if you have named instances
AND counter_name = N'Page life expectancy';

-- PLE is a good measurement of memory pressure.
-- Higher PLE is better. Below 300 is generally bad.
-- Watch the trend, not the absolute value.


-- Get total buffer usage by database for current instance (SQL 2005, 2008 and 2008 R2)
-- Note: This is a fairly expensive query
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;

-- Helps determine which databases are using the most memory on an instance


-- Memory Clerk Usage for instance
-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
-- (SQL 2005, 2008 and 2008 R2)
SELECT TOP(20) [type], [name], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY [type], [name]  
ORDER BY SUM(single_pages_kb) DESC;

-- CACHESTORE_SQLCP  SQL Plans         These are cached SQL statements or batches that aren't in 
--                                     stored procedures, functions and triggers
-- CACHESTORE_OBJCP  Object Plans      These are compiled plans for stored procedures, 
--                                     functions and triggers
-- CACHESTORE_PHDR   Algebrizer Trees  An algebrizer tree is the parsed SQL text that 
--                                     resolves the table and column names


-- Find single-use, ad-hoc queries that are bloating the plan cache
SELECT TOP(100) [text], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cp.cacheobjtype = N'Compiled Plan' 
AND cp.objtype = N'Adhoc' 
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

-- Gives you the text and size of single-use ad-hoc queries that waste space in the plan cache
-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and 2008 R2 only)
-- Enabling forced parameterization for the database can help, but test first!


-- Database level queries (switch to your database)
--USE YourDatabaseName;
--GO

-- Breaks down buffers used by current database by object (table, index) in the buffer cache
-- (SQL 2008 and 2008 R2) Note: This is a fairly expensive query
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], 
p.index_id, COUNT(*)/128 AS [Buffer size(MB)],  COUNT(*) AS [BufferCount], 
p.data_compression_desc AS [CompressionType]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = CONVERT(int,DB_ID())
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id, p.data_compression_desc
ORDER BY [BufferCount] DESC;


-- Top Cached SPs By Total Logical Reads (SQL 2008 and 2008 R2). Logical reads relate to memory pressure
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], 
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], 
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;

-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure

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