程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SqlServer若何經由過程SQL語句獲得處置器(CPU)、內存（Memory）、磁盤（Disk）和操作體系相干信息

SqlServer若何經由過程SQL語句獲得處置器(CPU)、內存（Memory）、磁盤（Disk）和操作體系相干信息

編輯：MSSQL

SqlServer若何經由過程SQL語句獲得處置器(CPU)、內存（Memory）、磁盤（Disk）和操作體系相干信息。本站提示廣大學習愛好者：（SqlServer若何經由過程SQL語句獲得處置器(CPU)、內存（Memory）、磁盤（Disk）和操作體系相干信息）文章只能為提供參考，不一定能成為您想要的結果。以下是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 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這類數據）。

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

MSSQL