Tools Tips and Tricks #12: XPerf, Memory usage and much more


This is the last post for the series Tools Tips and Tricks as May draws to a close. Today I shall talk about another tool that we use called XPerf for performance troubleshooting. Though this is not a common tool which is used on a regular basis by the SQL CSS team. But when we do decide to use this for very specific scenarios, the usefulness of this tool cannot be put in words. I had talked about using Debug Diag for monitoring memory usage and tracking down allocations right upto the function call. There is another way to track heap allocations which is what I shall be talking about today. I shall use the same MemAllocApp that I had used last week. I start off the Xperf monitoring using the following commands:

xperf -on PROC_THREAD+LOADER -BufferSize 1024 -MinBuffers 16 -MaxBuffers 16
xperf -start HeapSession -heap -Pids 9532 -BufferSize 1024 -MinBuffers 128 -MaxBuffers 128 -stackwalk HeapAlloc+HeapRealloc+HeapCreate

Now once I have collected the XPerf data, you can use the following command to stop the data collection:

xperf -stop HeapSession -stop –d F:\MemAlloc.etl

Once that is done, you should have an ETL file in the specified location by the –d parameter. Since, I am interested in the functions in the functions which were allocating the maximum amount of memory, I will use the following command to generate a summary report for the heap allocations traced by XPerf using the command below:

xperf -i "F:\MemAlloc.etl" -o "F:\MemAlloc.txt" -symbols -a heap -stacks -top 5

/* Output of MemAlloc.txt file */

Results for process MemAllocApp.exe (9532):

———————————————————————

GLOBAL ALLOCATIONS:
Alloc       :         100,     512000.0 KB
Realloc     :           0
Outstanding :         100,     512000.0 KB

———————————————————————

TOP 1:
Alloc       :         100,     512000.0 KB
Realloc     :           0
Outstanding :         100,     512000.0 KB

———————————————————————

MemAllocApp.exe!fn_allocatememory
MemAllocApp.exe!wmain
MemAllocApp.exe!__tmainCRTStartup
MemAllocApp.exe!wmainCRTStartup
kernel32.dll!BaseThreadInitThunk
ntdll.dll!RtlUserThreadStart

Alloc       :         100,     512000.0 KB
Realloc     :           0
Outstanding :         100,     512000.0 KB

 

As you can see from the above output, the function fn_allocatememory was responsible for 100 allocations worth 512KB each. With just the use of a single command I was able to figure out the reason behind my outstanding allocations for my EXE. Troubleshooting SQL Server outstanding memory allocations for heaps may not be as easy as this but it definitely saves time in having to look and dig out the allocations from the a memory dump.

This method is quite useful when you have a very large ETL file which you need to analyze. You can even configure a Circular Buffer for capturing data appending the following command for your HeapSession tracing commands:

-BufferSize 1024 -MaxBuffers 1024 -MaxFile 1024 -FileMode Circular

Note: Make sure that you set your _NT_SYMBOL_PATH environment variable correctly if you want the function calls to be resolved correctly.

Hope you enjoyed this series of Tools Tips and Tricks as much as I had fun in posting the various methods that I use to collect diagnostic data while troubleshooting SQL performance related issues.

References:
Using Actions to process Heap Data
Enabling Data Capture using XPerf
XPerf Options

Advertisements

19 thoughts on “Tools Tips and Tricks #12: XPerf, Memory usage and much more

  1. Pingback: Tools Tips and Tricks: Round-up « TroubleshootingSQL

  2. Hey Amit,

    i actually have a question about another of your scripts – SQL Server Account Password Change, if you don’t mind. The SQL service appeared bounced after the password was changed. Is it possible not to bounce the service and still have the password changed?

    Thanks

    Yan Li

  3. Thanks for the quick response, Amit.

    I only intend to change the password, not the service account.

    I can see the script (pwdchange.vbs) ran successfully and returned the msg saying so. But when I checked the database, i could see from the log the sql server was rebooted. Is the script supposed for changing both the service account and the password?

    The following lines in the script seem to be the one that did the work:

    Wscript.Echo “Setting new password for startup account for service: ” & objItem.ServiceName
    Set objShare = objWMIService.Get(“SqlService.ServiceName='” &strServiceName&”‘,SQLServiceType='”&int(strServiceType)&”‘”)

    ‘ Obtain an InParameters object specific
    ‘ to the method.
    Set objInParam = objShare.Methods_(“SetServiceAccount”). _
    inParameters.SpawnInstance_()

    ‘ Add the input parameters.
    objInParam.Properties_.Item(“ServiceStartName”) = strAccount1
    objInParam.Properties_.Item(“ServiceStartPassword”) = strPassword

    ‘ Execute the method and obtain the return status.
    ‘ The OutParameters object in objOutParams
    ‘ is created by the provider.
    Set objOutParams = objWMIService.ExecMethod(“SqlService.ServiceName='” &strServiceName&”‘,SQLServiceType='”&int(strServiceType)&”‘”, “SetServiceAccount”, objInParam)

    Or change needs to be made to these lines to make it password change only?

    Thanks again

  4. The script is intended to change the account and password. You can use the following code to change only the password:
    set svr = GetObject(“WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement:SqlService.ServiceName=”,SQLServiceType=1″)
    svr.SetServiceAccountPassword “”, “”

  5. I like to maintain the main structure and coding style of your script and integrate the password-change-only feature into it, because I have about 100 SQL Servers and even more instances/services of different editions that need to have the password changed all together without the service reboot.

    Based on your script, the following lines of code changes seem doing the work:

    Set objInParam = objShare.Methods_(“SetServiceAccountPassword”). _
    inParameters.SpawnInstance_()

    ‘ Add the input parameters.
    objInParam.Properties_.Item(“AccountOldPassword”) = “”
    objInParam.Properties_.Item(“AccountNewPassword”) = strPassword

    ‘ Execute the method and obtain the return status.
    ‘ The OutParameters object in objOutParams
    ‘ is created by the provider.
    Set objOutParams = objWMIService.ExecMethod(“SqlService.ServiceName='” &strServiceName&”‘,SQLServiceType='”&int(strServiceType)&”‘”, “SetServiceAccountPassword”, objInParam)

    Again, thank you very much for the script.

    Yan Li

  6. One more question, Amit, hope you don’t mind:

    it seems \root\Microsoft\SqlServer\ComputerManagement10 working for both 2005 and 2008 services, and \root\Microsoft\SqlServer\ComputerManagement only working for 2005. Is it correct?

    Yan

  7. Hey, Amit,

    I just noticed that the sql server agent service wasn’t restarted by the script after the service account and password were changed, but the SQL Server service was restarted correctly. Is this on purpose?

    I googled around but didn’t find anything. How to make the agent restarted within the script after the account/password change?

    Thanks

  8. Amit,

    Just want to share it with you that the following to start the agent works great:

    if (InStr(objItem.ServiceName, “SQLAgent”) > 0 or InStr(objItem.ServiceName, “SQLSERVERAGENT”) > 0) ) then objItem.startService()

    Yan

It is always good to hear from you! :)

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s