SQL SERVER中获服务器硬件和系统信息SQL语句

.NET跨平台

于 2024-11-13 16:26:53 发布

阅读量500

点赞数 11

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

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

版权

SqlServer数据库专栏收录该内容

196 篇文章

订阅专栏

在 SQL Server 中，你可以通过查询系统视图、函数和扩展的管理视图来获取硬件和系统信息。以下是一些常见的 SQL 查询，它们可以提供有关 SQL Server 实例、操作系统和硬件的相关信息。

1. 获取 SQL Server 实例版本、操作系统版本和架构信息

SELECT 
    SERVERPROPERTY('ProductVersion') AS SQLServerVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EngineEdition') AS EngineEdition,
    SERVERPROPERTY('MachineName') AS ServerName,
    SERVERPROPERTY('IsClustered') AS IsClustered,
    SERVERPROPERTY('MaxDop') AS MaxDegreeOfParallelism,
    SERVERPROPERTY('Collation') AS Collation,
    SERVERPROPERTY('Platform') AS Platform,
    SERVERPROPERTY('OSVersion') AS OSVersion;

2. 获取操作系统的详细信息

SELECT 
    sqlserver_start_time AS SQLServerStartTime,
    cpu_count AS CPUCount,
    physical_memory_in_bytes/1048576 AS PhysicalMemoryMB,
    max_workers_count AS MaxWorkerThreads,
    process_physical_memory_low AS IsMemoryLow
FROM sys.dm_os_sys_info;

3. 获取磁盘空间信息

EXEC xp_fixeddrives;

4. 获取当前运行的 SQL Server 配置

EXEC sp_configure;

5. 获取服务器硬件信息

SELECT 
    cpu_count AS CPUCount,
    hyperthread_ratio AS HyperThreadRatio,
    physical_memory_in_bytes / 1048576 AS PhysicalMemoryMB,
    virtual_memory_in_bytes / 1048576 AS VirtualMemoryMB
FROM sys.dm_os_sys_info;

6. 获取SQL Server硬件性能指标

SELECT 
    logical_cpu_count,
    physical_memory_in_bytes / 1048576 AS TotalPhysicalMemoryMB,
    virtual_memory_in_bytes / 1048576 AS TotalVirtualMemoryMB,
    sql_memory_model_desc AS MemoryModel,
    sql_memory_model AS MemoryModelId
FROM sys.dm_os_sys_info;

这些 SQL 查询通过系统管理视图（如 sys.dm_os_sys_info）和内置函数（如 SERVERPROPERTY）提供了硬件、操作系统和 SQL Server 实例的详细信息。你可以根据自己的需求调整查询，进一步获取具体的信息。

编写SQL语句来获取SQL Server服务器的硬件和系统信息

-- 1. CPU信息
SELECT cpu_count AS [逻辑CPU数量],
       hyperthread_ratio AS [处理器核心数],
       cpu_count/hyperthread_ratio AS [物理CPU数量],
       physical_memory_kb/1024/1024 AS [物理内存(GB)]
FROM sys.dm_os_sys_info;

-- 2. 操作系统信息
SELECT windows_release AS [Windows版本],
       windows_service_pack_level AS [Service Pack],
       windows_sku AS [Windows版本类型],
       os_language_version AS [系统语言版本]
FROM sys.dm_os_windows_info;

-- 3. SQL Server实例信息
SELECT @@VERSION AS [SQL Server版本],
       SERVERPROPERTY('Edition') AS [版本],
       SERVERPROPERTY('ProductLevel') AS [补丁级别],
       SERVERPROPERTY('ProductVersion') AS [产品版本];

-- 4. 磁盘空间使用情况
SELECT DISTINCT
    vs.volume_mount_point AS [挂载点],
    vs.logical_volume_name AS [逻辑卷名],
    vs.total_bytes/1024/1024/1024 AS [总容量(GB)],
    vs.available_bytes/1024/1024/1024 AS [可用空间(GB)],
    CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) * 100 AS DECIMAL(5,2)) AS [可用空间百分比]
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs;

-- 5. 内存使用情况
SELECT 
    (total_physical_memory_kb/1024/1024) AS [系统总物理内存(GB)],
    (available_physical_memory_kb/1024/1024) AS [系统可用物理内存(GB)],
    (total_page_file_kb/1024/1024) AS [系统页面文件总大小(GB)],
    (available_page_file_kb/1024/1024) AS [系统可用页面文件(GB)],
    system_memory_state_desc AS [系统内存状态]
FROM sys.dm_os_sys_memory;

-- 6. SQL Server内存使用
SELECT 
    (committed_kb/1024) AS [SQL Server已提交内存(MB)],
    (committed_target_kb/1024) AS [SQL Server目标内存(MB)],
    (visible_target_kb/1024) AS [可见目标内存(MB)]
FROM sys.dm_os_sys_info;

-- 7. 网络配置信息
SELECT 
    local_net_address AS [本地IP地址],
    local_tcp_port AS [本地TCP端口]
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;