sql server 运维时CPU，内存，操作系统等信息查询（用sql语句）

我们只要用到数据库，一般会遇到数据库运维方面的事情，需要我们寻找原因，有很多是关乎处理器(cpu)、内存（memory）、磁盘（disk）以及操作系统的，这时我们就需要查询他们的一些设置和内容，
下面讲的就是如何查询它们的相关信息。
1、（1）获取数据库服务器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)

（2） 获取数据库服务器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

2：在老外博客中看到一个计算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函数。

1、（1）适用于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

（2）获取数据库服务器物理内存数(适用于所有版本)
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

（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);

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

如下所示，我们可以通过下面脚本获取服务器的各个磁盘的使用情况。但是无法获取磁盘的型号、转速之类的信息。
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这类数据）。

使用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

-------------------------------

上面是通过sql语句查询，可以通过（这里是2014版）点击数据库-属性--可以看到很多信息，但是看不到物理内存和虚拟内存，一般在属性-内存-最大内存设置：物理内存的80%

----------------------