Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Mastering SQL Server Profiler - Part 6: Creating Custom Traces Expand / Collapse
Author
Message
Posted Thursday, August 6, 2009 4:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 18, 2010 12:01 AM
Points: 1, Visits: 8
Hi there,

I would like to describe your training on sql profiler just in one word "Outstanding"
Post #766049
Posted Tuesday, August 11, 2009 6:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:14 AM
Points: 96, Visits: 199
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
Post #768525
Posted Tuesday, August 11, 2009 8:12 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
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
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #768634
Posted Tuesday, August 11, 2009 1:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:14 AM
Points: 96, Visits: 199
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

Post #768902
Posted Tuesday, August 11, 2009 3:27 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
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
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #768995
Posted Monday, September 21, 2009 3:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 12:19 PM
Points: 2, Visits: 30
Excellent training on profiler.. Thanks for that..
Post #791450
Posted Tuesday, November 24, 2009 6:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 16, 2012 4:10 PM
Points: 11, Visits: 46
Thank you for the videos. Superb presentation!
Post #824242
Posted Tuesday, February 2, 2010 7:55 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 14, 2010 9:41 PM
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
Post #857966
Posted Friday, March 5, 2010 7:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 28, 2013 1:33 PM
Points: 57, Visits: 32
very helpful and easy to learn tutorial. Thanks a lot for putting up this excellent presentation online. Great work!
Post #878050
Posted Wednesday, May 12, 2010 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 22, 2010 12:35 PM
Points: 1, Visits: 1
Completed all 6 parts and it was wonderful. Many thanks.
Post #920653
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse