【SQLServer】SQLServer内存使用查看

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

?

1 2 3 4 5 6

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

　　

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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之前的版本可能不支持)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

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

##执行查看

?

1 2 3 4

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

　　

 

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