DBCC FREESYSTEMCACHE ('ALL'); 
DBCC FREEPROCCACHE; 

-- 查看耗時且占用高的會話（為啥限制大于50是因為2005之前系統(tǒng)會話ID都小于等于50）
SELECT session_id, status, command, wait_type 
FROM sys.dm_exec_requests 
WHERE session_id > 50;
-- 殺掉會話（替換 SPID）注意一定不要誤殺 有些屬于系統(tǒng)會話（比如寫日志、清理）
KILL 56; 

  -- 查看數(shù)據(jù)庫內(nèi)存使用
   SELECT 
       (physical_memory_in_use_kb / 1024) AS SQL_Server_Used_Memory_MB,
       (locked_page_allocations_kb / 1024) AS SQL_Server_Locked_Pages_MB,
       (total_virtual_address_space_kb / 1024) AS Total_Virtual_Address_Space_MB,
       process_physical_memory_low,
       process_virtual_memory_low
   FROM sys.dm_os_process_memory;
   --查看服務(wù)器內(nèi)存使用
     SELECT 
       (total_physical_memory_kb / 1024) AS Total_OS_Memory_MB,
       (available_physical_memory_kb / 1024) AS Available_OS_Memory_MB,
       system_memory_state_desc
   FROM sys.dm_os_sys_memory;

-- 按數(shù)據(jù)庫統(tǒng)計內(nèi)存占用
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) * 8/1024 AS CacheSize_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id)
ORDER BY CacheSize_MB DESC;
GO