Why can I not see the Object name or the SQLtext that was being run?

Mike 2014-08-05 04:56:55

Mike 2014-08-05 23:04:31
It is from MS SQL's own DMV data (ie, no old-school server trace was used)

SQL Server 2008 R2

DECLARE @XMLData xml

SELECT TOP 1
@XMLData = CAST(target_data AS xml)
FROM
sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address WHERE
name = 'system_health'

SELECT
col.value('(./@timestamp)', 'DATETIME') AS Deadlock_TimeStamp,
CONVERT(xml, col.value('(data/value)[1]', 'VARCHAR(MAX)')) AS DeadLock_XML FROM
@XMLData.nodes('//event') AS X (Col)
WHERE
col.value('(data/value)[1]', 'VARCHAR(MAX)') LIKE '%deadlock%'
AND DATEDIFF(MINUTE, col.value('(./@timestamp)', 'DATETIME'), GETUTCDATE()) <= 60 --deadlocks in last 60 mins
ORDER BY
col.value('(./@timestamp)', 'DATETIME') desc

Kevin 2014-08-05 14:10:29
Hi Mike,

The PE display and XML file shows "host redacted". So it looks like all of the data which could reveal the object names has been removed. I don't know exactly why or how that can happen. So I'd encourage you to contact Support (it's free) to get a more accurate ruling on what that's about.

Sorry I couldn't be more help,

-Kev

Mike 2014-08-05 23:10:24
Thanks, Kevin. Those redacted parts are automatically done by Plan Explorer when posting to this forum and don't relate to the object I don't believe which is still clearly visible in my XML here.

"waitresource="OBJECT: 11:759673754:0"

where "759673754" is the object in DB_ID=11

Aaron Bertrand 2014-08-05 23:30:37
Okay so now that we know you got the deadlock from the system_health session, you can see why there is no query text there by scripting the session: the xml_deadlock_report event does not explicitly capture sql_text (most events don't because it is a bit more costly to do so). If you want to capture more information about this deadlock, you're going to have to set up your own extended event session with ACTION(sqlserver.sql_text), or implement a monitoring tool that will capture this information for you (SQL Sentry Performance Advisor comes to mind, for obvious reasons :-)).

Sorry that the host_redacted thing threw me off initially; we get a whole lot of query plan uploads here, but very few deadlocks.