Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mastering SQL Server Profiler - Part 6: Creating Custom Traces


Mastering SQL Server Profiler - Part 6: Creating Custom Traces

Author
Message
Balraj Thumma
Balraj Thumma
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
Hi there,

I would like to describe your training on sql profiler just in one word "Outstanding"
rharderwijk
rharderwijk
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 201
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
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 730
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
rharderwijk
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 201
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
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 730
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
kumaralingamp
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 30
Excellent training on profiler.. Thanks for that..
SQLphone
SQLphone
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 46
Thank you for the videos. Superb presentation!
billross
billross
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 119
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
shruti.kalia
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 32
very helpful and easy to learn tutorial. Thanks a lot for putting up this excellent presentation online. Great work!
griselljaffe
griselljaffe
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Completed all 6 parts and it was wonderful. Many thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search