question

Glenn Berry of SQLskills keeps a nice collection of DMVs that most of us use. Within those scripts are the memory scripts you seek. [dmv-queries]( http://www.sqlskills.com/blogs/glenn/category/dmv-queries/)

There are many ways to get this. As mentioned by Tim, Glenn Berry's scripts is the good way to start: However I will share some what I've been trying to get the same: 1) You can Look for the performance counters( Perfmon). Look for the counters like Total server memory and target server memory counters.will give you the size of the buffer pool cache, which is a subset of the total memory being used by SQL Server. you can use below code to get this info:(Ps note :Be aware that Total Server Memory is NOT how much memory SQL Server is currently using) SELECT object_name, counter_name, cntr_value AS 'Total Server Memory (KB)' FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)' 2) Also, Look for the memory you have set in MAX and MIn server memory settings in Server memory option tab of server properties in sql server. Its generally believed SQL will take whatever has been assigned to it in SQL server properties box. 3) DBCC MEMORYSTATUS is one very useful command i use to figure out the memory usage: Refer [here][1] to know more about it. 4) per [MSDN][2] you can also use below to find current usage SELECT (physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB, (locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB, (total_virtual_address_space_kb/1024) AS Total_VAS_in_MB, process_physical_memory_low, process_virtual_memory_low FROM sys.dm_os_process_memory; [1]: https://support.microsoft.com/en-us/kb/271624?wa=wsignin1.0 [2]: https://msdn.microsoft.com/en-us/library/ms176018.aspx?f=255&MSPPError=-2147217396

You can also query the ring buffers to understand exactly when, or if, you're running out of memory on the OS or within SQL Server. I wrote it up in [an article][1]. One note, if you're running 2008R2 the query illustrated won't work and will need to be adjusted (the ring buffers are still there). [1]: https://www.simple-talk.com/sql/database-administration/why-is-that-sql-server-instance-under-stress/