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:


SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')



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
WHERE
cpu_affinity_mask = online_scheduler_mask
AND
node_state_desc = 'ONLINE'

4 comments:

Anonymous said...

Last query works for 2008 R2 only, not for 2008

GrumpyDBA said...

Thanks, I'll add that as an update

Anonymous said...

But I still interested how discover default and current node for instance on 2008 without call WMI

Grumpy DBA said...

You can also use:

EXEC master..xp_regread 'HKEY_LOCAL_Machine',
'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\',
'ComputerName'

Post a Comment