SQL SERVER如何通过SQL语句获服务器硬件和系统信息-阿里云开发者社区

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

SQL SERVER如何通过SQL语句获服务器硬件和系统信息

热门文章

最新文章

相关课程

相关电子书

相关实验场景