I will be writing
this article in four parts because this topic is huge and the amount of
information can be overwhelming for readers and for me also. So we will be
exploring it step by step.
1.
DBCC
MemoryStatus
2.
Using DMV
3.
Alert and
perfmon counters
As you may know, Dynamic Management
Views were introduced in SQL Server 2005 and since then these are important
source of information for database health status. DMV can be used to monitor
SQL Server overall health, finding the root cause of performance bottlenecks
and tuning database performance.In this article we will be concentrating on
DMVs important for finding and resolving Memory issues. Since there are many
DMV for memory status and utilization, so we will be concentrating on the most
useful ones. You can explore other DMV by simply writing a select statement and
going through information provided by them.
sys.dm_os_sys_info
This provides the
information about the system and SQL Server resources and consumption. It gives
the overall data about physical and virtual memory, CPU count etc which can
help us to analyze the overall memory and CPU utilization. When I ran the
select query against sys.dm_os_sys_info, I got the following result for my
environment.
select
T.affinity_type,
T.affinity_type_desc,
T.committed_kb,
T.committed_target_kb,
T.cpu_count,
T.cpu_ticks,
T.deadlock_monitor_serial_number,
T.hyperthread_ratio,
T.max_workers_count,
T.ms_ticks,
T.os_error_mode,
T.os_priority_class,
T.os_quantum,
T.physical_memory_kb,
T.process_kernel_time_ms,
T.process_user_time_ms,
T.sqlserver_start_time,
T.sqlserver_start_time_ms_ticks
T.virtual_machine_type,
T.virtual_machine_type_desc,
T.virtual_memory_kb,
T.visible_target_kb
from sys.dm_os_sys_info T
cpu_count- it shows the number of
logical CPU in the system.
cpu_ticks- it shows the current tick
count for the CPU and this is read from processor's RDTSC counter.
ms_ticks- it shows the number of
milliseconds , since the system restarted.
physical_memory_kb- it shows the
total physical memory in the system.
virtual_memory_kb- it show shows the
total virtual address space available for user process.
max_workers_count- it shows maximum
number of worker threads that can be created.
sqlserver_start_time- it shows the
last time SQL Server was restarted.
sys.dm_os_memory_cache_counters
It provides the snapshot of the cache and this information can
be used to understand the health of cache in SQL Server instance. When I ran
the below script on my SQL Server instance, I got the following output. I am
showing only first few rows here.
select
T.cache_address,
T.entries_count,
T.entries_in_use_count,
T.name,
T.pages_in_use_kb,
T.pages_kb,
T.type
from sys.dm_os_memory_cache_counters
T
select
T.memory_node_id,
T.virtual_address_space_reserved_kb,
T.virtual_address_space_committed_kb,
T.locked_page_allocations_kb,
T.pages_kb,
T.shared_memory_committed_kb,
T.shared_memory_reserved_kb,
T.cpu_affinity_mask,
T.online_scheduler_mask,
T.processor_group,
T.foreign_committed_kb
from sys.dm_os_memory_nodes
T
select
T.cache_address,
T.name,
T.type,
T.entry_address,
T.entry_data_address,
T.in_use_count,
T.is_dirty,
T.disk_ios_count,
T.context_switches_count,
T.original_cost,
T.current_cost,
T.memory_object_address,
T.pages_kb,
T.entry_data,
T.pool_id,
T.time_to_generate,
T.use_count,
T.average_time_between_uses,
T.time_since_last_use,
T.probability_of_reuse,
T.value
from sys.dm_os_memory_cache_entries T
Object
Catalog Views
The
following object catalog views are used specifically with In-Memory OLTP.
Internal
DMVs
There
are additional DMVs that are intended for internal use only, and for which we
provide no direct documentation. In the area of memory-optimized tables,
undocumented DMVs include the following:
Ø sys.dm_xtp_threads
Ø sys.dm_xtp_transaction_recent_rows
