Hi guys in the Previous post i explained How we can get the Global Trace Flag Information , Getting Information About Windows and How to return SQL Server Services Information put today in DAY 4 i will show new DMV in our track Instance Level configuration
DMV#9 SQL Server NUMA Info
by the below DMV we can return more helpful information related to SQL Server NUMA info and it will return also the Node status , and load balance .
SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count, active_worker_count, avg_load_balance, resource_monitor_state FROM sys.dm_os_nodes WITH (NOLOCK) WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);
DMV#10 Getting Hardware Information
very fantastic DMV and very helpful by the below T-SQL we can return lot of information about Hardware like Logical CPU count , Physical Memory , Physical CPU count , SQL Start time …ETC
virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM
It merely indicates that you have a hypervisor running on your host
SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count], physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)], committed_target_kb/1024 AS [Committed Target Memory (MB)], max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type], sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type] FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
To check all my posts you can direct to my personal website or you can download this Excel sheet My Technical POSTS
View all my tips , LinkedIn , Website , Slideshare ,Youtube Channel.
Follow up us on
One thought on “SQL Server 2014 Health check DMV queries Day 4”