Microsoft Dynamics 365 Blog

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


–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:


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.

We're always looking for feedback and would like to hear from you. Please head to the Dynamics 365 Community to start a discussion, ask questions, and tell us what you think!