Sunday, November 11, 2007

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.

No comments:

Post a Comment