December 30, 2014 at 10:49 am
We have Spotlight running and causing PREEMPTIVE_XE_GETTARGETSTATE on
this query below, I was trying to find out what XE_GETTARGETSTATE means, but cant find anything? I know its not cooperative, but what XE_GETTARGETSTATE means? I would appreciate a hint. Thank you in advance.
/* (inserted by Ignite)
Character Range: 308 to 592
Waiting on statement:
SELECT cast(target_data as xml) as target_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
ON t.event_session_address = s.address
WHERE s.name = N'SpotlightResponseTimeWaitData_sqldba1_3843'
AND t.target_name = N'ring_buffer' --stop session
*/
/*
-- SoSSE 20.0.1.333
-- File - Procedure\sqlserver_spotlight\2012\StatementWaits.sql
-- *
-- * Copyright 2014 Dell Inc. ALL RIGHTS RESERVED
-- *
*/
if (exists
(
SELECT name
FROM sys.server_event_sessions
WHERE name = N'SpotlightResponseTimeWaitData_sqldba1_3843'
)
)
begin
--get the data
/* BEGIN ACTIVE SECTION (inserted by Ignite) */
SELECT cast(target_data as xml) as target_data
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
ON t.event_session_address = s.address
WHERE s.name = N'SpotlightResponseTimeWaitData_sqldba1_3843'
AND t.target_name = N'ring_buffer' --stop session
/* END ACTIVE SECTION (inserted by Ignite) */
begin try alter event session [SpotlightResponseTimeWaitData_sqldba1_3843]
ON server state = stop
END try
begin
catch
-- session was most likely not running
end catch
--start the session
alter event session [SpotlightResponseTimeWaitData_sqldba1_3843] on server state = start
end
else
begin
declare
@TraceWriteType int
declare
@PreemptiveType int
select @TraceWriteType = map_key FROM sys.dm_xe_map_values WHERE name = 'wait_types' and
map_value = 'TRACEWRITE'
select @PreemptiveType = map_key FROM sys.dm_xe_map_values WHERE name = 'wait_types' and
map_value = 'PREEMPTIVE_XE_GETTARGETSTATE'
declare
@SQL nvarchar(max)
set @SQL =
'CREATE EVENT SESSION [SpotlightResponseTimeWaitData_sqldba1_3843] ON SERVER
ADD EVENT sqlos.wait_info
(
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
package0.collect_system_time,
sqlserver.database_name,
sqlserver.plan_handle,
sqlserver.query_hash,
sqlserver.query_plan_hash,
sqlserver.session_id,
sqlserver.sql_text,
sqlserver.username)
WHERE
(
opcode = 1-- end events only
AND (duration > 0 OR signal_duration > 0)-- had to accumulate wait time
AND (sqlserver.query_hash <> 0)-- had to have a query associated
AND [package0].[counter] <= 1000-- only collect 1000 events
AND wait_type <> '
+ convert(nvarchar, @TraceWriteType) +
' -- TRACEWRITE
)
),
ADD EVENT sqlos.wait_info_external
(
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
package0.collect_system_time,
sqlserver.database_name,
sqlserver.plan_handle,
sqlserver.query_hash,
sqlserver.query_plan_hash,
sqlserver.session_id,
sqlserver.sql_text,
sqlserver.username)
WHERE
(
opcode = 1-- end events only
AND (duration > 0)-- had to accumulate wait time
AND (sqlserver.query_hash <> 0)-- had to have a query associated
AND [package0].[counter] <= 1000-- only collect 1000 events
AND wait_type <> '
+ convert(nvarchar, @PreemptiveType) +
' -- PREEMPTIVE_XE_GETTARGETSTATE
)
)
ADD TARGET package0.ring_buffer(SET max_events_limit=(4000),occurrence_number=(1000))
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=ON,STARTUP_STATE=OFF)'
;
exec
(@SQL)
--start the session
alter event session [SpotlightResponseTimeWaitData_sqldba1_3843] on server state = start
end
June 26, 2020 at 7:50 am
Hi We are having a similar issue, any update on this please.
Regards,
Waqar
July 19, 2020 at 1:10 am
Check out this link : https://www.sqlskills.com/help/waits/preemptive_xe_gettargetstate/and this one: https://app.spotlightcloud.io/waitopedia/waits/PREEMPTIVE_XE_GETTARGETSTATE
I'm not entirely sure why this wait is a point of focus for you. I highly doubt this is actually causing a performance issue. Both links indicate that this wait type can be safely ignored. I would use your monitoring tools to identify slow running queries or high category wait types and focus on those first.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply