Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
add comment

@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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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