' 파라미터는 데이터베이스 이름
Dim oArgs
Set oArgs = WScript.Arguments

If oArgs.Count < 1 Then
WScript.Quit -1
End If

Dim dbcon, strCon, strDB, strSQL
strDB = oArgs(0)

Set dbcon = createobject("adodb.connection")
strCon = "provider=sqloledb;Data Source=localhost;Initial Catalog="&strDB&";Integrated Security=SSPI"

dbcon.open strCon
strSQL ="SELECT L.request_session_id AS SPID,DB_NAME(L.resource_database_id) AS DatabaseName,O.Name AS LockedObjectName,ST.text AS SqlStatementText,ES.login_name AS LoginName,ES.host_name AS HostName FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id() ORDER BY L.request_session_id"

Set Result = dbcon.execute(strSQL)

WScript.Echo "SPID | DatabaseName | LockedObjectName | SqlStatementText | LoginName | HostName"
Wscript.Echo "================================================================================"

Do while not Result.eof
strROW1 = Result(0)
strROW2 = Result(1)
strROW3 = Result(2)
strROW4 = Result(3)
strROW5 = Result(4)
strROW6 = Result(5)

If IsNull(strROW1) Then
strROW1 = "NULL"
End If
If IsNull(strROW2) Then
strROW2 = "NULL"
End If
If IsNull(strROW3) Then
strROW3 = "NULL"
End If
If IsNull(strROW4) Then
strROW4 = "NULL"
End If
If IsNull(strROW5) Then
strROW5 = "NULL"
End If
If IsNull(strROW6) Then
strROW6 = "NULL"
End If

WScript.Echo strROW1 & " | " & strROW2 & " | " & strROW3 & " | " & strROW4 & " | " & strROW5 & " | " & strROW6
Wscript.Echo "--------------------------------------------------------------------------------"

Result.movenext
loop

set Result = NOTHING
dbcon.close

' 성능 카운터 리스트 쿼리
SELECT ObjectName, CounterName, MultiInstanceInd FROM vPerformanceRule ORDER BY ObjectName

' Windows 컴퓨터 리스트 쿼리
SELECT Name FROM dbo.vManagedEntity WHERE ManagedEntityTypeRowId = '22' ORDER BY Name

' 성능 정보 쿼리 (데이터웨어하우스)
EXEC SP_PerfViewDaily '2009-02-01', '2009-02-20', 'Processor', '% Processor Time', 'Server1'

----------------------------------------------------------------------------------------------------------

CREATE PROCEDURE SP_PerfViewDaily
@StartDate DATETIME,
@EndDate DATETIME,
@ObjectName NCHAR(30),
@CounterName NCHAR(30),
@ServerName NCHAR(30)
AS
SELECT    
Dateadd (HH, 9, vPerf.DateTime) AS DateTime ,
vPerf.SampleCount,
Round (vPerf.AverageValue, 2) AS Average,
Round (vPerf.MinValue, 2) AS Minimum,
Round (vPerf.MaxValue, 2) AS Maximum,
Round (vPerf.StandardDeviation, 2) AS StandardDeviation,
vPerformanceRuleInstance.InstanceName,
vManagedEntity.Path AS ServerName,
vPerformanceRule.ObjectName,
vPerformanceRule.CounterName
FROM Perf.vPerfdaily AS vPerf INNER JOIN
vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE vPerf.DateTime >= @StartDate
AND vPerf.DateTime < @EndDate
AND (vPerformanceRule.ObjectName IN (@ObjectName))
AND (vPerformanceRule.CounterName IN (@CounterName))
/* AND (vPerformanceRuleInstance.InstanceName IN ('')) */
AND (vManagedEntity.Path IN (@ServerName))
ORDER BY  DateTime
GO