emeadaxsupport: Trace Parser Tip - How to find a SQL statement across all sessions

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...-sessions.aspx
==============

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.




Источник: http://blogs.msdn.com/b/axsupport/ar...-sessions.aspx