2

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?

4

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.

3

@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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.