How can SQL Server obtain processor (CPU), Memory (Memory), Disk (Disk), and operating system information through SQL statements? sqlserversql

How can SQL Server obtain processor (CPU), Memory (Memory), Disk (Disk), and operating system information through SQL statements? sqlserversql

In SQL server, how does one obtain SERVER hardware and system information through SQL statements? The following describes how to obtain processor (CPU), Memory (Memory), Disk (Disk), and operating system information through SQL statements. If you have any deficiencies or omissions, please add them. Thank you!

I. view the CPU information of the Database Server

--- SQL 1: Obtain the CPU model of the Database Server

EXEC xp_instance_regread   'HKEY_LOCAL_MACHINE',  'HARDWARE\DESCRIPTION\System\CentralProcessor\0',  'ProcessorNameString';

--- SQL 2: Obtain information such as the number of CPU cores of the database server (only applicable to databases of SQL 2005 and later versions)

/*************************************** **************************************** * ***** -- Cpu_count: specify the number of logical CPUs in the system-hyperthread_ratio: Specify the ratio of the number of logical kernels exposed by a physical processor package to the number of physical kernels. virtual Machine-indicates the number of cores in each virtual slot. [Physical CPU Count] indicates 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 s OPTION (RECOMPILE );

--- SQL 3: obtain the number of CPU cores of the database server (applicable to all versions)

CREATE TABLE #TempTable  (   [Index] VARCHAR(2000) ,   [Name] VARCHAR(2000) ,   [Internal_Value] VARCHAR(2000) ,   [Character_Value] VARCHAR(2000)  );INSERT INTO #TempTable    EXEC xp_msver;SELECT Internal_Value AS VirtualCPUCountFROM  #TempTableWHERE  Name = 'ProcessorCount';DROP TABLE #TempTable;GO

--- SQL 4.

DECLARE @ xp_msver TABLE ([idx] [int] NULL, [c_name] [varchar] (100) NULL, [int_val] [float] NULL, [c_val] [varchar] (128) NULL) insert into @ xp_msverEXEC ('[master] .. [xp_msver] '); WITH [ProcessorInfo] AS (SELECT ([cpu_count]/[hyperthread_ratio]) AS [number_of_physical_cpus], case when hyperthread_ratio = cpu_count THEN cpu_count ELSE ([cpu_count]-[hyperthread_ratio])/([cpu_count]/[hyperthread_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]/[hyperthread_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 [distinct], [distinct], [distinct], [number_of_virtual_cpus], LTRIM (RIGHT ([cpu_category], CHARINDEX ('x', [cpu_category]) -1) AS [cpu_category] FROM [ProcessorInfo] --- view Virtual Machine CPU information DECLARE @ xp_msver TABLE ([idx] [int] NULL, [c_name] [varchar] (100) NULL, [int_val] [float] NULL, [c_val] [varchar] (128) NULL) insert into @ xp_msverEXEC ('[master] .. [xp_msver] '); WITH [ProcessorInfo] AS (SELECT ([cpu_count]/[hyperthread_ratio]) AS [distinct], [hyperthread_ratio] 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 [distinct], [distinct], [distinct], [number_of_virtual_cpus], LTRIM (RIGHT ([cpu_category], CHARINDEX ('x', [cpu_category]) -1) AS [cpu_category] FROM [ProcessorInfo]

Ii. view the memory information of the Database Server

Can I use SQL statements to obtain the physical memory size of the server? Memory Module model? Virtual Memory size? Memory usage? Currently, all I know is to use SQL statements to obtain the physical memory size and memory usage of the server. As for the memory stick model, the system virtual memory size does not seem to be available for the moment through SQL statements.

View the physical memory of the server

As shown in the following figure, the physical_memory_in_bytes or physical_memory_kb value obtained from sys. dm_ OS _sys_info is always lower than the actual physical memory. The specific cause is unknown for the moment (relevant information has not been found), so the calculation size is different. To obtain the actual physical memory, you must use the CEILING function.

 

-- SQL 1: Obtain the physical memory size of the database server (applicable to all versions)

 CREATE TABLE #TempTable  (   [Index] VARCHAR(2000) ,   [Name] VARCHAR(2000) ,   [Internal_Value] VARCHAR(2000) ,   [Character_Value] VARCHAR(2000)  );INSERT INTO #TempTable    EXEC xp_msver;SELECT Internal_Value/1024 AS PhysicalMemoryFROM  #TempTableWHERE  Name = 'PhysicalMemory';DROP TABLE #TempTable;GO

--- SQL 2: Applicable to SQL Server 2005 and 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: Applicable 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: Applicable to SQL Server 2008 and later versions: Check the physical memory size, used physical memory, and 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 - available_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

3. view the hard disk information of the Database Server

As shown below, we can use the following script to obtain the usage of each disk on the server. However, you cannot obtain information such as the disk type and speed.

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 and save the 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_FORWARDFOR SELECT DiskCD FROM # DiskCapacityORDER by DiskCDOPEN CR_DiskInfo; fetch next from CR_DiskInfo INTO @ DriveInfoWHILE @ FETCH_STATUS = 0 BEGINEXEC @ Result = sp_OAMethod @ objectInfo, 'getdrive ', @ OutDrive OUT, @ DriveInfoEXEC @ Result = sp_OAGetProperty @ OutDrive, 'totalsize', @ TotalSize OUTUPDATE # DiskCapacitySET TotalSize = @ TotalSize/@ UnitMBWHERE DiskCD = @ DriveInfoFETCH next from CR_DiskInfo INTO @ DriveInfoENDCLOSE CR_DiskInfoDEALLOCATE CR_DiskInfo; EXEC @ Result = sp_OADestroy @ objectInfoEXEC 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/, 6, 2) AS [Total Size (GB)], STR (TotalSize-FreeSize) * 1.0/1.0, 6, 2) AS [Used Space (GB)], STR (FreeSize */, 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;

