question

half fast dba avatar image
half fast dba asked

How much physical Memory is SQL Server using?

If I had a simple query or set of queries for this it would be very useful. If you only have 'em for 64 bit systems, that is fine.
memory-utilization
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you literally copy and paste your question in Google, you'll find tons of information. :)
0 Likes 0 ·
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
0 Likes 0 ·
Tim avatar image
Tim answered
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/)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Those scripts are great.
0 Likes 0 ·
MAXKA avatar image
MAXKA answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.