Enabling Hidden Wait Types In Performance Advisor

The goal of our software is to highlight for you the metrics that are meaningful and actionable. We don't want to bombard you with information you can't learn from and act on. Based on empirical research analyzing hundreds of environments, we make decisions regarding which metrics we will or will not show on the Performance Advisor Dashboard and elsewhere. Specific wait types are a great example of this. We don’t show all wait types by default because many of them amount only to noise. If there are certain waits you would like to see on the dashboard and reports, however, you can easily enable them. Below are a couple of examples of how to do this.

OLEDB

We filter out OLEDB waits only because they are often what amounts to noise - often one of the top waits, but very rarely indicative of an actual performance problem. They can include everything from 3rd party software to full text searches to queries against DMVs. However, if you want these waits to show up on your dashboard, you can go to the repository and run the following update statement:

UPDATE dbo.PerformanceAnalysisWaitTypeClass SET Enabled = 1 WHERE Name = 'OLEDB Provider Calls'; Then the OLEDB Provider Calls wait category will show up on the dashboard.

TRACEWRITE

The TRACEWRITE wait type is a queue/timer wait. Its value will always be high – 1000ms of wait time per second, for all active traces, all the time. So, like OLEDB, showing this wait type would just drown out the more important waits, which is why we simply don't include it in our tracing waits. We do show the trace-related wait types that could be indicative of an issue (IO_AUDIT_MUTEX, SQLTRACE_LOCK, etc), so it is not that we are hiding tracing, just the meaningless trace-related waits.

Since TRACEWRITE is associated with the “Queue / Timer Waits” class which is hidden by default, its class needs to be changed in order for it to appear:

UPDATE dbo.PerformanceAnalysisWaitType SET WaitTypeClassID = 31 -- Tracing wait class WHERE Name = 'TRACEWRITE'; Since the Tracing class is already enabled, no further action is needed.

NOTE: We do prevent our own rowset trace process from showing up on the Top SQL grid by default, much like Profiler prevents its own calls from showing up, again because this is just noise. If you want to see the SQL Sentry trace in the Top SQL grid, you can check the box, "Show SQL Sentry trace process" , under the Top SQL tab in Performance Monitor.

You will see that the TRACEWRITE wait accumulates over time here, constantly increasing. But again, this is just the nature of the type of wait, and has no bearing on what the trace is actually doing.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.