Tracing Events Appropriately

When using SQL Server Profiler, it is generally a good idea to only trace COMPLETED events, instead of starting events.

Completed events have all necessary performance information. The starting events do not have important performance statistics because they haven't been collected yet. This will reduce the load of tracing and give you less data to filter out later.

Performance Tuning and the Heisenberg Uncertainty Principle

Often when I visit a new client, they say, "We need you to start SQL Server Profiler and tell us why our server is so slow."

I'm reluctant to immediately resort to the SQL Server tracing utilities because of the performance load that they cause. Sometimes, I explain to the Heisenberg Uncertainty Principle:

http://en.wikipedia.org/wiki/Uncertainty_principle

and similarly the Observer Effect:

http://en.wikipedia.org/wiki/Observer_effect

Developed by Werner Heisenberg in 1927, this principal states that when you measure something, there is uncertainty caused by the measuring process. For instance, if we write our trace logs to a file and we're looking for disk contention, the tracing itself might be contributing to it. The uncertainty might also be caused by the measuring logic. We might be looking at excessive disk contention and determine that we need faster disks. But what if memory paging is causing the pages to be written to disk? In that case, our measuring logic was flawed, and we would need to add more RAM to resolve the performance bottlenecks.

So before I start SQL tracing, I like to use other tools first. Task Manager, System Monitor, Activity Monitor, and DMVs will tell us an awful lot about a server's performance before tracing is even needed. This allows me to observe the server's performance with an open mind, before using invasive tracing utilities.