Checking SQL Server's memory utilization

Question

Is there a way to check how much of the memory reserved by SQL Server is actually being used by SQL Server?

Is there a way to look at this information in the past? I.e. how much memory was being used an hour ago?

Answer 1

You can run DBCC MEMORYSTATUS at any time (or probably better to look at the sys.dm_os_memory* DMVs).

As for getting the data from an hour ago, that would require some kind of collection using a monitoring tool, MDW, etc. SQL Server doesn't keep snapshots of memory utilization for all of time - that would get pretty expensive.

Answer 2

@AaronBertrand is correct. You can't really get that sort of granular data. You can look at the ring buffer to see if you can possible find any statistics of interest to you, but these are event-driven and are not necessarily filterable to the time precision that you're seeking. I've only slightly modified Jonathan Kehayias' Memory Pressure query. It may or may not be of use to you, but, as I noted, Aaron's answer is correct.

SELECT 
 EventTime, 
 record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') as [Type],
 record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') as [IndicatorsProcess],
 record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') as [IndicatorsSystem],
 record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [Avail Phys Mem, Kb],
 record.value('(/Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [Total Phys Mem, Kb],
 record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [Avail VAS, Kb],
 record.value('(/Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [Avail VAS, Kb]
FROM (
 SELECT
 DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,
 CONVERT (xml, record) AS record
 FROM sys.dm_os_ring_buffers
 CROSS JOIN sys.dm_os_sys_info
 WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS tab
ORDER BY EventTime DESC;