DMVs for Memory

Today we will look into DMVs related to memory

First we will look into sys.dm_os_sys_memory which will give us memory related basic informatio for the system
This will give us detail for physical memory, available memory , toatl page file and available page file and
high/low memory status. If  memory status is low then it means system is facing memory pressure.

SELECT total_physical_memory_kb / ( 1024.0 * 1024 )     total_physical_memory_gb,

       available_physical_memory_kb / ( 1024.0 * 1024 ) available_physical_memory_gb,

       total_page_file_kb / ( 1024.0 * 1024 )           total_page_file_gb,

       available_page_file_kb / ( 1024.0 * 1024 )       available_page_file_gb,

       system_high_memory_signal_state,

       system_low_memory_signal_state,

       system_memory_state_desc

FROM   sys.dm_os_sys_memory

dm_os_process_memory
This dmv is supprted on SQL 2008 and later versions
it provides some  more detailed information about memory in sql server
All details are in kb

SELECT physical_memory_in_use_kb,

       large_page_allocations_kb,

       locked_page_allocations_kb,

       total_virtual_address_space_kb,

       virtual_address_space_reserved_kb,

       virtual_address_space_committed_kb,

       virtual_address_space_available_kb,

       page_fault_count,

       memory_utilization_percentage,

       available_commit_limit_kb,

       process_physical_memory_low,

       process_virtual_memory_low

FROM   sys.dm_os_process_memory

This will give us physical memory , locked page allocation (should be 0 if AWE is not enabled) ,
total , reserved, committed and available virtual address space, page fault count , memory utilization percentage and process physical memory low if the server is facing memory pressure

Server details using DMV

Sometimes we need basic server  details , we can get this information from below DMV

SELECT *

FROM   sys.dm_os_sys_info

It provides valuable miscellaneous  hardware information which includes  number of logical cpu and hyperthread ratio for servers. Also provides information for physical memory , virtual memory  , buffer pool , sql server start time . In one of my previous article I have discussed how to get SQL server start time from tempdb
But from here we can get more accurate detail about sql server start time .

SELECT cpu_count                                         logical_cpu,

       hyperthread_ratio,

       cpu_count / hyperthread_ratio                     physcial_cpu,

       physical_memory_in_bytes / ( 1024 * 1024 * 1024 ) memory_in_gb,

       virtual_memory_in_bytes / ( 1024 * 1024 * 1024 )  virtual_memory_in_gb,

       bpool_committed                                   buffer_pool_committed,

       bpool_commit_target                               buffer_pool_commit_target,

       bpool_visible                                     buffer_pool_visible,

       sqlserver_start_time

FROM   sys.dm_os_sys_info

get row count for a table SQL Server DMVs

In SQL 2000 you can get this informartion from sysindexes system table.
But we should not sysindexes in because it will be deprecated in future

DMVs instead of DBCC Opentran

Those who are using SQL Server must be aware of dbcc opentran
After 2005 edition , SQL Server  is providing reach information in DMV.
For transactions 2 DMVs are available in SQL Server