x

How can I enable hidden wait types in Performance Advisor?

We are evaluating some third party applications and I was wondering how I can enable waits that are not already enabled on our Performance Monitor dashboard such as OLEDB and TRACEWRITE?

avatar image By swordfish 16 asked Sep 18, 2012 at 07:37 PM
more ▼
(comments are locked)
10|5000 characters needed characters left

4 answers: sort voted first

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.

avatar image By James Holden ♦ 1.6k answered Sep 18, 2012 at 07:50 PM
more ▼
(comments are locked)
10|5000 characters needed characters left

We would like to see SOS_SCHEDULER_YIELD waits. Is that possible?,How do I enable monitoring of the SOS_SCHEDULER_YIELD wait?

avatar image By Aaron Sentell 36 answered Mar 21, 2013 at 12:50 AM
more ▼
(comments are locked)
avatar image Jason Hall ♦♦ Mar 21, 2013 at 02:23 PM

Hi Aaron,

Actually everything you need to know to enable this is already in the posted answer above, but I wanted to ask why you are looking to monitor this specific wait type?

This wait type is typically related to CPU pressure, and there are already several other metrics on the dashboard that provide that information. Consider monitoring the CPU chart itself, context switches (which will generally go up and down along with these scheduler yield waits), and the % of signal waits.

With a bit more information about what you're trying to accomplish I can determine the best way to help you.

10|5000 characters needed characters left

We're seeing CPU pressure but the CPU is running only at 40%. Using another tool we saw that SOS_SCHEDULER_YIELD waits were running high. Being that we didn't see this wait type in Sentry we thought it would be good to.

As for enabling it I'm not sure which record to set to enabled = 1 in PerformanceAnalysisWaitTypeClass. The only options for this are:

Queue / Timer Waits Extended Stored Procs OLEDB Provider Calls

avatar image By Aaron Sentell 36 answered Mar 21, 2013 at 03:25 PM
more ▼
(comments are locked)
10|5000 characters needed characters left

This is actually part of the Queue / Timer Waits class, but there are other wait types in that class that you are not really going to want to include.

What I would do in this case is actually change the class for it to Resource Waits, which is already enabled.

Use the PerformanceAnalysisWaitType table along with the PerformanceAnalysisWaitTypeClass table to find out what rows to update.

Here are a couple of blog posts that you might find useful on the subject as well: http://sqlblog.com/blogs/linchi_shea/archive/2012/01/12/performance-impact-driving-up-context-switches-sec.aspx

http://sqlblog.com/blogs/kevin_kline/archive/2008/05/14/sos-scheduler-yield.aspx

avatar image By Jason Hall ♦♦ 1k answered Mar 21, 2013 at 03:33 PM
more ▼
(comments are locked)
10|5000 characters needed characters left

This site is moving!

Our Q and A site has moved! Please visit our new Q and A site at support.sentryone.com to submit all new questions and to revisit our most-viewed and up-voted articles. For your convenience, this web page will be available in read-only mode for another two weeks.

Follow this question

Topics:

x42
x35
x29
x7
x5

asked: Sep 18, 2012 at 07:37 PM

Seen: 8527 times

Last Updated: Mar 21, 2013 at 04:05 PM

Related Questions