The standard triad of SQL Server performance monitoring: Dynamic Management Views, performance counters, and trace, provide the bulk of the information necessary for routine performance data collection. There are times, however, when a more powerful tool is needed. Perhaps you need to determine exactly what tasks are causing a wit, or taking up to many CPU cycles. Perhaps you need detail on updates being made to the log. In such cases, the Xperf tool set can be indispensable. Xperf is also a godsend for true geeks who want to understand how things work at a level of detail that would be excruciating for the rest of us.
As Xperf does not come already installed on Windows, we must start with the installation.
Xperf is now part of the “Assessment and Deployment” Kit, and is bundled and downloaded with a bunch of other stuff you may or may not want. Fortunately, even if the download is large, you need only install what you want. At the time of this writing, the ADK can be downloaded here. The file you should have is adksetup.exe.
After you are asked to what machine you would like to install the kit, you will be asked for the features you would like to install. Xperf does not depend on any of the other choices, so it may be the only one selected.
Xperf has no user interface; its sole job is to write copious amounts of information to a file. Be careful, the data yielded is voluminous. One of my looks into stack traces recorded about one megabyte per second. By default, Xperf will store its output in a file called ‘kernel.etl’ (and user.etl for user traces) in the default directory. You can change this if you like, but this file is only a workfile used in the preparation of the final output. You will have to supply a final output file name when you stop the trace.
Xperf can monitor a wide variety of system events. To make it easy to trace the events you need, XPerf combines individual traces into groups called “Kernel Groups”.If we elect to use the group “FileIO” we will automatically include the individual traces we will need. The various kernel groups can be viewed by entering
xperf -providers KG at the command line. Instead of a command line specifying individual parameters:
xperf -on PROC_THREAD+LOADER+DISK_IO+HARD_FAULTS+FILE_IO+FILE_IO_INIT
We can just type:
xperf -on FileIO
Either of these two command lines results in exactly the same trace.
The optional -f flag allows us to specify a preferred filename and location.
xperf -on FileIO -f C:\Temp\20160714-tmp.etl
Stopping the trace requires specifying the location and name of the final output file.
xperf -d C:\Temp\20160714-01.etl.
Now the fun can start. In the next installment, we will look at the IO activity during the startup of an SQL Server database with memory-optimized tables.
Xperf is a surprisingly powerful tool for examining details of SQL Server performance. Since it is a Windows tool, the same tool set can be applied to any program running in the Windows OS. This includes programs like the analysis services that do not have the sophisticated monitoring features of SQL Server itself.
The hardest part of getting started with Xperf is finding the right spot to download it on Microsoft’s website. Starting and stopping traces is trivial, so long as you have a hard drive with lots of empty space. The hard part, making sense of it all, well that is yet to come.