在SQL SERVER中如何通过SQL语句获取服务器硬件和系统信息呢?下面介绍一下如何通过SQL语句获取处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统相关信息。如有不足和遗漏,敬请补充。谢谢!
 
一:查看数据库服务器CPU的信息
 
---SQL 1:获取数据库服务器的CPU型号
 
EXEC xp_instance_regread 
'HKEY_LOCAL_MACHINE',
'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
'ProcessorNameString';

 

 
---SQL 2:获取数据库服务器CPU核数等信息(只适用于SQL 2005以及以上版本数据库)
 
/*************************************************************************************
--cpu_count:指定系统中的逻辑 CPU 数
--hyperthread_ratio:指定一个物理处理器包公开的逻辑内核数与物理内核数的比.
-- 中可以表示每个虚拟插槽的核数。虚拟中[Physical CPU Count]其实
-- 表示虚拟插槽数
*************************************************************************************/
SELECT s.cpu_countAS [Loggic CPU Count]
,s.hyperthread_ratioAS [Hyperthread Ratio]
,s.cpu_count/s.hyperthread_ratioAS [Physical CPU Count] 
FROM sys.dm_os_sys_info s OPTION (RECOMPILE);

 

---SQL 3:获取服务器CPU核数(适用于所有版本)
 
CREATE TABLE #TempTable
(
[Index] VARCHAR(2000) ,
[Name] VARCHAR(2000) ,
[Internal_Value] VARCHAR(2000) ,
[Character_Value] VARCHAR(2000)
);
INSERTINTO #TempTable
EXEC xp_msver;
SELECTInternal_Value AS VirtualCPUCount
FROM#TempTable
WHERE Name = 'ProcessorCount';
DROP TABLE #TempTable;
GO

 

---SQL 4:在老外博客中看到一个计算CPU相关信息的SQL,不过虚拟机计算有点小问题,我修改了一下。
 
DECLARE @xp_msver TABLE (
[idx] [int] NULL
,[c_name] [varchar](100) NULL
,[int_val] [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]
,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 [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]

 

---查看虚拟机CPU信息
 
DECLARE @xp_msver TABLE (
[idx] [int] NULL
,[c_name] [varchar](100) NULL
,[int_val] [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_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 [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]

 

 
二:查看数据库服务器内存的信息
 
能否通过SQL语句获取服务器的物理内存大小?内存条型号?虚拟内存大小?内存使用情况? 目前我所知道的只能通过SQL语句获取服务器物理内存大小,内存的使用情况。 至于内存条型号,系统虚拟内存大小,暂时好像还无法通过SQL语句获取。
 
查看服务器的物理内存情况
 
如下所示,从sys.dm_os_sys_info里面获取的physical_memory_in_bytes 或physical_memory_kb 的值总是低于实际物理内存。暂时不清楚具体原因(还未查到相关资料),所以计算大小有出入,要获取实际的物理内存,就必须借助CEILING函数。
 
 
--SQL 1:获取数据库服务器物理内存数(适用于所有版本)
 CREATE TABLE #TempTable
(
[Index] VARCHAR(2000) ,
[Name] VARCHAR(2000) ,
[Internal_Value] VARCHAR(2000) ,
[Character_Value] VARCHAR(2000)
);
INSERTINTO #TempTable
EXEC xp_msver;
SELECTInternal_Value/1024 AS PhysicalMemory
FROM#TempTable
WHERE Name = 'PhysicalMemory';
DROP TABLE #TempTable;
GO

 

---SQL 2:适用于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_infoOPTION (RECOMPILE) 


SELECT physical_memory_in_bytes*1.0/1024/1024/1024
 , physical_memory_in_bytes AS [Physical Memory Size]
FROM sys.dm_os_sys_infoOPTION (RECOMPILE)
---SQL 3:适用于SQL Server 2012 到 SQL Server 2014

 SELECT CEILING(physical_memory_kb*1.0/1024/1024) AS [Physical Memory Size]
FROM sys.dm_os_sys_infoOPTION (RECOMPILE);

 

---SQL 4:适用于SQL Server 2008以及以上的版本:查看物理内存大小,已经使用的物理内存以及还剩下的物理内存。
 
SELECTCEILING(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 /