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:
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.
Very interesting. I also agree that the Profiler GUI should be rarely used.
[…] 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 […]
[…] 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. […]
Wow! I knew it would skip over stuff if it was a busy box… but large? good info.
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.
[…] Home of the Scary DBA also exposes some snags with Profiler GUI. […]