I'm using SQL Server Profiler to trace application activity, but the Profiler logs aren't showing some commands that I know the application issued. Is Profiler reliable? Why is it missing commands?
Profiler is reliable. To understand the behavior you're seeing, you need to differentiate between Profiler, which is simply a GUI client application, and SQL Trace, which refers to a collection of server-side components that collect the events and data columns you want to see. Profiler makes using SQL Trace easy by providing a GUI wrapper, but you can use T-SQL to call the underlying SQL Trace procedures and functions without using the GUI.
The case of the missing events you describe is a result of a Microsoft design decision to minimize the risk of the Profiler GUI significantly degrading server performance. Occasionally, the Profiler GUI client can't consume the information it's tracing fast enough to receive and display all of the trace events. Rather than slow down SQL Server or run the risk of capturing only intermittent commands (which would be worse than an explicit warning that Profiler has paused the trace), Profiler stops producing events until it can read all existing events from the Profiler queue. Event production starts again when Profiler catches up.
You'll know that the Profiler GUI has paused if you see an entry within the Profiler Message EventClass that has the TextData value Some trace events have not been reported to SQL Profiler because the server has reached its maximum amount of available memory for the process. Note that SQL Server Books Online (BOL) doesn't document the Profiler Message EventClass. The correct integer value for the class in SQL Server 2000 is 57. You'll need this value if you load a trace file into SQL Server for detailed analysis. (The behavior of Profiler in SQL Server 7.0 is different, and this information applies specifically to SQL Server 2000.)
How can you avoid missing events? Two solutions let SQL Server capture all events, even under a heavy load. You can write the trace output to a file and select the GUI check box Server processes SQL Server trace data, or you can write the output to a SQL Server table. Writing the data to a SQL Server table imposes a heavier performance load than simply writing to a file, so I usually write to a file.
This problem and its solutions aren't well documented, but you can see how the exact T-SQL calls that implement this behavior work by using one Profiler instance to trace how another Profiler instance creates and runs a server-side trace. Using the Profiler GUI to write trace data to a file with the Server processes SQL Server trace data option enabled causes Profiler to create and run two separate traces. One trace captures data as a rowset for display in the Profiler GUI; the second trace writes data to the file. However, running two traces to capture the same information is inefficient, so consider running the T-SQL trace commands directly on the server.
The trace that displays data in the Profiler GUI might not capture all the events that you request, but the trace data that a server-side trace writes to the file will capture all the events. How many events might the display trace miss? I recently ran a heavily stressed Profiler test that lasted 15 seconds. The Profiler GUI captured 23,718 rows, and the trace file captured 93,839 rows—which means the Profiler GUI lost 70,121 events. In just 15 seconds, this example generated 1,011 separate events that indicated that Profiler output had temporarily paused.
The Profiler GUI is a wonderful tool for doing simple tests. However, when you have a heavy load, always process it through the server and write the results to a file or a SQL Server table. Otherwise, you'll likely miss key events.
