SqlServer如何通过SQL语句获取处理器(CPU)、内存（Memory）、磁盘（Disk）以及操作系统相关信息
在SQLSERVER中如何通过SQL语句获取服务器硬件和系统信息呢？下面介绍一下如何通过SQL语句获取处理器(CPU)、内存（Memory）、磁盘（Disk）以及操作系统相关信息。如有不足和遗漏，敬请补充
在SQL SERVER中如何通过SQL语句获取服务器硬件和系统信息呢？下面介绍一下如何通过SQL语句获取处理器(CPU)、内存（Memory）、磁盘（Disk）以及操作系统相关信息。如有不足和遗漏，敬请补充。谢谢！
一：查看数据库服务器CPU的信息
---SQL 1:获取数据库服务器的CPU型号
EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'HARDWAREDESCRIPTIONSystemCentralProcessor ', '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 VirtualCPUCountFROM #TempTableWHERE 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_msverEXEC ('[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_msverEXEC ('[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 PhysicalMemoryFROM #TempTableWHERE 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', 1RECONFIGURE 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_FORWARDFOR SELECT DiskCD FROM #DiskCapacityORDER by DiskCDOPEN CR_DiskInfo;FETCH NEXT FROM CR_DiskInfo INTO @DriveInfoWHILE @@FETCH_STATUS=0BEGINEXEC @Result = sp_OAMethod @objectInfo,'GetDrive', @OutDrive OUT, @DriveInfoEXEC @Result = sp_OAGetProperty @OutDrive,'TotalSize', @TotalSize OUTUPDATE #DiskCapacitySET TotalSize=@TotalSize/@UnitMBWHERE DiskCD=@DriveInfoFETCH NEXT FROM CR_DiskInfo INTO @DriveInfoENDCLOSE CR_DiskInfoDEALLOCATE CR_DiskInfo;EXEC @Result=sp_OADestroy @objectInfoEXEC sp_configure 'show advanced options', 1RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'Ole Automation Procedures', 0;RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'show advanced options', 0RECONFIGURE 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 #LanguageSELECT 'English - United States' ,1033 UNION ALLSELECT 'English - United Kingdom' ,2057 UNION ALLSELECT 'Chinese - People''s Republic of China',2052 UNION ALLSELECT 'Chinese - Singapore' ,4100 UNION ALLSELECT 'Chinese - Taiwan' ,1028 UNION ALLSELECT 'Chinese - Hong Kong SAR' ,3076 UNION ALLSELECT 'Chinese - Macao SAR' ,5124;WITH SystemVersion(SystemInfo,ReleaseNo)AS(SELECT 'Windows 10' , '10.0*'UNION ALLSELECT 'Windows Server 2016 Technical Preview' , '10.0*'UNION ALLSELECT 'Windows 8.1' , '6.3*'UNION ALLSELECT 'Windows Server 2012 R2' , '6.3'UNION ALLSELECT 'Windows 8' , '6.2'UNION ALLSELECT 'Windows Server 2012' , '6.2'UNION ALLSELECT 'Windows 7' , '6.1'UNION ALLSELECT 'Windows Server 2008 R2' , '6.1'UNION ALLSELECT 'Windows Server 2008' , '6.0'UNION ALLSELECT 'Windows Vista' , '6.0'UNION ALLSELECT 'Windows Server 2003 R2' , '5.2'UNION ALLSELECT 'Windows Server 2003' , '5.2'UNION ALLSELECT 'Windows XP 64-Bit Edition' , '5.2'UNION ALLSELECT 'Windows XP' , '5.1'UNION ALLSELECT 'Windows 2000' , '5.0') SELECT s.SystemInfo ,w.windows_service_pack_level ,l.LanguageDtlFROM sys.dm_os_windows_info wINNER JOIN SystemVersion s ON w.windows_release=s.ReleaseNoINNER 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 @@SERVERNAMEselect 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 如何 通过 SQL 语句 获取 处理器 CPU 内存
声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！
