Snags With Profiler GUI

October 28, 2009 at 7:59 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, Tools, TSQL) (, , , , )


Running the Profiler GUI against a production server is not something you should do. I’ve outlined my research into exactly why in the past. But I hit another little issue with the Profiler GUI as part of work I’m doing on a Microsoft PSS call (more on that in another post). We have a procedure on one of our systems that is erroring out, but only on a particular server and only when called from the application, not when it’s called, on the same server, from SQL Server Management Studio.

I needed to capture some trace events and do it quickly, so I decided to use the GUI, just this once. I put filters on it so that I would only collect certain data, and the database I was collecting from was not in active use, so I knew the only commands I’d be capturing were my own. I just captured SQL:BatchCompleted and SQL:BatchStarting. I needed to run the query one time to validate the behavior. Under these circumstances, with full knowledge of what the GUI can do to a production system, I figured I was ok.

I kicked off the trace through the GUI and ran my proc. Here’s what I saw:

Skipped

This is not evidence of the problems outlined above. You know how TextData normally shows the query that is being run? In this case the query being run involves a rather large XML parameter being passed in as part of the query. This is so large that the Profiler GUI skipped over it and didn’t bother clogging  the memory of the system or my network with capturing it. Don’t panic though. If I run the exact same trace as a server side trace, I get the full text of the call in the TextData field. And yes, if you’re wondering, I’m pretty positive this is by design (although the only reference I can see in BOL is to events greater than 1gb, which we are not into here).

On more reason to avoid the Profiler GUI as a data collection mechanism.

6 Comments

  1. Jack Corbett said,

    Very interesting. I also agree that the Profiler GUI should be rarely used.

  2. Twitter Trackbacks for Snags With Profiler GUI « Home of the Scary DBA [scarydba.wordpress.com] on Topsy.com said,

    […] Snags With Profiler GUI « Home of the Scary DBA scarydba.wordpress.com/2009/10/28/snags-with-profiler-gui – view page – cached Running the Profiler GUI against a production server is not something you should do. I’ve outlined my research into exactly why in the past. But I hit another little issue with the Profiler GUI as… (Read more)Running the Profiler GUI against a production server is not something you should do. I’ve outlined my research into exactly why in the past. But I hit another little issue with the Profiler GUI as part of work I’m doing on a Microsoft PSS call (more on that in another post). We have a procedure on one of our systems that is erroring out, but only on a particular server and only when called from the application, not when it’s called, on the same server, from SQL Server Management Studio. (Read less) — From the page […]

  3. Trace skipped records – SQL Server profiler « Viking’s Weblog said,

    […] I thought this is something new which I haven’t seen before and took it as learning (partly ignorance too ).  But, it is so coincident that someone, somewhere had the same problem and this is what I appreciate about sharing information across the globe.  That someone is ScaryDBA  who had the same issue, but he went and dug around on to see what is this all about?  Well here is what he had to say in his blog post – Snags with Profiler GUI. […]

  4. SuperDBA said,

    Wow! I knew it would skip over stuff if it was a busy box… but large? good info.

  5. scarydba said,

    Yeah, I was a little shocked too. Although it’s no big deal. I shouldn’t have been running the GUI on the server anyway.

  6. Log Buffer #167: a Carnival of the Vanities for DBAs | Pythian Group Blog said,

    […] Home of the Scary DBA also exposes some snags with Profiler GUI. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: