I recently had a trace from a customer that had around 60 different sessions. I was trying to find a specific SQL statement in one of the sessions that I had identified in a SQL blocking trace.
However I didn’t want to manually select each session to look for it, and there is no other way to search across all sessions. So I created the following query you can run against the Trace Parser database to locate the session(s) with the statement in:
–Find SQL in all traces
— Suggest you create the following to improve performance
–CREATE NONCLUSTERED INDEX IX_USP_QueryStatementHash
–ON [dbo].[TraceLines] ([CallTypeId],[QueryStatementHash])
SELECT [T4].[TraceId], [T6].[TraceName], [T5].[UserName], [T2].Statement
FROM [dbo].[TraceLines] AS [T1]
INNER JOIN [dbo].[QueryStatements] AS [T2] ON [T1].[QueryStatementHash] = [T2].[QueryStatementHash]
INNER JOIN [dbo].[UserSessionProcesses] AS [T3] ON [T1].[UserSessionProcessId] = [T3].[UserSessionProcessId]
INNER JOIN [dbo].[UserSessions] AS [T4] ON [T3].[SessionId] = [T4].[SessionId]
INNER JOIN [dbo].[Users] AS [T5] ON [T4].[UserId] = [T5].[UserId]
INNER JOIN [dbo].[Traces] AS [T6] ON [T4].[TraceId] = [T6].[TraceId]
WHERE ([T2].[Statement] LIKE ‘%queryhere%’) AND ([T1].[CallTypeId] = 64)
Edit the %queryhere% above to be something like:
“SELECT A.SALESID,A.DELIVERYNAME,A.ORDERING,A.SALESNAME%”
I would not recommend you try to find the full statement; only use the start of the statement as in the example above.
Use the results to easily find the session in trace parser you wanted to investigate.