How SQL Server obtains the processor (CPU), Memory (Memory), disk (disks), and operating system-related information through an _mssql

How do you get server hardware and system information through SQL statements in SQL Server? Here's how to get the processor (CPU), Memory (Memory), disk, and operating system-related information through an SQL statement. If there are deficiencies and omissions, please add. Thank you!

One: View the database server CPU information

---SQL 1: Getting the CPU model for the database server

EXEC xp_instance_regread 
  ' HKEY_LOCAL_MACHINE ',
  ' hardware\description\system\centralprocessor\0 ',
  ' ProcessorNameString ';

---SQL 2: Get the database Server CPU audit information (SQL 2005 and above version database only)

/*************************************************************************************
--cpu_count        : Specify the number of logical CPUs in the system
--hyperthread_ratio    : Specifies the ratio of logical cores exposed by a physical processor package to the number of physical cores. Virtual Machines
--             can represent the number of cores per virtual slot. Virtual [physical CPU count] actually
--             represents the number of virtual slots
*************************************************************** /
SELECT s.cpu_count            as [loggic CPU Count]
   , s.hyperthread_ratio as        [ Hyperthread Ratio]
   , s.cpu_count/s.hyperthread_ratio as [physical CPU count] from 
sys.dm_os_sys_info OPTION (RECOMPILE);

---SQL 3: Get the database Server CPU core (for all versions)

CREATE TABLE #TempTable
  (
   [Index] VARCHAR,
   [Name] VARCHAR,
   [Internal_value] VARCHAR ( ),
   [Character_value] VARCHAR
  );
INSERT into #TempTable
    EXEC xp_msver;
SELECT Internal_value as Virtualcpucount
from  #TempTable
WHERE  Name = ' processorcount ';
DROP TABLE #TempTable;
Go

---SQL 4: In the Foreigner blog to see a calculation of CPU-related information of the SQL, but the virtual machine computing a little problem, I modified a bit.

DECLARE @xp_msver TABLE ([idx] [int] null, [c_name] [varchar] (m) null, [Int_val] [float] null, [C_val] [varcha R] (128) NULL) INSERT into @xp_msver EXEC (' [Master] ...
[xp_msver];; With [Processorinfo] as (SELECT ([Cpu_count]/[Hyperthread_ratio]) as [Number_of_physical_cpus], case Hyperthread_ratio = Cpu_count THEN cpu_count ELSE ([Cpu_count]-[hyperthread_ratio])/([Cpu_count]/[Hyp Erthread_ratio]) end as [number_of_cores_per_cpu], case when hyperthread_ratio = Cpu_count THEN Cpu_count ELSE ([Cpu_count]/[Hyperthread_ratio]) * ([Cpu_count]-[hyperthread_ratio])/([Cpu_count]/[Hyperthr 
      Ead_ratio]) end as [Total_number_of_cores], [Cpu_count] as [Number_of_virtual_cpus], (SELECT [C_val] From @xp_msver WHERE [c_name] = ' Platform ') as [cpu_category] from [SYS].
[Dm_os_sys_info]) SELECT [Number_of_physical_cpus], [number_of_cores_per_cpu], [total_number_of_cores], [Number_of_virtual_cpus], LTRIM (Right ([cpu_category], CHARINDEX (' x ', [Cpu_category])-1) as [cpu_category] From [Processorinfo]---View the virtual machine CPU information DECLARE @xp_msver TABLE ([idx] [int] null, [c_name] [varchar] (m) NULL, [Int_va L] [float] null, [c_val] [varchar] (128) NULL) INSERT into @xp_msver EXEC (' [Master] ...
[xp_msver];; With [Processorinfo] as (SELECT ([Cpu_count]/[Hyperthread_ratio]) as [Number_of_physical_cpus], [Hyperthread_rati
    O] as [number_of_cores_per_cpu], [Cpu_count] as [Total_number_of_cores], [Cpu_count] as [Number_of_virtual_cpus] , (SELECT [C_val] from @xp_msver WHERE [c_name] = ' Platform ') as [cpu_category] from [sys].[ Dm_os_sys_info]) SELECT [Number_of_physical_cpus], [number_of_cores_per_cpu], [Total_number_of_cores], [number_of _virtual_cpus], LTRIM ([Cpu_category], CHARINDEX (' x ', [Cpu_category])-1) as [cpu_category] from [Processorinfo]

Second: View database server memory information

Can I get the physical memory size of the server through SQL statements? Memory strip model? Virtual memory size? Memory usage? At present, I know only through SQL statements to get the size of the server's physical memory, memory usage. As for the memory strip model, the system virtual memory size, temporarily seems to be unable to get through the SQL statement.

View the physical memory of the server

As shown below, the value of the physical_memory_in_bytes or physical_memory_kb obtained from the sys.dm_os_sys_info is always lower than the actual physical memory. Temporarily not clear the specific reason (has not found the relevant data), so the calculation size discrepancy, to obtain actual physical memory, you must use the ceiling function.

--sql 1: Get the number of physical memory for the database server (applicable to all versions)

 CREATE TABLE #TempTable
  (
   [Index] VARCHAR,
   [Name] VARCHAR,
   [Internal_value] VARCHAR ( ),
   [Character_value] VARCHAR
  );
INSERT into #TempTable
    EXEC xp_msver;
SELECT internal_value/1024 as PhysicalMemory
from  #TempTable
WHERE  Name = ' physicalmemory ';
DROP TABLE #TempTable;
Go

---SQL 2: For SQL Server 2005, SQL Server 2008

 SELECT CEILING (physical_memory_in_bytes*1.0/1024/1024/1024) as  [physical memory Size] from
sys.dm_os_sys_ Info OPTION (RECOMPILE) 
SELECT physical_memory_in_bytes*1.0/1024/1024/1024
  ,  physical_memory_in_bytes As [physical Memory Size] from
sys.dm_os_sys_info OPTION (RECOMPILE)

---SQL 3: Applies to SQL Server 2012 to SQL Server 2014

 SELECT CEILING (physical_memory_kb*1.0/1024/1024) as [physical memory Size] from
sys.dm_os_sys_info OPTION ( RECOMPILE);

---SQL 4: For SQL Server 2008 and above versions: view the physical memory size, the physical memory that has been used, and the remaining physical memory.

SELECT CEILING (total_physical_memory_kb * 1.0/1024/1024)  as [physical memory Size] 
    , CAST (Available_physical_ MEMORY_KB * 1.0/1024/1024 
                       as DECIMAL (8, 4)) as [unused physical memory]
    , CAST ((Total_physical_memory_kb-ava ILABLE_PHYSICAL_MEMORY_KB) * 1.0
    /1024/1024 as DECIMAL (8, 4))              as [Used physical memory]
    , CAST (system_cache _kb*1.0/1024/1024 as DECIMAL (8, 4)) as [System Cache Size]
from  sys.dm_os_sys_memory

Third: View the database server hard disk information

As shown below, we can use the following script to get the usage of each disk in the server. However, you cannot get information such as the disk model, rotate speed, and so on.

SET NOCOUNT on DECLARE @Result INT;
DECLARE @objectInfo INT;
DECLARE @DriveInfo CHAR (1);
DECLARE @TotalSize VARCHAR (20);
DECLARE @OutDrive INT;
DECLARE @UnitMB BIGINT;
DECLARE @FreeRat FLOAT;
SET @UnitMB = 1048576;
--Create a temporary table save server disk capacity Information CREATE table #DiskCapacity ([DISKCD] CHAR (1), freesize int, totalsize int);
INSERT #DiskCapacity ([diskcd], freesize) EXEC master.dbo.xp_fixeddrives;
EXEC sp_configure ' show advanced options ', 1 reconfigure with OVERRIDE;
EXEC sp_configure ' Ole automation procedures ', 1;
Reconfigure with OVERRIDE;
EXEC @Result = Master.sys.sp_OACreate ' Scripting.FileSystemObject ', @objectInfo out;
DECLARE cr_diskinfo CURSOR Local fast_forward for SELECT DISKCD to #DiskCapacity order by DISKCD OPEN Cr_diskinfo; FETCH NEXT from Cr_diskinfo to @DriveInfo while @ @FETCH_STATUS =0 BEGIN EXEC @Result = sp_OAMethod @objectInfo, ' getdrive ' , @OutDrive out, @DriveInfo EXEC @Result = sp_OAGetProperty @OutDrive, ' totalsize ', and @TotalSize out UPDATE #DiskCaPacity SET totalsize= @TotalSize/@UnitMB WHERE diskcd= @DriveInfo FETCH NEXT from Cr_diskinfo to @DriveInfo end Close Cr_d
Iskinfo deallocate Cr_diskinfo;
EXEC @Result =sp_oadestroy @objectInfo exec sp_configure ' show advanced options ', 1 reconfigure with OVERRIDE;
EXEC sp_configure ' Ole automation procedures ', 0;
Reconfigure with OVERRIDE;
EXEC sp_configure ' show advanced options ', 0 reconfigure with OVERRIDE; SELECT diskcd as [Drive CD], str (totalsize*1.0/1024,6,2) as [Total Size (GB)], str ((totalsize-freesize) *1.0/ 1024,6,2) as [Used spaces (GB)], str (freesize*1.0/1024,6,2) as [free space (GB)], str ((totalsize-freesize) *1.0/(  totalsize) * 100.0,6,2) as [Used Rate (%)], STR ((Freesize * 1.0/(totalsize)) * 100.0,6,2) as [free Rate (%)] from
#DiskCapacity; DROP TABLE #DiskCapacity;

Four: View operating System Information

With the following SQL statements, we can view the operating system version, patch, language, etc.

--Create a temporary table save language version Information create TABLE #Language ([LANGUAGEDTL] NVARCHAR (), [os_language_version] INT);  INSERT into #Language select ' english-united States ', 1033 UNION all SELECT ' english-united Kingdom ', 2057 UNION ALL SELECT ' Chinese-people ' Republic of ", 2052 union ALL Select ' Chinese-singapore ', 4100 union All select ' Chinese-taiwan ', 1028 union ALL SELECT ' Chinese-hong Kong SAR ', 3076 UNION ALL SELECT ' Chi
Nese-macao SAR ', 5124; With Systemversion (Systeminfo,releaseno) as (SELECT ' Windows ten ', ' 10.0* ' UNION all SELECT ' Windows Server 2016 Tech Nical Preview ', ' 10.0* ' union ALL SELECT ' Windows 8.1 ', ' 6.3* ' union ALL SELECT ' Windows Server R2 ', '  6.3 ' UNION ALL SELECT ' Windows 8 ', ' 6.2 ' union ALL SELECT ' Windows Server # ', ' 6.2 ' union ALL SELECT ' windows 7 ', ' 6.1 ' union ALL SELECT ' Windows Server 2008 R2 ', ' 6.1 ' union ALL SELECT ' Windows Server 2008 ', ' 6.0 ' U Nion ALL Select ' Windows Vista ', ' 6.0 ' union ALL SELECT ' Windows Server 2003 R2 ', ' 5.2 ' union ALL select ' Windows Serve R 2003 ', ' 5.2 ' union ALL SELECT ' Windows XP 64-bit Edition ', ' 5.2 ' union ALL SELECT ' Windows XP ', ' 5.1 ' UNI On all select ' Windows s.systeminfo ', ' 5.0 ') Select, W.windows_service_pack_level, L.languagedtl FRO M sys.dm_os_windows_info W INNER join Systemversion s on W.windows_release=s.releaseno INNER join #Language l on L.os_lang
Uage_version = w.os_language_version; DROP TABLE #Language;

Attention:

1: As shown above, temporary table #language data here only a few commonly used data, such as the need for all data, reference https://msdn.microsoft.com/zh-CN/goglobal/bb964664.aspx supplement.

2: The version information of the operating system is derived from https://msdn.microsoft.com/zh-CN/library/ms724832 (vs.85). aspx

It is possible that different operating systems have the same version number value, such as Windows 7 and Windows Server 2008 R2 version Numberd are 6.1. Causes the following query results to appear more than one record (shown below). It is generally appropriate to judge (if the production server is a Windows server, you can eliminate data such as Windows XP, Windows 7).

PS: Get server name and IP address using SQL statement

Get server name and IP address get server name using SQL statement:

Select SERVERPROPERTY (' machinename ')
SELECT @ @SERVERNAME
Select HOST_NAME ()

To obtain an IP address, you can use xp_cmdshell to execute the ipconfig command:

However, in many cases, because the security issue is not allowed to use xp_cmdshell, you can query sys.dm_exec_connections:

SELECT SERVERNAME = CONVERT (NVARCHAR (128), serverproperty (' SERVERNAME ')) 
, local_net_address as ' Ipaddressofsqlserver '
, client_net_address as ' clientipaddress ' from
 SYS. Dm_exec_connections WHERE session_id = @ @SPID