Mastering SQL Server Profiler - Part 6: Creating Custom Traces

  • Balraj Thumma

    Newbie

    Points: 3

    Hi there,

    I would like to describe your training on sql profiler just in one word "Outstanding"

  • rharderwijk

    Ten Centuries

    Points: 1119

    Brad,

    I am looking at something strange here: the output of my Profiler GUI trace and a programmatic trace do not match. I created a trace in the GUI, trying to capture the occurrence of two stored procedures, so our developers can look at the variables customers put in on the website.

    I the GUI all, looked exactly as I wanted, so I stopped the trace, did an export of the trace and ran the script on the server. After some time, I stopped the trace and looked at the output. Many more events where collected now, and a lot of stored procedures where captured that I am not interested in (all captured events where of the database I traced, so that went ok).

    First thing I did was pull your book 'Mastering SQL Server Profiler' (yes, the hardcopy) from the shelf to see if I could find anything on this particular behaviour; I could not find any hints as to a possible difference in captured events using one method or the other.

    Are you familiar with this behaviour? Do you know what does (or might) be the cause of it, and what the reason is for this difference?

    I hope you can shed some light on this!

    With kind regards,

    Rick

  • Brad McGehee

    SSCertifiable

    Points: 5272

    rharderwijk (8/11/2009)


    Brad,

    I am looking at something strange here: the output of my Profiler GUI trace and a programmatic trace do not match. I created a trace in the GUI, trying to capture the occurrence of two stored procedures, so our developers can look at the variables customers put in on the website.

    I the GUI all, looked exactly as I wanted, so I stopped the trace, did an export of the trace and ran the script on the server. After some time, I stopped the trace and looked at the output. Many more events where collected now, and a lot of stored procedures where captured that I am not interested in (all captured events where of the database I traced, so that went ok).

    First thing I did was pull your book 'Mastering SQL Server Profiler' (yes, the hardcopy) from the shelf to see if I could find anything on this particular behaviour; I could not find any hints as to a possible difference in captured events using one method or the other.

    Are you familiar with this behaviour? Do you know what does (or might) be the cause of it, and what the reason is for this difference?

    I hope you can shed some light on this!

    With kind regards,

    Rick

    If both the Profiler Trace and the Server Side Trace were identically configured, and you saw different results in two different traces, the only conclusion that I know of is the load (activity) on the server was different between the two traces.

    Brad

    Brad M. McGehee
    DBA

  • rharderwijk

    Ten Centuries

    Points: 1119

    If both the Profiler Trace and the Server Side Trace were identically configured, and you saw different results in two different traces, the only conclusion that I know of is the load (activity) on the server was different between the two traces.

    Thanks Brad. I can hardly imagine that being the case, looking at the trace results. Maybe I need to give you an example of what I have configured. Names look odd, but as you will understand I cannot post the actual code here.

    In my trace, I have included the following events and columns

    RPC:Starting

    RPC:Completed

    With the columns DatabaseName, SPID and Textdata

    (it's not a lot, but for the purpose I do not need any other data anyway, so no need to trace that)

    I put two filters on the trace, one on DatabaseName, containing the databasename and Textdata with the a string like %partofstoredprocedurename%. This should capture two stored procedures that have this string in the Textdata column: dbo.sprPartofstoredprocedurename and dbo.sprPartofstoredprocedurenamelongversion (no need to tell you what it does, but maybe for other readers - or just in case I'm missing something).

    As said before, I do get the correct results running the trace in Profiler GUI, but when exporting the trace to SQL and loading it on the server, it produces quite different results. Not only do I get the desired results, but also different stored procedures such as dbo.sprSomeDifferentCode and dbo.sprUnrelatedCodeforSameDatabase. Also I see lines in my trace with sp_connection_reset, quite a lot actually.

    Now I know some tracing might be dropped when the serverload is high, but it wasn't, and the behaviour seems to be consistent. Maybe this extra information will clarify it for you or any other reader on the forum.

    Again, thank you for your time.

    Rick

  • Brad McGehee

    SSCertifiable

    Points: 5272

    I have to say that I have not seen this behavior before, and I am not sure what to suggest, other than to use the Profiler GUI. Hopefully some other reader has seen this before, and can offer a suggestion.

    Brad M. McGehee
    DBA

  • kumaralingamp

    Valued Member

    Points: 66

    Excellent training on profiler.. Thanks for that..

  • SQLphone

    SSC-Addicted

    Points: 403

    Thank you for the videos. Superb presentation!

  • billross

    SSC Eights!

    Points: 892

    Superb training. The voice was familiar... "Hitchhiker's Guide to Sql Server Profiler?"

    "What I lack in youth I make up for in immaturity!"
    Please visit my music site at http://woundedego.com

  • shruti.kalia

    SSC Journeyman

    Points: 77

    very helpful and easy to learn tutorial. Thanks a lot for putting up this excellent presentation online. Great work!

  • griselljaffe

    Valued Member

    Points: 61

    Completed all 6 parts and it was wonderful. Many thanks.

  • mail.schaefer

    SSC Rookie

    Points: 45

    Simply great - logical, clear structured and a good introduction to SQL Server Profiler. Thank you for these great videos.

  • harisettipalli

    Grasshopper

    Points: 19

    thank you its very helpful:-D

  • bajabero

    Grasshopper

    Points: 12

    Really great job

    is this the last module in this course?

    Thanks

  • Brad McGehee

    SSCertifiable

    Points: 5272

    Yes, this is the last of the series.

    Brad M. McGehee
    DBA

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Hi, It's really great series.Thanks:-)

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

Viewing 15 posts - 31 through 45 (of 47 total)

You must be logged in to reply to this topic. Login to reply