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

在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 - people''s *',2052 union all
select 'chinese - singapore'         ,4100 union all
select 'chinese - *'           ,1028 union all
select 'chinese - * 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_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 地址%'--这里需要注意一下，系统不同这里的匹配值就不同 
  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