블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28    
  • 35,390total
  • 16today
  • 2yesterday

'2019/02'에 해당되는 글 2건

  1. 2019.02.19 Extended Event Data Query로 Select
  2. 2019.02.19 Postgresql 모니터링
2019.02.19 15:27 SQL SERVER

아주 괜챃은 방법이다.


Extended Event Data 조회

http://www.sqldoubleg.com/2016/05/31/extended-events-file-target-reader/


CREATE EVENT SESSION [ALL_SQL_SAVE] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([object_name]<>N'sp_reset_connection')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'F:\temp\ALL_QUERY.xel',max_file_size=(500),max_rollover_files=(30))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO



IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent
SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
INTO #ExEvent
from sys.fn_xe_file_target_read_file ( 'f:\Temp\AAA.xel',null, null, null ) ;




(39266개 행이 영향을 받음)
SELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId
, ISNULL(t_action.event_name, t_data.event_name) AS event_name
, t_action.[client_hostname], t_action.[collect_system_time], t_action.[client_app_name], t_action.[session_id]
, t_data.[cpu_time], t_data.[duration], t_data.[logical_reads], t_data.[object_name], t_data.[physical_reads], t_data.[result], t_data.[row_count], t_data.[statement], t_data.[writes]
FROM (
SELECT RowId, event_name, [client_hostname], [collect_system_time], [client_app_name], [session_id]
FROM (
SELECT RowId
, event_name
, T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
, T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
FROM #ExEvent
CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
IN ('client_hostname', 'collect_system_time', 'client_app_name', 'session_id')
) AS SourceTable
PIVOT(
MAX(att_value)
FOR att_name IN ([client_hostname], [collect_system_time], [client_app_name], [session_id])
) AS PivotTable
) AS t_action

-- Full outer because it might be no events selected only the payload
FULL OUTER JOIN (
SELECT RowId, event_name, [cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes]
FROM (
SELECT RowId
, event_name
, T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)') AS att_name
, T3.Loc.query('.').value('(/data/value)[1]', 'varchar(max)') AS att_value

FROM #ExEvent
CROSS APPLY event_data.nodes('/event/data') as T3(Loc)
WHERE T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)')
IN ( 'cpu_time', 'duration', 'logical_reads', 'object_name', 'physical_reads', 'result', 'row_count', 'statement', 'writes')
) AS SourceTable
PIVOT (
MAX(att_value)
FOR att_name IN ([cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes])
) AS PivotTable

) AS t_data
ON t_data.RowId = t_action.RowId




posted by bedbmsguru
2019.02.19 13:13 Zabbix

Linux Version 가능


http://cavaliercoder.com/libzbxpgsql/

'Zabbix' 카테고리의 다른 글

Windows Zabbix Agent 설치  (0) 2019.05.30
Postgresql 모니터링  (0) 2019.02.19
Linux DISK IO 모니터링  (0) 2018.11.30
zabbix Agent 설치  (0) 2018.10.26
SAN Switch 모니터링 적용  (0) 2018.10.26
brocade SAN fc port zabbix 모니터링  (0) 2018.10.26
posted by bedbmsguru