MSSQL Sever 性能,磁盘等监控建立(二) 服务器侧内存使用量,CPU压力查询

这里简单查询了数据库服务器当前内存使用情况以及CPU等待百分比情况. 虽然signal (cpu) waits 对于分析CPU压力来说不是绝对唯一的参数.但是也是一个不错的参考值. 欢迎有类似需求的朋友指正讨论

 

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE ID = OBJECT_ID('Tempdb..#Temp1_table'))
DROP TABLE #Temp1_table
GO

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE ID = OBJECT_ID('Tempdb..Temp_Memory_cpu_info'))
DROP TABLE Tempdb..Temp_Memory_cpu_info
GO

SELECT @@SERVERNAME AS [ServerName],
       CONVERT(FLOAT,ROUND(total_physical_memory_kb/1048576.0,1),2) AS [TotalSize(GB)],
       CONVERT(FLOAT,ROUND(available_physical_memory_kb/1048576.0,2)) AS [FREESize(GB)],
       100- CONVERT(FLOAT,ROUND((available_physical_memory_kb/1048576.0)/(total_physical_memory_kb/1048576.0)*100,2)) as [USAGE(%)],
       GETDATE() as CheckTime
INTO #Temp1_table
FROM sys.dm_os_sys_memory



select D.ServerName,D.[TotalSize(GB)],D.[FREESize(GB)],D.[USAGE(%)],D.signal_wait_time_ms,D.[%signal (cpu) waits],D.resource_wait_time_ms,
       D.[%resource waits],D.CheckTime
into Tempdb..Temp_Memory_cpu_info
from
(
select T.ServerName,T.[TotalSize(GB)],T.[FREESize(GB)],T.[USAGE(%)],C.signal_wait_time_ms,C.[%signal (cpu) waits],
       C.resource_wait_time_ms,C.[%resource waits],C.CheckTime
from #Temp1_table as T left join
(
SELECT @@SERVERNAME AS [ServerName],
SUM(signal_wait_time_ms) AS 'signal_wait_time_ms',
CAST(100.0 * SUM(signal_wait_time_ms)/SUM (wait_time_ms)AS NUMERIC(20,2)) AS '%signal (cpu) waits',
SUM(wait_time_ms - signal_wait_time_ms) AS 'resource_wait_time_ms',
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) /SUM (wait_time_ms) AS NUMERIC(20,2)) AS '%resource waits',
GETDATE() as CheckTime
FROM sys.dm_os_wait_stats) as C on T.ServerName = C.ServerName) as D


GO


select * from Tempdb..Temp_Memory_cpu_info

DROP TABLE #Temp1_table
DROP TABLE Tempdb..Temp_Memory_cpu_info
GO