【SQLServer】SQLServer内存使用查看

SQLServer查看每个数据库占用的内存

SELECT DB_NAME(database_id),
COUNT (1) * 8 / 1024 AS MBUsed
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
GO

　　

数据库中每个对象使用的内存大小

SELECT obj.name [Object Name], o.type_desc [Object Type],
i.name [Index Name], i.type_desc [Index Type],
COUNT(*) AS [Cached Pages Count],
COUNT(*)/128 AS [Cached Pages In MB]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name, object_id
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name, object_id
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
INNER JOIN sys.indexes i ON obj.[object_id] = i.[object_id]
INNER JOIN sys.objects o ON obj.[object_id] = o.[object_id]
WHERE database_id = DB_ID()
GROUP BY obj.name, i.type_desc, o.type_desc,i.name
ORDER BY [Cached Pages In MB] DESC;

　　

可以创建以下函数来查看内存的使用(SQL Server 2012之前的版本可能不支持)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.fn_CheckSQLMemory()
RETURNS @Sql_MemStatus TABLE 
 (
   SQLServer_Start_DateTime datetime, 
   SQL_current_Memory_usage_mb int,
   SQL_Max_Memory_target_mb int,
   OS_Total_Memory_mb int,
   OS_Available_Memory_mb int)
AS
BEGIN
   declare @strtSQL datetime
   declare @currmem int
   declare @smaxmem int
   declare @osmaxmm int
   declare @osavlmm int 
 
   -- SQL memory
   SELECT 
      @strtSQL = sqlserver_start_time,
      @currmem = (committed_kb/1024),
      @smaxmem = (committed_target_kb/1024)           
   FROM sys.dm_os_sys_info;
   
   --OS memory
   SELECT 
      @osmaxmm = (total_physical_memory_kb/1024),
      @osavlmm = (available_physical_memory_kb/1024) 
   FROM sys.dm_os_sys_memory;
   
   INSERT INTO @Sql_MemStatus values (@strtSQL, @currmem, @smaxmem, @osmaxmm, @osavlmm)
 
   RETURN 
END
GO 

##执行查看

USE master 
GO 
select * from dbo.fn_CheckSQLMemory()
GO 

　　

https://www.mssqltips.com/sqlservertip/6833/sql-server-memory-usage-query/