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

最新推荐文章于 2024-11-18 21:40:13 发布

.NET跨平台

最新推荐文章于 2024-11-18 21:40:13 发布

阅读量859

点赞数 23

分类专栏： SqlServer数据库文章标签：服务器 sql 数据库

本文链接：https://blog.youkuaiyun.com/WuLex/article/details/125524374

版权

SqlServer数据库专栏收录该内容

196 篇文章

订阅专栏

在SQL SERVER中如何通过SQL语句获取服务器硬件和系统信息呢？下面介绍一下如何通过SQL语句获取处理器(CPU)、内存（Memory）、磁盘（Disk）以及操作系统相关信息。如有不足和遗漏，敬请补充。谢谢！

一：查看数据库服务器CPU的信息

---SQL 获取数据库服务器的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](0) NULL
    ,[int_val] [float] NULL
    ,[c_val] [varchar](8) 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]) - ) AS [cpu_category]

FROM [ProcessorInfo]

---查看虚拟机CPU信息

DECLARE @xp_msver TABLE (
    [idx] [int] NULL
    ,[c_name] [varchar](0) NULL
    ,[int_val] [float] NULL
    ,[c_val] [varchar](8) 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]) - ) AS [cpu_category]
FROM [ProcessorInfo]

二：查看数据库服务器内存的信息

能否通过SQL语句获取服务器的物理内存大小？内存条型号？虚拟内存大小？内存使用情况？目前我所知道的只能通过SQL语句获取服务器物理内存大小，内存的使用情况。至于内存条型号，系统虚拟内存大小，暂时好像还无法通过SQL语句获取。

查看服务器的物理内存情况

如下所示，从sys.dm_os_sys_info里面获取的physical_memory_in_bytes 或physical_memory_kb 的值总是低于实际物理内存。暂时不清楚具体原因（还未查到相关资料），所以计算大小有出入，要获取实际的物理内存，就必须借助CEILING函数。
在这里插入图片描述

--SQL 获取数据库服务器物理内存数(适用于所有版本)
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/24 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*0/24/24/24)    AS [Physical Memory Size]
FROM sys.dm_os_sys_info  OPTION (RECOMPILE) 

SELECT physical_memory_in_bytes*0/24/24/24 , physical_memory_in_bytes AS [Physical Memory Size]
FROM sys.dm_os_sys_info  OPTION (RECOMPILE)

---SQL 3：适用于SQL Server 20 到 SQL Server 20
SELECT CEILING(physical_memory_kb*0/24/24) AS [Physical Memory Size]
FROM sys.dm_os_sys_info  OPTION (RECOMPILE);

---SQL 4：适用于SQL Server 2008以及以上的版本:查看物理内存大小，已经使用的物理内存以及还剩下的物理内存。
SELECT  CEILING(total_physical_memory_kb * 0 / 24 / 24)    AS [Physical Memory Size] 
       ,CAST(available_physical_memory_kb * 0 / 24 / 24 
                                              AS DECIMAL(8, 4)) AS [Unused Physical Memory]
       ,CAST(( total_physical_memory_kb - available_physical_memory_kb ) * 0
        / 24 / 24 AS DECIMAL(8, 4))                            AS [Used Physical Memory]
       ,CAST(system_cache_kb*0 / 24/24 AS DECIMAL(8, 4))  AS [System Cache Size]
FROM    sys.dm_os_sys_memory

三：查看数据库服务器硬盘的信息

如下所示，我们可以通过下面脚本获取服务器的各个磁盘的使用情况。但是无法获取磁盘的型号、转速之类的信息。

-- 设置 NOCOUNT 为 ON，防止存储过程在每次执行时返回行数消息
SET NOCOUNT ON;

-- 声明变量
DECLARE @Result     INT;            -- 用于存储操作结果
DECLARE @objectInfo INT;            -- 用于存储文件系统对象
DECLARE @DriveInfo  CHAR(2);        -- 用于存储磁盘的驱动器字母（如 C:，D:）
DECLARE @TotalSize  VARCHAR(20);    -- 用于存储磁盘的总容量
DECLARE @OutDrive   INT;            -- 用于存储驱动器对象
DECLARE @UnitMB     BIGINT;         -- 存储转换单位，1MB=1024KB
DECLARE @FreeRat     FLOAT;         -- 存储空闲磁盘的百分比

