Profiler Research

December 18, 2008 at 8:21 am (SQL Server 2005, SQL Server 2008) (performance monitoring, performance tuning, profiler, trace)

A question came up on SQL Server Central as to why or how Profiler could bring down the production server.  I was aware of the fact that Profiler caused problems and that’s why you should always use a server-side trace on production systems. Duh! Of course…. But why? I didn’t have an answer. I just “knew” what everyone told me. So I set out to do some research. Initially I hit my favorite source of information on SQL Server (and it should be yours too) the Books Online.

Here is what I was reading on the architecture of Profiler. Good, but not enough.

I still want more information, so I’ve gone out a’wanderin’ through the interwebs. First, Linchi Shea has this great set of tests comparing Profiler to server side traces. Fantastic information, and he proves what lots of people “know,” but he doesn’t explain why it’s occurring, simply that it does, which, by and large, is good enough for me most of the time, but my interest was piqued.

I still haven’t read Inside 2005: Query Tuning and Optimization (trying very hard not to plagiarize, even by accident, that’s about done and I can’t wait to read this), but one chapter, just on trace and Profiler is available online. That’s a scary read (read it to find out what happens if the file buffer fills). But there is a mechanism that flushes the file reader queue every four seconds, which is a trick to apparently reduce load on systems with a high number of transactions. That and the general speed of a file system over SMO seems to be why there is better performance for the server-side trace. They go on to quote Linchi Shea’s article above, which still doesn’t provide the answer as to why Profiler is, not only slower, but actually slows the system.

Ah, finally, here’s the answer. Profiler, as opposed to trace, actually requires memory latches, and exclusive latches at that, in order for the sessions on SQL Server to write the event out to Profiler. Then, you factor in the network, that the machine consuming this stuff is probably not terribly powerful… You get Profiler slowing down the server… Cool!

UPDATE: Missed some links. Thanks Gail.