SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

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

•  by datatake
 
•  December 26, 2013
 
•  Uncategorized

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 ):

.

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:

 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.