SQL Server 2014 Health check DMV queries Day 4

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

Facebook Page , Link