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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
@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)', 'varchar(max)') as [Type], record.value('(/Record/ResourceMonitor/IndicatorsProcess)', 'int') as [IndicatorsProcess], record.value('(/Record/ResourceMonitor/IndicatorsSystem)', 'int') as [IndicatorsSystem], record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)', 'bigint') AS [Avail Phys Mem, Kb], record.value('(/Record/MemoryRecord/TotalPhysicalMemory)', 'bigint') AS [Total Phys Mem, Kb], record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)', 'bigint') AS [Avail VAS, Kb], record.value('(/Record/MemoryRecord/TotalVirtualAddressSpace)', '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;