-- 设置磁盘容量换算单位为MB，48576 KB = 1MB
SET @UnitMB = 48576;

-- 创建临时表 #DiskCapacity 用于存储服务器磁盘信息（驱动器字母，空闲空间，总空间）
CREATE TABLE #DiskCapacity
(
    [DiskCD]     CHAR(2),   -- 存储磁盘驱动器字母（如 C:、D:）
    FreeSize     INT,        -- 存储磁盘空闲空间大小（以KB为单位）
    TotalSize    INT         -- 存储磁盘总空间大小（以KB为单位）
);

-- 从系统存储过程 xp_fixeddrives 获取磁盘的驱动器信息并插入临时表中
-- xp_fixeddrives 会返回每个固定磁盘的驱动器字母及其空闲空间大小
INSERT #DiskCapacity([DiskCD], FreeSize) 
EXEC master.dbo.xp_fixeddrives;

-- 开启高级选项和 Ole Automation 扩展选项
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE WITH OVERRIDE;

-- 创建 COM 对象 Scripting.FileSystemObject，用于获取磁盘信息
EXEC @Result = master.sys.sp_OACreate 'Scripting.FileSystemObject', @objectInfo OUT;

-- 定义一个游标 CR_DiskInfo，用于遍历每个磁盘驱动器
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;

    -- 更新临时表 #DiskCapacity，转换总空间为MB并存入表中
    UPDATE #DiskCapacity
    SET TotalSize = @TotalSize / @UnitMB  -- 总空间转换为MB
    WHERE DiskCD = @DriveInfo;

    -- 获取下一个磁盘驱动器的信息
    FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo;
END

-- 关闭游标
CLOSE CR_DiskInfo;

-- 释放游标资源
DEALLOCATE CR_DiskInfo;

-- 销毁文件系统对象
EXEC @Result = sp_OADestroy @objectInfo;

-- 关闭高级选项和 Ole Automation 扩展选项
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;

-- 查询临时表 #DiskCapacity，计算磁盘的总容量、已使用空间、空闲空间以及使用率和空闲率，并格式化显示
SELECT DiskCD AS [Drive CD],  -- 显示磁盘驱动器字母
       STR(TotalSize * 0.001024, 6, 2) AS [Total Size(GB)],  -- 总容量（GB），转换为GB
       STR((TotalSize - FreeSize) * 0.001024, 6, 2) AS [Used Space(GB)],  -- 已使用容量（GB）
       STR(FreeSize * 0.001024, 6, 2) AS [Free Space(GB)],  -- 空闲空间（GB）
       STR((TotalSize - FreeSize) * 100.0 / TotalSize, 6, 2) AS [Used Rate(%)] ,  -- 使用率（%）
       STR((FreeSize * 100.0) / TotalSize, 6, 2) AS [Free Rate(%)]  -- 空闲率（%）
FROM #DiskCapacity;

-- 删除临时表 #DiskCapacity
DROP TABLE #DiskCapacity;

sql解释：

临时表 #DiskCapacity:
- 用于存储磁盘的信息（驱动器字母、空闲空间、总空间）。
xp_fixeddrives:
- SQL Server 提供的系统存储过程，用于获取磁盘的驱动器字母和空闲空间。
Scripting.FileSystemObject:
- 创建一个 COM 对象，用于获取磁盘的详细信息，如磁盘总容量。
游标:
- 游标用于遍历临时表中的每个磁盘驱动器字母，并使用 FileSystemObject 获取对应的磁盘总容量。
空间计算:
- 将磁盘的总空间转换为MB，并计算已使用空间、空闲空间以及使用率和空闲率。
格式化输出:
- 输出最终的磁盘信息时，使用 STR() 函数格式化数据，确保以 GB 和百分比形式显示。
清理:
- 使用 DROP TABLE 删除临时表，释放资源。

四：查看操作系统信息

通过下面SQL语句，我们可以查看操作系统版本、补丁、语言等信息

以下是对你提供的 SQL 脚本的详细注释说明：

