34,594
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Waits_of_Particular_Session')
DROP EVENT session Waits_of_Particular_Session ON SERVER;
GO
CREATE EVENT SESSION Waits_of_Particular_Session
ON SERVER
ADD EVENT sqlserver.sql_statement_starting
(
ACTION (sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle)
WHERE sqlserver.session_id =Spid
),
ADD EVENT sqlos.wait_info
(
ACTION (sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle)
WHERE sqlserver.session_id =Spid
),
ADD EVENT sqlos.wait_info_external
(
ACTION (sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle)
WHERE sqlserver.session_id =Spid
),
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle)
WHERE sqlserver.session_id =Spid
)
ADD TARGET package0.asynchronous_file_target
(SET filename=N'C:\Waits_of_Particular_Session.xel')
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, TRACK_CAUSALITY=ON)
GO
ALTER EVENT SESSION Waits_of_Particular_Session ON SERVER
STATE = START
GO
2.2 运行SELECT [GridID ,[XCoor ,[YCoor] FROM [CIAEMonitoringAndAssessment].[dbo].[Grid]
2.3
运行下面的语句,然后把结果集贴出来
SELECT
wait_type
,TSQLQuery
, sum(duration) as 'total_duration'
, sum(SignalDuration) as 'total_SignalDuration'
FROM (
SELECT
event_xml.value('(./@name)', 'varchar(1000)') as Event_Name,
event_xml.value('(./data[@name="wait_type"]/text)[1]', 'nvarchar(max)') as Wait_Type,
event_xml.value('(./data[@name="duration"]/value)[1]', 'int') as Duration,
event_xml.value ('(/event/data[@name=''signal_duration'']/value)[1]', 'BIGINT') AS SignalDuration,
event_xml.value('(./action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as TSQLQuery
FROM
(SELECT CAST(event_data AS XML) xml_event_data FROM sys.fn_xe_file_target_read_file('C:\Waits_of_Particular_Session*.xel', 'C:\Waits_of_Particular_Session*.xem', NULL, NULL)) AS event_table
CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
WHERE
event_xml.value('(./@name)', 'varchar(1000)') IN ('wait_info','wait_info_external')
) TMP
GROUP BY wait_type,TSQLQuery
按照上面的步骤做,对上面的步骤,有不明白的提出来。[/quote]不好意思,客户那边的额数据库最近一直连不上所以没法贴结果,能脸上了第一时间会贴出来的,谢谢热心解答
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Waits_of_Particular_Session')
DROP EVENT session Waits_of_Particular_Session ON SERVER;
GO
CREATE EVENT SESSION Waits_of_Particular_Session
ON SERVER
ADD EVENT sqlserver.sql_statement_starting
(
ACTION (sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle)
WHERE sqlserver.session_id =Spid
),
ADD EVENT sqlos.wait_info
(
ACTION (sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle)
WHERE sqlserver.session_id =Spid
),
ADD EVENT sqlos.wait_info_external
(
ACTION (sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle)
WHERE sqlserver.session_id =Spid
),
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.session_id,
sqlserver.sql_text,
sqlserver.plan_handle)
WHERE sqlserver.session_id =Spid
)
ADD TARGET package0.asynchronous_file_target
(SET filename=N'C:\Waits_of_Particular_Session.xel')
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, TRACK_CAUSALITY=ON)
GO
ALTER EVENT SESSION Waits_of_Particular_Session ON SERVER
STATE = START
GO
2.2 运行SELECT [GridID ,[XCoor ,[YCoor] FROM [CIAEMonitoringAndAssessment].[dbo].[Grid]
2.3
运行下面的语句,然后把结果集贴出来
SELECT
wait_type
,TSQLQuery
, sum(duration) as 'total_duration'
, sum(SignalDuration) as 'total_SignalDuration'
FROM (
SELECT
event_xml.value('(./@name)', 'varchar(1000)') as Event_Name,
event_xml.value('(./data[@name="wait_type"]/text)[1]', 'nvarchar(max)') as Wait_Type,
event_xml.value('(./data[@name="duration"]/value)[1]', 'int') as Duration,
event_xml.value ('(/event/data[@name=''signal_duration'']/value)[1]', 'BIGINT') AS SignalDuration,
event_xml.value('(./action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as TSQLQuery
FROM
(SELECT CAST(event_data AS XML) xml_event_data FROM sys.fn_xe_file_target_read_file('C:\Waits_of_Particular_Session*.xel', 'C:\Waits_of_Particular_Session*.xem', NULL, NULL)) AS event_table
CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
WHERE
event_xml.value('(./@name)', 'varchar(1000)') IN ('wait_info','wait_info_external')
) TMP
GROUP BY wait_type,TSQLQuery
按照上面的步骤做,对上面的步骤,有不明白的提出来。