MSSQLTREK

Lot of times, when stepping up for supporting a new SQL Server Instance, we as a DBA need to know whether it’s running on a Physical Machine or a VM. In this post, I will provide you guys with a simple T-SQL Script which you can run from your client machine(SSMS) connecting to the respective SQL Server, which lets you know if it’s a Physical machine or a VM.

Note: This Script works only on 2008R2 SP1 and above.

SELECT SERVERPROPERTY('computernamephysicalnetbios') AS ServerName
,dosi.virtual_machine_type_desc
,Server_type = CASE 
WHEN dosi.virtual_machine_type = 1
THEN 'Virtual' 
ELSE 'Physical'
END
FROM sys.dm_os_sys_info dosi

/* If you have a CMS configured, run the below Script from your CMS against multiple servers*/
SELECT dosi.virtual_machine_type_desc
,Server_type = CASE 
WHEN dosi.virtual_machine_type = 1
THEN 'Virtual' 
ELSE 'Physical'
END
FROM sys.dm_os_sys_info dosi

Additional Comments:
“virtual_machine_type_desc” Output value description
NONE – SQL Server is not running inside a virtual machine.
HYPERVISOR – SQL Server is running inside a hypervisor, which implies a hardware-assisted virtualization. If the instance is running on the host OS, the description will still return HYPERVISOR.