Friday, 19 November 2010

Check which node a clustered SQL Instance is on

Sometimes it is useful to know which physical node a SQL instance is running on in a cluster - e.g. for performance monitoring to check CPU attributable to an instance.

You can check this via the cluster failover manager, but you can also run the following SQL statement on the instance in question:


This will  return the netbios name of the server the node is currently active on.

I've used this in conjunction with CPU monitors, and setting processor affinity by NUMA node to track CPU activity by instance. 
The assigned NUMA node can be obtained from sys.dm_os_nodes [update] if you are on SQL 2008 R2 such as:

SELECT memory_node_id FROM sys.dm_os_nodes
cpu_affinity_mask = online_scheduler_mask
node_state_desc = 'ONLINE'