-- 创建临时表 #Language，用于存储语言版本和对应的语言详细信息
CREATE TABLE #Language
(
    [LanguageDtl] NVARCHAR(64),   -- 存储语言详情（例如：English - United States）
    [os_language_version] INT     -- 存储与语言相关的操作系统语言版本号（例如：33）
);

-- 向临时表 #Language 插入多种语言及其对应的操作系统语言版本号
INSERT INTO #Language
SELECT 'English - United States', 33 UNION ALL
SELECT 'English - United Kingdom', 2057 UNION ALL
SELECT 'Chinese - People''s Republic of China', 2052 UNION ALL
SELECT 'Chinese - Singapore', 40 UNION ALL
SELECT 'Chinese - Taiwan', 28 UNION ALL
SELECT 'Chinese - Hong Kong SAR', 3076 UNION ALL
SELECT 'Chinese - Macao SAR', 54;

-- 定义一个公用表表达式 (CTE)，命名为 SystemVersion，用于存储不同 Windows 版本及其对应的版本号
WITH SystemVersion(SystemInfo, ReleaseNo)
AS
(
    -- 插入多个 Windows 版本及其对应的内部版本号
    SELECT 'Windows ', '.0*'
    UNION ALL
    SELECT 'Windows Server 20 Technical Preview', '.0*'
    UNION ALL
    SELECT 'Windows 8.1', '6.3*'
    UNION ALL
    SELECT 'Windows Server 20 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.0'
    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'
)

-- 使用内部查询从 sys.dm_os_windows_info 系统视图获取当前系统的 Windows 信息，
-- 然后通过 JOIN 操作与定义的 SystemVersion CTE 和临时表 #Language 连接。
SELECT s.SystemInfo,                     -- Windows 版本信息（如 Windows 7, Windows 10 等）
       w.windows_service_pack_level,     -- 当前系统的服务包级别
       l.LanguageDtl                     -- 系统语言详细信息（如 English - United States 等）
FROM sys.dm_os_windows_info w           -- sys.dm_os_windows_info 系统视图：提供操作系统的基础信息
INNER JOIN SystemVersion s              -- 内连接到 CTE SystemVersion，根据版本号匹配 Windows 版本
    ON w.windows_release = s.ReleaseNo  -- 将视图中的 ReleaseNo 与 CTE 中的 ReleaseNo 进行匹配
INNER JOIN #Language l                 -- 内连接到临时表 #Language，根据语言版本号匹配语言
    ON l.os_language_version = w.os_language_version; -- 匹配操作系统语言版本号

-- 删除临时表 #Language，释放资源
DROP TABLE #Language;

sql解释：

创建临时表 #Language:
- 临时表用于存储系统语言信息。包含两列：LanguageDtl 存储语言的描述（如 English - United States），os_language_version 存储与该语言对应的操作系统语言版本号（如 33 对应 English - United States）。
插入多种语言版本信息:
- 使用 INSERT INTO 将不同语言及其对应的版本号插入到临时表 #Language 中。
定义公用表表达式 (CTE) SystemVersion:
- SystemVersion CTE 用于列出不同版本的 Windows 操作系统及其版本号（如 Windows 10 对应 10.0、Windows Server 2008 对应 6.0 等）。这个 CTE 列出了一些常见的 Windows 操作系统和其版本号，便于后续查询时使用。
sys.dm_os_windows_info 视图:
- 这是 SQL Server 中的动态管理视图（DMV），提供有关操作系统的详细信息。windows_release 和 os_language_version 是其中的字段，用来获取当前操作系统的版本号和语言版本号。
连接操作:
- sys.dm_os_windows_info 视图与 SystemVersion CTE 和临时表 #Language 通过 windows_release 和 os_language_version 字段进行内连接（INNER JOIN）。查询结果包括：
  - SystemInfo: 操作系统的版本信息（如 Windows 7, Windows 10 等）
  - windows_service_pack_level: 操作系统的服务包级别（如 Service Pack 1, Service Pack 2 等）
  - LanguageDtl: 系统语言的详细描述（如 English - United States, Chinese - Taiwan 等）
清理:
- 最后，使用 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.导致下面查询结果出现多条记录（如下所示）。一般要酌情判断（如果生产服务器都为Windows服务器，可以剔除Windows XP、Windows 7这类数据）。
在这里插入图片描述