How to read Trace Information

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

How to read Trace Information

Postby m.cecili » Fri Apr 27, 2012 9:00 am

I tried to activate Trace to have more information about deadlocks.
I need an explanation how to read extra information.
I saw that in Trace tabsheet now i can click every single process row and read other information about the executed sql.
My problem was that the basic information about deadlock, more exatly the line of code that generated dead lock, was wrong.
We use a lot of stored procedure very long that call other stored procedure and is difficult to understand which line of code causes the problem.
I hoped that enabling trace, it could help me to understand but i don't know how to read all logged information. Can you help me?
m.cecili
 
Posts: 2
Joined: Thu Apr 26, 2012 11:12 am

Postby m.cecili » Fri Apr 27, 2012 11:29 am

When i enable trace the informations are not immediatly available. I saw an information tip which say: NO SQL statements to display. No statement captured for the selected process...
After how many time trace information are displayed? Is there a fixed rule?
m.cecili
 
Posts: 2
Joined: Thu Apr 26, 2012 11:12 am

Postby Chris Spencer » Mon Apr 30, 2012 12:42 pm

On activating trace in SQL Monitor we immediately set trace running on the relevant monitored server. When an alert is raised that supports trace (going from memory this would be Deadlocks, Long-running Query, Job Failed, Blocked Process and Error Log entries), we read the trace files and transfer data for the relevent process and time period into our data repository. The recently raised alert should display this trace data although not always straight away. If there's a lot of trace data in the time period that the alert was raised it takes a while to transfer so there could potentially be a couple of minutes lag before the trace data appears. I believe that you'd need to reopen the alert to get the trace to appear.

So do you see the call to the stored procedure but not the code in the stored procedure itself?

Regards
Chris
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests