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.