用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.csv ? ?

二：確認正常通過測試過，我后開始進入數據庫的設置部分

1. 創建數據庫，用戶及存放監控數據的表

?

create database MonitorProcesses go use MonitorProcesses go Create 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()) go Create view Processes_view as select ServerName,ProcessName, CPU_Usage from Processes go use master go sp_addlogin 'procuser','your_password','MonitorProcesses' go use MonitorProcesses go sp_adduser 'procuser' go sp_addrolemember 'db_datareader','procuser' go sp_addrolemember 'db_datawriter','procuser' go

?

?

2. 創建Job 來執行監控程序，并收集監控數據

?

?

?

1）設置Monitor 程序

?

?

Type: Operation System Command(CmdExec)

Command:

cd C:/Monitorprocess

Listaprocessremote.bat myserverprocess.csv

?

2. 將監控數據導入數據庫表

?

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 Processes select * from Processes where processname like '%SQLServr' Select * from Processes where servername ='ATDBQA'

?

?

轉載于:https://www.cnblogs.com/sesexxoo/archive/2009/09/18/6190178.html

總結

以上是生活随笔為你收集整理的用SQL Server 监控 OS Server 的Task Management信息的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。