One of my colleagues shared a discovery he made during a recent performance-tuning audit that he was doing for a customer. As part of his tuning methodology, he regularly ran SQLTrace and other tools.He typically began his audit by determining whether other traces might be running before he started a new one. My colleague determined that a well-known SQL Server performance-monitoring tool from a well-known vendor was running a SQL trace that was capturing events at the SP:StmtComplete level of granularity. This trace-event class captures every statement executed within every stored procedure. Tracing at this level of granularity can add a noticeable amount of overhead on a busy server. But, the biggest performance problem associated with this level of tracing is that the trace writes the event to disk rather than capturing it.At this level of granularity, the number of events that trace writes to disk can negatively affect performance.
I've seen traces capture events at the SP:StmtComplete level of granularity and consume 50-100MB of disk space per second for short time periods. It doesn't take a performance-tuning expert to know that adding that amount of I/O to a SQL Server that might already be experiencing performance problems can lead to less than desirable results. Referring back to the customer scenario, although the customer was running the performance-monitoring tool to detect any system performance problems, the tool was actually one of the main causes of performance degradation. Simply turning off the performance-monitoring tool corrected many of the problems the customer was experiencing.
The customer was aware that the SQL Server performance-monitoring tool was installed on the server, but was unaware that the trace was running at the SP: StmtComplete level of granularity. I want to caution you to make sure you understand what your monitoring tools do. If you're not sure how your tools are collecting data, ask the vendor. If the vendor can't tell you (and yes, this has happened to me), then perhaps you shouldn't be using the tool.
It's easy to look for existing SQL traces that are running on a SQL Server.The table valued system function fn_trace_getinfo( ) will help you see any defined or running traces. The following statement provides information for all traces running or defined on SQL Server 2005 or 2000:
SELECT * from :: fn_trace_getinfo(default)
For more information about fn_trace_getinfo( ), see the Microsoft article " Introducing SQLTrace" at http://msdn2.microsoft .com/en-us/library/ms191006.aspx. In addition, the SQL Server 2005 sys.traces catalog view is easy to use and provides more detailed information about a trace than the information you get from fn_trace_getinfo( ).For more information about using sys.traces catalog view, see the Microsoft article "sys.traces (Transact-SQL)" at http://msdn2.microsoft.com/ enus/library/ms178579(SQL.90).aspx.
I don't want to scare you off from ever using a performance-monitoring tool. These tools are very powerful, provide useful analysis information, and are a great item to have in your "tuning bag of tricks." However, you need to use them carefully, especially in a production environment.