The Case For Using XPerf In Your SQL Server Performance Tuning Toolkit

XPerf comes as part of the windows performance tool kit and it makes use of the Event Tracing for Windows framework. To sum this tool up at a very high level, it provides the means of capturing stack trace and performance related statistics on the operating system all the way down to the kernel. For me, the main selling point of XPerf is its ability to obtain stack traces. There is a plethora of good material on Microsoft technet about how to get started with XPerf, therefore I will not attempt replicate this information. I will, however, endeavor to put forward the case as to why you might want to use XPerf.

You will most probably ask; why would I want to use XPerf when SQL Server provides a vast array of instrumentation options in the form of perfmon counters, dynamic management views and extended events, the simple answer to this is that none of these tools provide:

  • A definitive answer as to where your CPU cycles are being burned when you wish to investigate things such as SOS_SCHEDULER_YIELD signal waits.
  • Not all wait events and latches are documented or have clear explanations. For example the description given for the latch “ACCESS_METHODS_DATASET_PARENT” is:

“Used to synchronize child dataset access to the parent dataset during parallel operations.”

This is obviously something to do with parallel execution, but what exactly ?, using XPerf we can obtain a stack trace of where we hit the latch, and then walk the stack backwards ( image courtesy of Thomes Kejser ):

.stack walk

SQLMIN:DLL!HeapDataSetSession::GetNextRangeForChildScan hints that this latch might be associated with the parallel page supplier, the specific problem being that child threads are waiting to be served with pages ranges when a parallel table scan is taking place. In this specific example, the resolution was to partition the table as this has the effect of creating one parallel page supplier per partition.

  • Not all elements of the SQL Server business intelligence stack come with good instrumentation, analysis services being a major case in point, this also applies to integration services. In my session at the last ( 2013 ) SQL Bits, I was able show how one approach to implementing a data flow resulted in two execution paths and twice as many context switches as an alternative approach which only used one execution path:

execution paths

 I cannot think of any other tool which would allow this information to be captured. Extended events can be used to create stack traces, Paul Randal has an excellent blog post on how to do this over at the SQL Skills web site, however this approach will only result in a stack trace for the event that triggered the stack trace in the first place. If you want the entire picture of what is taking place on your server, XPerf remains the definitive way to go.

3 thoughts on “The Case For Using XPerf In Your SQL Server Performance Tuning Toolkit

    • Hi Maneesh,

      If I get time I will endeavour to do this, alternatively you can look up the session I presented at SQL Bits which should be recorded or lookup debugtools on Microsoft’s channel 9.

      Chris

  1. You are Fabulous….. Hardly in internet we are getting this DEPTH knowldege.other sites are only providing surface knowledge. When I read your blog, I find my self with new energy, new thought that it is also possible in sql server. Really grateful

    In this specific example, the resolution was to partition the table as this has the effect of creating one parallel page supplier per partition.

    I am noticing ACCESS_METHODS_DATASET_PARENT, it means we can partition the object to alleviate this issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s