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

作者:潇湘隐者 时间:2024-01-17 20:18:16 

在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函数。

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

--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 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这类数据)。

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

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
标签:sqlserver,服务器
0
投稿

猜你喜欢

  • 慢慢的网页

    2009-11-12 12:53:00
  • PHP getDocNamespaces()函数讲解

    2023-06-13 22:19:06
  • PHP后台备份MySQL数据库的源码实例

    2023-09-04 14:55:11
  • window.top[_CACHE]实现多个jsp页面共享一个js对象

    2023-07-18 09:25:14
  • Python 读取xml数据,cv2裁剪图片实例

    2021-07-18 00:08:08
  • linux安装mysql和使用c语言操作数据库的方法 c语言连接mysql

    2024-01-12 20:31:03
  • Python获取CPU、内存使用率以及网络使用状态代码

    2023-03-14 22:43:36
  • 详解TensorFlow在windows上安装与简单示例

    2021-06-01 23:54:31
  • pandas 使用merge实现百倍加速的操作

    2022-06-11 08:22:28
  • 使用CSS3和RGBa创建超酷的按钮

    2009-06-02 12:41:00
  • Python中非常实用的一些功能和函数分享

    2022-05-28 16:25:06
  • Python算法之求n个节点不同二叉树个数

    2022-08-22 03:59:30
  • Python基础教程之循环语句(for、while和嵌套循环)

    2022-07-30 10:00:26
  • 使用Keras预训练好的模型进行目标类别预测详解

    2023-02-04 09:14:37
  • 简单介绍Python虚拟环境及使用方法

    2021-03-19 20:59:53
  • SQLServer数据库中开启CDC导致事务日志空间被占满的原因

    2024-01-18 01:41:39
  • Python中的下划线详解

    2021-10-24 14:17:32
  • Keras函数式(functional)API的使用方式

    2023-09-19 05:52:15
  • Python contextlib模块使用示例

    2023-08-08 16:34:23
  • Django原生sql也能使用Paginator分页的示例代码

    2023-11-09 10:00:06
  • asp之家 网络编程 m.aspxhome.com