您的位置:金沙游乐场85155 > 大数据库 > sql server 运维时CPU,内存,操作系统等信息查询(

sql server 运维时CPU,内存,操作系统等信息查询(

发布时间:2019-11-13 19:07编辑:大数据库浏览(168)

    我们只要用到数据库,一般会遇到数据库运维方面的事情,需要我们寻找原因,有很多是关乎处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统的,这时我们就需要查询他们的一些设置和内容,
    下面讲的就是如何查询它们的相关信息。
    1、(1)获取数据库服务器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)

    --查看内存信息
    SELECT CEILING(total_physical_memory_kb * 1.0 / 1024 / 1024)  AS [物理内存大小]
        ,CAST(available_physical_memory_kb * 1.0 / 1024 / 1024
                           AS DECIMAL(8, 4)) AS [未使用的内存大小]
        ,CAST(( total_physical_memory_kb - available_physical_memory_kb ) * 1.0
        / 1024 / 1024 AS DECIMAL(8, 4))              AS [已使用的内存大小]
        ,CAST(system_cache_kb*1.0 / 1024/1024 AS DECIMAL(8, 4)) AS [系统缓存大小]
    FROM  sys.dm_os_sys_memory

    (2) 获取数据库服务器CPU核数(适用于所有版本

    --查看CPU的型号
    EXEC xp_instance_regread
      'HKEY_LOCAL_MACHINE',
      'HARDWAREDESCRIPTIONSystemCentralProcessor',
      'ProcessorNameString';

    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

    --查询每个磁盘的信息,包括已经使用的和未使用的信息
    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;

    2:在老外博客中看到一个计算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函数。

    1、(1)适用于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

    (2)获取数据库服务器物理内存数(适用于所有版本)
    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

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

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

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

    注意:

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:sql server 运维时CPU,内存,操作系统等信息查询(

    关键词: