Whats new with SQL 2008 July CTP - new DMVs for memory management
As this is the 2nd instalment of public CTP of SQL Server 2008 we will be expecting more information on 'whats new' and 'whats not (deprecated)'. For me (majority of you too) having handful information with DMVs is essential, in this regad we have 5 new DMVs available with this CTP, here they are:
sys.dm_os_memory_brokers
This DMV is helpful to find the difference between the process memory and internal counters from non-SQL Server components. This is purely based upon the tracking allocation of memory computation. To be specific when you execute this DMV concentrate on the values on the memory_broker_type as explained with BOL:
| Value |
Description |
|
MEMORYBROKER_FOR_CACHE |
Memory that is allocated for use by cached objects. |
|
MEMORYBROKER_FOR_STEAL |
Memory that is stolen from the buffer pool. This memory is not available for reuse by other components until it is freed by the current owner. |
|
MEMORYBROKER_FOR_RESERVE |
Memory reserved for future use by currently executing requests. |
I will blog further about this DMV once I've setup the environment with this July CTP.
sys.dm_os_memory_nodes
This DMV elevates the informaton from the memory nodes that are created by NUMA (non-uniform memory access) nodes. This is useful to find virtual address space for reserved and commited bytes in the memory.
sys.dm_os_nodes
This DMV is useful to get the information on the node state that uses SQL OS which is a layer between SQL Server and Windows where it is possible to gain visibility into how SQL Server uses Windows memory, the CPU, schedulers, tasks, I/O, disk drives and so on. By understanding how resources are being used, you can better tune SQL Server for workloads. The node state values are displayed with the mutually exclusive values first, followed by the combinable values. For example: Online, Thread Resources Low, Lazy Preemptive
sys.dm_os_process_memory
This DMV can be used to get memory allocations and during the performance hit by looking at memory_utilization_percentage & page_fault_count values are useful to see how memory is resourced. Further BOL returns that All returned values that indicate memory sizes are shown in kilobytes (KB). The column total_virtual_address_space_reserved_kb is a duplicate of virtual_memory_in_bytes from sys.dm_os_sys_info.
sys.dm_os_sys_memory
Another high level view to return complete information on memory that is responded to memory condition from the operating system within the limits of available physical memory. This is most helpful DMV to get overall system state which is an important part of evaluating SQL Server memory usage. Concentrate on system_memory_state_desc value during the performance monitoring.