Xperf is designed for hardcore Windows programmers and systems analysts, but it can also be an invaluable tool for SQL Server developers and admins who need to find very detailed answers as a part of some performance evaluation. However, Xperf is also great fun for those SQL Server folks who just want to throw up the hood and get a closer look at how the engine works. In this installment, we will illustrate this second category of use for Xperf by looking at how SQL Server loads memory-optimized tables into RAM.
If you have not already installed Xperf, you can find instructions here.
We will need an example database that contains at least one memory-optimized table; I will be using a variation of Adventureworks in which memory-optimized copies of tables have been created. We start by right-clicking on the database and choosing Tasks | Take Offline.We then open a command prompt and type the command
xperf -on FileIO
This command will start the recording of trace data, so we want to go to back to the Management Studio as quickly as possible to bring the test database back online. Every second of delay is going to include more irrelevant entries in the trace file we are building. For the same reason, when the database is back online we want to turn of the trace logging as quickly as possible.
xperf -d C:\MyFavoriteFileLocation\MyFavoriteFileName.etl
You should be able to go to the file location and double-click on the file to bring up the Windows Performance Analyzer or WPA. If the extension ”etl” is not associated with WPA, you can browse for wpa.exe in the ”Windows Kits\<version number>\Windows Performance Toolkit” folder in the 32-bit program files folder (i.e. ”Program Files (x86)”. Upon loading the etl file, the WPA should look something like this:
At this point, you may wish to start writing down the steps you take as you drill into the information. The data are so voluminous and the display options so numerous that not only is it easy to get lost, but it can also be difficult to remember exactly how you arrived at the useful information you were looking at yesterday. It this case, we will click on the small arrowhead pointing to ”Storage” in the Graph Explorer. This will reveal the File I/O graph.
Double-clicking on the File I/O graph will bring up an analysis window on the File I/O information.
The default is ”Count by Type”. Clicking on the down arrowhead will reveal alternatives; in this case we would like ”Activity by Process, Thread, Type”.
In the ”Process” column, there are many entries for cl.exe. This is the Microsoft C compiler that has been invoked for the creation and compilation of the native code required by the in-memory optimized tables and related structures. Clicking the arrowhead in the thread column pointing to the thread ID will reveal the individual file operations performed by that thread.
Have we clicked on enough arrowheads yet? Not quite. Clicking on the arrowhead in the Event Sub Type column next to ”Create” reveales individual create events, along with their filenames.
In this example, we can see the creation of a ”c” file for one of the memory-optimized tables. In this example, the ”1607676775” in the filename corresponds to the object ID in SQL Server, and we can use the OBJECT_NAME ( ) function to identify the actual table name if we choose. Continued examination reveals the creating of the header files, object files, and dlls, all loved by C programmers.
If you are looking at the initialization of in-memory tables in your own database, it is interesting to note the file creation taking place in whichever folder you have marked as the filestream group for in-memory optimization. The files created in this folder will manage the delta store information necessary for transactional updating of in-memory tables.
Xperf can provide unique insights into SQL Server for technologists who want to gain a greater understanding of the SQL Server internals. For those who would like to pursue the topic further, the most recent documentation (as of summer 2016) can be found on Microsoft’s MSDN website.