您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 以 T-SQL 取得 CPU、記憶體、磁碟與作業系統資訊完整指南

編輯：關於SqlServer

概述

DBA 日常巡檢、容量規劃、雲端遷移評估常需快速掌握伺服器硬體規格。本文彙整 純 T-SQL 方式 查詢處理器（CPU）、記憶體、磁碟、作業系統版本與網路資訊，涵蓋 SQL Server 2005 至 2019+ 版本差異，並附虛擬化環境注意事項。

一、CPU 資訊查詢

1.1 處理器型號（適用所有版本）

EXEC xp_instance_regread 
  'HKEY_LOCAL_MACHINE', 
  'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 
  'ProcessorNameString';

1.2 邏輯/實體核心數（SQL 2005+，DMV 方式）

SELECT 
  cpu_count AS [Logical CPU Count],
  hyperthread_ratio AS [Hyperthread Ratio],
  cpu_count / hyperthread_ratio AS [Physical CPU Count]
FROM sys.dm_os_sys_info
OPTION (RECOMPILE);

說明：hyperthread_ratio 為每顆實體 CPU 的邏輯核心比。虛擬機中 Physical CPU Count 實為 vSocket 數，需結合雲端廠商規格解讀。

1.3 通用版本：xp_msver 取得處理器計數

CREATE TABLE #Tmp (Idx VARCHAR(2000), Name VARCHAR(2000), Internal_Value VARCHAR(2000), Character_Value VARCHAR(2000));
INSERT INTO #Tmp EXEC xp_msver;
SELECT Internal_Value AS VirtualCPUCount FROM #Tmp WHERE Name = 'ProcessorCount';
DROP TABLE #Tmp;

1.4 進階：計算實體 CPU、每顆核心數、總核心數（含虛擬機修正版）

DECLARE @xp_msver TABLE (idx INT, c_name VARCHAR(100), int_val FLOAT, c_val VARCHAR(128));
INSERT INTO @xp_msver EXEC ('[master]..[xp_msver]');

;WITH ProcessorInfo AS (
  SELECT 
    (cpu_count / hyperthread_ratio) AS [Physical_CPUs],
    CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count 
         ELSE ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio)) END AS [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_Cores],
    cpu_count AS [Logical_CPUs],
    (SELECT c_val FROM @xp_msver WHERE c_name = 'Platform') AS [Platform]
  FROM sys.dm_os_sys_info
)
SELECT 
  [Physical_CPUs], [Cores_Per_CPU], [Total_Cores], [Logical_CPUs],
  LTRIM(RIGHT([Platform], CHARINDEX('x', [Platform]) - 1)) AS [CPU_Category]
FROM ProcessorInfo;

虛擬機修正版：若 hyperthread_ratio = cpu_count，視為單執行緒 vCPU，直接以 hyperthread_ratio 為每插槽核心數，cpu_count 為總核心數。

二、記憶體資訊查詢

DMV 回傳的 physical_memory_in_bytes / physical_memory_kb 常略低於實際實體記憶體（保留給硬體/韌體），建議以 CEILING 進位至 GB。

2.1 通用版：xp_msver 取得實體記憶體（KB → MB）

CREATE TABLE #Tmp (Idx VARCHAR(2000), Name VARCHAR(2000), Internal_Value VARCHAR(2000), Character_Value VARCHAR(2000));
INSERT INTO #Tmp EXEC xp_msver;
SELECT Internal_Value/1024 AS PhysicalMemory_MB FROM #Tmp WHERE Name = 'PhysicalMemory';
DROP TABLE #Tmp;

2.2 SQL 2005/2008：physical_memory_in_bytes

SELECT CEILING(physical_memory_in_bytes*1.0/1024/1024/1024) AS [Physical_Memory_GB]
FROM sys.dm_os_sys_info OPTION (RECOMPILE);

2.3 SQL 2012~2014：physical_memory_kb

SELECT CEILING(physical_memory_kb*1.0/1024/1024) AS [Physical_Memory_GB]
FROM sys.dm_os_sys_info OPTION (RECOMPILE);

2.4 SQL 2008+：總量、可用、已用、系統快取（最實用）

SELECT 
  CEILING(total_physical_memory_kb * 1.0 / 1024 / 1024) AS [Physical_Memory_GB],
  CAST(available_physical_memory_kb * 1.0 / 1024 / 1024 AS DECIMAL(8,4)) AS [Available_GB],
  CAST((total_physical_memory_kb - available_physical_memory_kb) * 1.0 / 1024 / 1024 AS DECIMAL(8,4)) AS [Used_GB],
  CAST(system_cache_kb * 1.0 / 1024 / 1024 AS DECIMAL(8,4)) AS [System_Cache_GB]
