用SQL Server 监控 OS Server 的Task Management信息
用SQL Server 监控 OS Server 的Task Management信息--原文来自于http://www.databasejournal.com/features/mssql/article.php/3562586/Monitor-CPU-Usage-of-All-Running-Processes---Part-II.htm
一： 监控程序部分
1. 在C 盘创一个文件夹：如 C:\Monitorprocess
2. 创建 C:\monitorprocess\ Servers.txt 用来记录要监控的服务器：内容如下
Server1
Server2
DB_Server1
DB_Server2
….
3. 在用记事本写一个C:\monitorprocess\Listremoteprocess2.vbs程序， 内容如下
'Objective: To Find the CPU usage of each process that are running on a remote machine
'Created by : MAK
'Created Date: Nov 2, 2005
'Syntax: cscript Listremoteprocess2.vbs machinename
'Example: cscript Listremoteprocess2.vbs MyMachine
Set objArgs = WScript.Arguments
strComputer =objArgs(0)
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colProcesses = objWMIService.ExecQuery("Select * from Win32_PerfFormattedData_PerfProc_Process",,48)
' wscript.echo "Computer Name" & "," & "Process Name" & ","& "CPU Usage"
For Each objItem in colProcesses
if objItem.Name <> "Idle" and objItem.Name <> "_Total" then
wscript.echo strcomputer & "," & objItem.Name & ","& objItem.PercentProcessorTime
end if
Next
4. 创建 C:\monitorprocess\ Listaprocessremote.bat ， 内容如下
REM Objective: To execute the Listremoteprocess2.vbsfor every server listed in servers.txt
REM Created by: MAK
REM Created by" Nov 2, 2005
REM Usage: Listaprocessremote.bat Allservers.csv
dir %1
if %errorlevel% == 0 goto process
goto delfile
:delfile
del %1
goto process
:process
for /f "tokens=1 delims=&" %%i in (c:\Monitorprocess\Servers.txt) do cscript/nologo c:\Monitorprocess\Listremoteprocess2.vbs %%i >>%1
goto end
:end
5. 测试 创建的监控程序,cmd 进入dos,
cd c:\Monitorprocess
Listaprocessremote.bat myserverprocess.csvhttp://p.blog.csdn.net/images/p_blog_csdn_net/zwxrain/EntryImages/20090918/2009-9-18%2016-33-47.png二：确认正常通过测试过，我后开始进入数据库的设置部分
1. 创建数据库，用户及存放监控数据的表
create database MonitorProcessesgouse MonitorProcessesgoCreate table Processes (id int identity (1,1) not null,ServerName varchar(128),ProcessName varchar(256),CPU_Usage int not null,Time datetime constraint currentdate default getdate())goCreate view Processes_view asselect ServerName,ProcessName, CPU_Usage from Processesgouse mastergosp_addlogin 'procuser','your_password','MonitorProcesses'gouse MonitorProcessesgosp_adduser 'procuser'gosp_addrolemember 'db_datareader','procuser'gosp_addrolemember 'db_datawriter','procuser'go
2. 创建Job 来执行监控程序，并收集监控数据
http://p.blog.csdn.net/images/p_blog_csdn_net/zwxrain/EntryImages/20090918/2009-9-18%2016-23-23.png
1）设置Monitor 程序
http://p.blog.csdn.net/images/p_blog_csdn_net/zwxrain/EntryImages/20090918/2009-9-18%2016-27-11.png
Type: Operation System Command(CmdExec)
Command:
cd C:\Monitorprocess
Listaprocessremote.bat myserverprocess.csv
2. 将监控数据导入数据库表
http://p.blog.csdn.net/images/p_blog_csdn_net/zwxrain/EntryImages/20090918/2009-9-18%2016-28-26.png
Type:Transact-SQL Script(TSQL)
Command:
use MonitorProcesses
go
BULK INSERT MonitorProcesses.dbo.Processes_view
FROM 'c:\Monitorprocess\myserverprocess.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
3. 设定Job 的Schedule 来定时执行监控收集数据。
4. 查询收集的监控数据
select * from Processesselect * from Processes where processname like '%SQLServr'Select * from Processes where servername ='ATDBQA'
页:
[1]