软件开发培训班 >> 编程开发 >> SQL


SqlServer如何通过SQL语句获取处理器(CPU)、内存（Memory）、磁盘（Disk）以及操作系统相关信息

　　本文标签：sqlserver服务器

在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_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:获取数据库服务器CPU核数(适用于所有版本)

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 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)
  );
INSERT INTO #TempTable
    EXEC xp_msver;
SELECT Internal_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_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：适用于SQL Server 2012 到 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：适用于SQL Server 2008以及以上的版本:查看物理内存大小，已经使用的物理内存以及还剩下的物理内存 。

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

三：查看数据库服务器硬盘的信息

如下所示，我们可以通过下面脚本获取服务器的各个磁盘的使用情况 。但是无法获取磁盘的型号、转速之类的信息 。

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 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 FROM #DiskCapacity
ORDER by DiskCD
OPEN CR_DiskInfo;
FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @Result = sp_OAMethod @objectInfo,GetDrive, @OutDrive OUT, @DriveInfo
EXEC @Result = sp_OAGetProperty @OutDrive,TotalSize, @TotalSize OUT
UPDATE #DiskCapacity
SET TotalSize=@TotalSize/@UnitMB
WHERE DiskCD=@DriveInfo
FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo
END
CLOSE CR_DiskInfo
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 Space(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;

四：查看操作系统信息

  通过下面SQL语句，我们可以查看操作系统版本、补丁、语言等信息

--创建临时表保存语言版本信息
CREATE TABLE #Language
(
[LanguageDtl]      NVARCHAR(64) ,
[os_language_version]  INT
);
INSERT INTO #Language
SELECT English - United States       ,1033 UNION ALL
SELECT English - United Kingdom       ,2057 UNION ALL
SELECT Chinese - Peoples Republic of China,2052 UNION ALL
SELECT Chinese - Singapore         ,4100 UNION ALL
SELECT Chinese - Taiwan           ,1028 UNION ALL
SELECT Chinese - Hong Kong SAR       ,3076 UNION ALL
SELECT Chinese - Macao SAR         ,5124;
WITH SystemVersion(SystemInfo,ReleaseNo)
AS
(
SELECT Windows 10 ,
    10.0*
UNION ALL
SELECT Windows Server 2016 Technical Preview ,
    10.0*
UNION ALL
SELECT Windows 8.1 ,
    6.3*
UNION ALL
SELECT Windows Server 2012 R2 ,
    6.3
UNION ALL
SELECT Windows 8 ,
    6.2
UNION ALL
SELECT Windows Server 2012 ,
    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
UNION ALL
SELECT Windows Vista ,
    6.0
UNION ALL
SELECT Windows Server 2003 R2 ,
    5.2
UNION ALL
SELECT Windows Server 2003 ,
    5.2
UNION ALL
SELECT Windows XP 64-Bit Edition ,
    5.2
UNION ALL
SELECT Windows XP ,
    5.1
UNION ALL
SELECT Windows 2000 ,
    5.0
)    
SELECT s.SystemInfo 
   ,w.windows_service_pack_level
   ,l.LanguageDtl
FROM sys.dm_os_windows_info w
INNER JOIN SystemVersion s ON w.windows_release=s.ReleaseNo
INNER JOIN #Language l ON l.os_language_version = w.os_language_version;
DROP TABLE #Language;

注意：

  1：如上所示，临时表#Language的数据此处只列了几条常用的数据，如需全部数据，参考https://msdn.microsoft.com/zh-CN/goglobal/bb964664.aspx自行补充 。

  2：操作系统的版本信息的数据来源于https://msdn.microsoft.com/zh-CN/library/ms724832(VS.85).aspx

有可能出现不同操作系统具有相同Version number值，例如Windows 7 和Windows Server 2008 R2的Version numberd都为6.1 。导致下面查询结果出现多条记录（如下所示） 。一般要酌情判断（如果生产服务器都为Windows服务器，可以剔除Windows XP、Windows 7这类数据） 。

ps:使用SQL语句获得服务器名称和IP地址

使用SQL语句获得服务器名称和IP地址   获取服务器名称： 

SELECT SERVERPROPERTY(MachineName)
select @@SERVERNAME
select HOST_NAME()

获取IP地址可以使用xp_cmdshell执行ipconfig命令：

--开启xp_cmdshell 
exec sp_configureshow advanced options, 1 
reconfigure with override 
exec sp_configurexp_cmdshell, 1 
reconfigure with override 
exec sp_configureshow 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_cmdshellipconfig 
  select @ipline = ipline 
  from #temp 
  where upper(ipline) like %IPv4 地址%--这里需要注意一下，系统不同这里的匹配值就不同 
  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

但是很多情况下由于安全问题是不允许使用xp_cmdshell，可以通过查询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