34,587
社区成员
发帖
与我相关
我的任务
分享
insert into #ASB_SQLCountValueInfo(CounterDateTime, BlockedCounterValue,UserConnectionCounterValue)
exec('select CounterDateTime,ProcessblockedValue,UserConnectionValue from
(
select A.CounterDateTime,
ProcessblockedValue=MAX(case when T.CounterName=''Processes blocked'' then A.CounterValue ELSE 0 END),
UserConnectionValue=MAX(case when T.CounterName=''User Connections'' then A.CounterValue ELSE 0 END)
from ASBSQL_PerformanceCounterDetailM'+@NowMonth+' A
inner join ASBSQL_PerformanceCounterList T
on A.CounterID=T.CounterID
where T.ServerName='''+@serverName+'''
and T.CounterName in (''Processes blocked'',''User Connections'')
group by A.CounterDateTime
) B
where B.ProcessblockedValue>0
order by CounterDateTime desc'
)
EXEC('WITH CTEUser
AS
(
SELECT CounterID AS UserConnectionCounterID
,CounterDateTime AS UserConnectionDateTime
,CounterValue AS UserConnectionCounterValue
,ROW_NUMBER()OVER(ORDER BY CounterDateTime DESC) AS RN
FROM dbo.ASBSQL_PerformanceCounterDetailM6 T
WHERE CounterID IN (
SELECT CounterID
FROM [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
WHERE ServerName = ''cnshjssql14.ad4.ad.alcatel.com''
AND CounterName = ''User Connections'' )
AND T.CounterValue > 0
AND DATEDIFF(MINUTE , CounterDateTime , GETDATE()) >= 30
),CTEBlocked
AS
(
SELECT CounterID AS BlockedCounterID
,CounterDateTime AS BlockedCounterDateTime
,CounterValue AS BlockedCounterValue
,ROW_NUMBER()OVER(ORDER BY CounterDateTime DESC) AS RN
FROM dbo.ASBSQL_PerformanceCounterDetailM6 T
WHERE CounterID IN (
SELECT CounterID
FROM [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
WHERE ServerName = ''cnshjssql14.ad4.ad.alcatel.com''
AND CounterName = ''Processes blocked'' )
AND ISNULL(T.CounterValue , 0) > 0
AND DATEDIFF(MINUTE , CounterDateTime , GETDATE()) >= 30
)
SELECT a.UserConnectionCounterID
,a.UserConnectionDateTime
,a.UserConnectionCounterValue
,b.BlockedCounterID
,b.BlockedCounterDateTime
,b.BlockedCounterValue
FROM CTEUser AS a
INNER JOIN CTEBlocked AS b ON a.RN=b.RN
WHERE (b.BlockedCounterValue/a.UserConnectionCounterValue)*100>20 ')
exec ('
SELECT UserConnectionCounterID,UserConnectionDateTime,UserConnectionCounterValue,BlockedCounterValue
FROM
(SELECT T1.CounterID AS UserConnectionCounterID ,
T1.CounterDateTime AS UserConnectionDateTime ,
MAX(CASE WHEN T2.CounterName = ''User Connections'' THEN T1.CounterValue
ELSE 0
END) AS UserConnectionCounterValue ,
MAX(CASE WHEN T2.CounterName = ''Processes blocked''
THEN T1.CounterValue
ELSE 0
END) AS BlockedCounterValue
FROM dbo.ASBSQL_PerformanceCounterDetailM6 T1
INNER JOIN ( SELECT DISTINCT
CounterID ,
CounterName
FROM [SQLMonitor].[dbo].[ASBSQL_PerformanceCounterList]
WHERE ServerName = ''cnshjssql14.ad4.ad.alcatel.com''
AND CounterName IN ( ''User Connections'',
''Processes blocked'' )
) AS T2 ON T1.CounterID = T2.CounterID
WHERE T1.CounterValue > 0
AND DATEDIFF(MINUTE, T1.CounterDateTime, GETDATE()) >= 30
GROUP BY T1.CounterID ,
T1.CounterDateTime
) AS T
WHERE (BlockedCounterValue/ISNULL(NULLIF(UserConnectionCounterValue,0),1))*100>20
ORDER BY T1.UserConnectionDateTime DESC;')