查看CPU、内存、磁盘的使用情况

2019-11-21 08:40栏目:网站首页

大家假若用到数据库,平日会遇见数据库运营方面包车型客车作业,需求大家搜求原因,有成百上千是事关微型机(CPU)、内部存款和储蓄器(Memory卡塔 尔(英语:State of Qatar)、磁盘(Disk卡塔 尔(阿拉伯语:قطر‎以致操作系统的,这时候大家就必要查询他们的后生可畏部分装置和内容,
上边讲的正是什么查询它们的相干音讯。
1、(1卡塔尔获取数据库服务器CPU核数等消息(只适用于SQL 二零零六甚至上述版本数据库)
/*************************************************************************************
--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\0',
  '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 二〇一〇以致上述的本子:查看物理内部存款和储蓄器大小,已经使用的情理内部存款和储蓄器甚至还剩余的物理内存。
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卡塔 尔(英语:State of Qatar)获取数据库服务装备理内部存款和储蓄器数(适用于具备版本)
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

注意:

1:如上所示,不经常表#Language的数量此处只列了几条常用的数量,如需任何数额,参谋

2:操作系统的版本音信的多少来自

有希望现身不一致操作系统具备相符Version number值,比如Windows 7 和Windows Server 二零一零 Sportage2的Version numberd都为6.1。招致上面查询结果现身多条记下(如下所示卡塔 尔(英语:State of Qatar)。常常要酌情推断(假设临蓐服务器都为Windows服务器,可以去除Windows XP、Windows 7那类数据卡塔尔。

图片 1

选拔SQL语句得到服务器名称和IP地址 获取服务器名称:

SELECT SERVERPROPERTY('MachineName')
select @@SERVERNAME
select HOST_NAME()

获取IP地址尚可xp_cmdshell执行ipconfig命令;
--开启xp_cmdshell
exec sp_configure'show advanced options', 1
reconfigure with override
exec sp_configure'xp_cmdshell', 1
reconfigure with override
exec sp_configure'show advanced options', 0
reconfigure with override
go
begin
declare @ipline varchar(200)
declare @pos int
declare @ip varchar(40)
set nocount on
set @ip = null
if object_id('tempdb..#temp') is not null drop table #temp
create table #temp(ipline varchar(200))
insert #temp exec master..xp_cmdshell'ipconfig'
select @ipline = ipline
from #temp
where upper(ipline) like '%IPv4 地址%'--这里需求小心一下,系统不一致这里的匹配值就分化
if @ipline is not null
begin
set @pos = charindex(':',@ipline,1);
set @ip = rtrim(ltrim(substring(@ipline ,
@pos 1 ,
len(@ipline) - @pos)))
end
select distinct(rtrim(ltrim(substring(@ipline ,
@pos 1 ,
len(@ipline) - @pos)))) as ipaddress from #temp
drop table #temp
set nocount off
end
go

可是过多动静下是因为安全难题是不容许选择xp_cmdshell,能够透过查询SYS.DM_EXEC_CONNECTIONS :
SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME'))
,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'
,CLIENT_NET_ADDRESS AS 'ClientIPAddress'
FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID


地点是经过sql语句询问,能够透过(这里是二〇一六版卡塔 尔(阿拉伯语:قطر‎点击数据库-属性--能够看出不菲新闻,不过看不到物理内部存款和储蓄器和设想内部存款和储蓄器,日常在质量-内存-最大内存设置:物理内部存款和储蓄器的十分九


 

版权声明:本文由威尼斯人app发布于网站首页,转载请注明出处:查看CPU、内存、磁盘的使用情况