Iv. View operating system information

The following SQL statement allows you to view the operating system version, patch, language, and other information.

-- CREATE a temporary TABLE and save the Language version information. create table # Language ([Your agedtl] NVARCHAR (64), [OS _your age_version] INT); INSERT INTO # Your ageselect 'English-United States ', 1033 union allselect 'English-United Kingdom ', 2057 union allselect 'Chinese-others' s Republic of China', 2052 union allselect 'Chinese-Singapore ', 4100 union allselect 'Chinese-Taiwan ', 1028 union allselect 'Chinese-Hong Kong SAR', 3076 union allselect 'Chinese-Macao SAR ', 5124; WITH SystemVersion (SystemInfo, ReleaseNo) AS (SELECT 'windows 10', '10. 0 * 'Union allselect' Windows Server 2016 Technical preview', '10. 0 * 'Union allselect' Windows 8.1 ', '6. 3 * 'Union allselect' Windows Server 2012 R2 ', '6. 3 'Union allselect' Windows 8', '6. 2 'Union allselect' Windows Server 2012 ', '6. 2 'Union allselect' Windows 7', '6. 1 'Union allselect' Windows Server 2008 R2 ',' 6. 1 'Union allselect' Windows Server 2008 ', '6. 0 'Union allselect' Windows Vista ', '6. 0 'Union allselect' Windows Server 2003 R2 ', '5. 2 'Union allselect' Windows Server 2003 ', '5. 2 'Union allselect' Windows XP 64-Bit version', '5. 2 'Union ALLSELECT 'windows XP ', '5. 1 'Union allselect' Windows 2000 ', '5. 0 ') SELECT s. systemInfo, w. windows_service_pack_level, l. extends agedtlfrom sys. dm_ OS _windows_info wINNER JOIN SystemVersion s ON w. windows_release = s. releaseNoINNER JOIN # Language l ON l. OS _language_version = w. OS _language_version; DROP TABLE # Language;

Note:

1: As shown above, the temporary table # Language data only lists several commonly used data items. For all the data, see https://msdn.microsoft.com/zh-cn/goglobal/bb964664.aspx.

2: the operating system version information data comes from the https://msdn.microsoft.com/zh-CN/library/ms724832 (VS.85). aspx

Different operating systems may have the same Version number value. For example, the Version numberd of Windows 7 and Windows Server 2008 R2 is 6.1. As a result, multiple records appear in the following query results (as shown below ). It is generally necessary to determine as appropriate (if all production servers are Windows servers, such data can be excluded from Windows XP and Windows 7 ).

Ps: use SQL statements to obtain the server name and IP address

Use an SQL statement to obtain the server name and IP address:

SELECT SERVERPROPERTY('MachineName')select @@SERVERNAMEselect HOST_NAME()

To obtain the IP address, run the ipconfig command in xp_cmdshell:

-- Enable xp_cmdshell exec sp_configure 'show advanced options', 1 reconfigure with override exec sp_configure 'xp _ cmdshell', 1 reconfigure with override exec sp_configure 'show advanced options ', 0 reconfigure with override go begin declare @ ipline varchar (200) declare @ pos int declare @ ip varchar (40) set nocount on set @ ip = null if object_id ('tempdb .. # temp ') is not null drop table # temp create table # temp (ipline varchar (200) insert # temp exec master .. xp_cmdshell 'ipconfig' select @ ipline = ipline from # temp where upper (ipline) like '% IPv4 address %' -- pay attention to this, if @ ipline is not null begin set @ pos = charindex (':', @ ipline, 1 ); set @ ip = rtrim (ltrim (substring (@ ipline, @ pos + 1, len (@ ipline)-@ pos ))) end select distinct (rtrim (ltrim (substring (@ ipline, @ pos + 1, len (@ ipline)-@ pos )))) as ipaddress from # temp drop table # temp set nocount off end go

However, xp_mongoshell is not allowed due to security issues in many cases. 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