You can use the sys.dm_os_memory_clerks dynamic management view (DMV) to get detailed information about memory allocation by the server components in SQL Server 2005 and 2008. Some of the DMVs provide similar data as the DBCC MEMORYSTATUS command, but their output is much more programmer friendly.

You can also use the following DMVs for memory troubleshooting both in SQL Server 2005 and 2008:

•sys.dm_exec_cached_plans

•sys.dm_exec_query_memory_grants

•sys.dm_exec_query_resource_semaphores

•sys.dm_exec_requests

•sys.dm_exec_sessions

•sys.dm_os_memory_cache_entries

There are several new DMVs in SQL Server 2008 which make us easier to gather memory diagnosis information. Below is a summary of these new DMVs for memory troubleshooting:

•sys.dm_os_memory_brokers provides information about memory allocations using the internal SQL Server memory manager. The information provided can be useful in determining very large memory consumers.

•sys.dm_os_memory_nodes and sys.dm_os_memory_node_access_stats provide summary information of the memory allocations per memory node and node access statistics grouped by the type of the page. This information can be used instead of running DBCC MEMORYSTATUS to quickly obtain summary memory usage. (sys.dm_os_memory_node_access_stats is populated under dynamic trace flag 842 due to its performance impact.)

•sys.dm_os_nodes provides information about CPU node configuration for SQL Server. This DMV also reflects software NUMA (soft-NUMA) configuration.

•sys.dm_os_sys_memory returns the system memory information. The ‘Available physical memory is low’ value in the system_memory_state_desc column is a sign of external memory pressure that requires further analysis.