萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 透過 T-SQL 獲取伺服器 CPU、記憶體、磁碟與作業系統資訊完整腳本

SQL Server 透過 T-SQL 獲取伺服器 CPU、記憶體、磁碟與作業系統資訊完整腳本

無需登入作業系統,僅用 T-SQL 即可查詢 SQL Server 實例所在伺服器的硬體規格與 OS 版本。本文整理適用於 SQL Server 2005~2022 的完整查詢腳本,涵蓋 CPU 型號/核心/超執行緒、實體記憶體/使用量、磁碟容量/使用率、Windows 版本/Service Pack/語言,並提供取得伺服器名稱與 IP 的多種方法。

一、CPU 資訊

1. CPU 型號(登錄表)

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

2. 邏輯/實體核心數(DMV,2005+)

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);

3. 相容所有版本(xp_msver)

CREATE TABLE #Tmp ([Index] 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;

4. 進階核心拓撲計算(修正虛擬機誤差)

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

WITH ProcessorInfo AS (
  SELECT
    cpu_count / hyperthread_ratio AS PhysicalCPUs,
    CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
         ELSE (cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio) END AS CoresPerCPU,
    CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
         ELSE (cpu_count / hyperthread_ratio) * ((cpu_count - hyperthread_ratio) / (cpu_count / hyperthread_ratio)) END AS TotalCores,
    cpu_count AS LogicalCPUs,
    (SELECT c_val FROM @xp WHERE c_name = 'Platform') AS Platform
  FROM sys.dm_os_sys_info
)
SELECT PhysicalCPUs, CoresPerCPU, TotalCores, LogicalCPUs,
       LTRIM(RIGHT(Platform, CHARINDEX('x', Platform) - 1)) AS CPU_Category
FROM ProcessorInfo;

-- 虛擬機修正版:hyperthread_ratio 即為每插槽核心數
WITH ProcessorInfoVM AS (
  SELECT
    cpu_count / hyperthread_ratio AS PhysicalCPUs,
    hyperthread_ratio AS CoresPerCPU,
    cpu_count AS TotalCores,
    cpu_count AS LogicalCPUs,
    (SELECT c_val FROM @xp WHERE c_name = 'Platform') AS Platform
  FROM sys.dm_os_sys_info
)
SELECT PhysicalCPUs, CoresPerCPU, TotalCores, LogicalCPUs,
       LTRIM(RIGHT(Platform, CHARINDEX('x', Platform) - 1)) AS CPU_Category
FROM ProcessorInfoVM;

二、記憶體資訊

1. 實體記憶體(xp_msver,所有版本)

CREATE TABLE #Tmp ([Index] 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. DMV 取得(2005/2008)

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

3. DMV 取得(2012+)

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

4. 詳細記憶體狀態(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;

三、磁碟資訊

SET NOCOUNT ON;
DECLARE @Result INT, @Obj INT, @Drv CHAR(1), @TotalSize VARCHAR(20), @Out INT;
DECLARE @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;
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @Obj OUT;

DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR SELECT DiskCD FROM #Disk ORDER BY DiskCD;
OPEN Cur; FETCH NEXT FROM Cur INTO @Drv;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC @Result = sp_OAMethod @Obj, 'GetDrive', @Out OUT, @Drv;
  EXEC @Result = sp_OAGetProperty @Out, 'TotalSize', @TotalSize OUT;
  UPDATE #Disk SET TotalMB = CAST(@TotalSize AS BIGINT) / @UnitMB WHERE DiskCD = @Drv;
  FETCH NEXT FROM Cur INTO @Drv;
END
CLOSE Cur; DEALLOCATE Cur;
EXEC sp_OADestroy @Obj;
EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

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)*100.0/TotalMB,6,2) AS [Used %],
       STR(FreeMB*100.0/TotalMB,6,2) AS [Free %]
FROM #Disk;
DROP TABLE #Disk;

前置條件:需啟用 Ole Automation Procedures(生產環境建議用完即關)。

四、作業系統資訊

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

WITH OSVer AS (
  SELECT 'Windows 10' AS Name, '10.0*' AS Ver 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 o.Name AS [OS Version],
       w.windows_service_pack_level AS [SP Level],
       l.LangDtl AS [Language]
FROM sys.dm_os_windows_info w
JOIN OSVer o ON w.windows_release LIKE o.Ver
JOIN #Lang l ON l.LangID = w.os_language_version;
DROP TABLE #Lang;

注意:相同版本號可能對應多種 OS(如 6.1 同時對應 Win7 與 WS2008 R2),需結合環境判斷。

五、伺服器名稱與 IP

伺服器名稱

SELECT SERVERPROPERTY('MachineName') AS MachineName;
SELECT @@SERVERNAME AS InstanceName;
SELECT HOST_NAME() AS HostName;

IP 位址(兩種方式)

方式 A:xp_cmdshell(需高權限,安全風險)

EXEC sp_configure 'show advanced options',1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE;
CREATE TABLE #IP (Line VARCHAR(200));
INSERT #IP EXEC xp_cmdshell 'ipconfig';
SELECT DISTINCT LTRIM(RTRIM(SUBSTRING(Line, CHARINDEX(':',Line)+1, 200))) AS IPv4
FROM #IP WHERE Line LIKE '%IPv4%';
DROP TABLE #IP;
EXEC sp_configure 'xp_cmdshell',0; RECONFIGURE;

方式 B:DMV(安全、無需額外權限)

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;

小結

整合上述腳本即可構建「資料庫端硬體/OS 巡檢報表」,納入日常巡檢或自動化監控,實現無代理、輕量級的基礎設施可視化。

copyright © 萬盛學電腦網 all rights reserved