FROM sys.dm_os_sys_memory;

三、磁碟資訊查詢

透過 xp_fixeddrives 取得剩餘空間，結合 Scripting.FileSystemObject (OLE Automation) 取得總容量。需暫時啟用 Ole Automation Procedures。

SET NOCOUNT ON;
DECLARE @Result INT, @Obj INT, @Drive CHAR(1), @TotalSize VARCHAR(20), @OutDrive INT, @UnitMB BIGINT = 1048576;

CREATE TABLE #Disk (DiskCD CHAR(1), FreeMB INT, TotalMB INT);
INSERT #Disk(DiskCD, FreeMB) 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', @Obj OUT;

DECLARE cr CURSOR LOCAL FAST_FORWARD FOR SELECT DiskCD FROM #Disk ORDER BY DiskCD;
OPEN cr; FETCH NEXT FROM cr INTO @Drive;
WHILE @@FETCH_STATUS = 0 BEGIN
  EXEC @Result = master.sys.sp_OAMethod @Obj, 'GetDrive', @OutDrive OUT, @Drive;
  EXEC @Result = master.sys.sp_OAGetProperty @OutDrive, 'TotalSize', @TotalSize OUT;
  UPDATE #Disk SET TotalMB = CAST(@TotalSize AS BIGINT) / @UnitMB WHERE DiskCD = @Drive;
  FETCH NEXT FROM cr INTO @Drive;
END
CLOSE cr; DEALLOCATE cr;
EXEC @Result = master.sys.sp_OADestroy @Obj;

EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE;

SELECT 
  DiskCD AS [Drive],
  STR(TotalMB*1.0/1024,6,2) AS [Total_GB],
  STR((TotalMB - FreeMB)*1.0/1024,6,2) AS [Used_GB],
  STR(FreeMB*1.0/1024,6,2) AS [Free_GB],
  STR((TotalMB - FreeMB)*1.0/TotalMB*100,6,2) AS [Used_%],
  STR(FreeMB*1.0/TotalMB*100,6,2) AS [Free_%]
FROM #Disk;
DROP TABLE #Disk;

限制：無法取得磁碟型號、轉速、RAID 層級等硬體細節；生產環境若禁用 OLE Automation，可改用 PowerShell / WMI 外部採集。

四、作業系統版本與語系

CREATE TABLE #Lang (LanguageDtl NVARCHAR(64), os_language_version INT);
INSERT INTO #Lang VALUES
  ('English - United States',1033), ('English - United Kingdom',2057),
  ('Chinese - PRC',2052), ('Chinese - Singapore',4100),
  ('Chinese - Taiwan',1028), ('Chinese - Hong Kong SAR',3076),
  ('Chinese - Macao SAR',5124);

WITH OSVer(SystemInfo, ReleaseNo) AS (
  SELECT 'Windows 10', '10.0*' UNION ALL SELECT 'Windows Server 2016', '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', '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
JOIN OSVer s ON w.windows_release LIKE s.ReleaseNo
JOIN #Lang l ON l.os_language_version = w.os_language_version;
DROP TABLE #Lang;

注意：Win 7 / Server 2008 R2 同為 6.1，查詢會回傳兩列，需依據伺服器角色人工判讀。

五、伺服器名稱與 IP 位址

5.1 伺服器名稱

SELECT SERVERPROPERTY('MachineName'); -- 實體主機名
SELECT @@SERVERNAME;                 -- SQL 實例名
SELECT HOST_NAME();                  -- 連線用戶端主機名

5.2 IP 位址（兩種方式）

方式 A：xp_cmdshell 執行 ipconfig（需啟用，安全性較低）

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

DECLARE @ip VARCHAR(40), @line VARCHAR(200), @pos INT;
CREATE TABLE #tmp(line VARCHAR(200));
INSERT #tmp EXEC master..xp_cmdshell 'ipconfig';
SELECT TOP 1 @line = line FROM #tmp WHERE UPPER(line) LIKE '%IPV4%';
IF @line IS NOT NULL BEGIN
  SET @pos = CHARINDEX(':', @line);
  SET @ip = LTRIM(RTRIM(SUBSTRING(@line, @pos+1, LEN(@line)-@pos)));
END
SELECT @ip AS IPv4_Address;
DROP TABLE #tmp;

EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

方式 B：sys.dm_exec_connections（無需額外權限，推薦）

SELECT 
  CONVERT(NVARCHAR(128), SERVERPROPERTY('SERVERNAME')) AS ServerName,
  LOCAL_NET_ADDRESS AS [SQL_Server_IP],
  CLIENT_NET_ADDRESS AS [Client_IP]
FROM SYS.DM_EXEC_CONNECTIONS
WHERE SESSION_ID = @@SPID;