I’m sure many of you are aware of the wonderful RML Tools (description here and x86 download here) and specifically the ReadTrace tool. Maybe you are also aware of SQLNexus which relies on ReadTrace for the trace analysis.
At some point in time when you use these tools, you would come across the ‘Interesting Events’ table (ReadTrace.tblInterestingEvents in the SQLNexus database) which is populated with various errors and warnings. While ReadTrace comes with a way to view this information at a summary level, one thing it does not easily provide is the ability to tie sample batch text to the ‘interesting event’. Strictly speaking the ReadTrace schema does not provide a ‘by-design’ way to query this information, however best-effort guessing can be done by using the time the ‘interesting event’ was generated and then using it to query tblBatches. Here’s one such query I use to dig for clues to which batches led to such events:
SELECT TEC.name, TUB.OrigText, TB1.Cpu
FROM ReadTrace.tblInterestingEvents TIE
JOIN ReadTrace.tblBatches TB1
ON TIE.SPID = TB1.SPID and TIE.StartTime >= TB1.StartTime AND TB1.EndTime >= TIE.StartTime
JOIN ReadTrace.tblBatches TB2
ON TB1.BatchSeq = TB2.BatchSeq AND TB1.BatchSeq >= TB2.BatchSeq
JOIN ReadTrace.tblUniqueBatches TUB
ON TB1.HashID = TUB.HashID
JOIN sys.trace_events TEC
ON TEC.trace_event_id = TIE.EventID
WHERE name = ‘Hash Warning’
Of course, the ‘Hash Warning’ (more information about it available here) event name mentioned above could be replaced with others, such as ‘Sort Warning’, ‘Exception’ etc.