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!RtlUserThreadStartAlloc : 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
TroubleshootingSQL 
Excellent Series! Very Informative.
LikeLike
Thanks. Glad to know it was helpful 🙂
LikeLike
Pingback: Tools Tips and Tricks: Round-up « TroubleshootingSQL
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
LikeLike
Unfortunately changing account and password through WMI leads to a service restart. If you are changing the password only and the service account remains the same, only then a service restart is not required.
Refer http://support.microsoft.com/kb/936492 for more information.
LikeLike
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
LikeLike
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 “”, “”
LikeLike
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
LikeLike
Welcome
LikeLike
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
LikeLike
Correct. \root\Microsoft\SqlServer\ComputerManagement works only for 2005.
LikeLike
Thank you, Amit.
LikeLike
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
LikeLike
The database engine service is the only service that is restarted in-case you change the account name and password using WMI.
LikeLike
Is there any WMI function call that can restart the agent service?
Yan
LikeLike
I will have to check if there is a different way to achieve this.
LikeLike
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
LikeLike
Thanks. I will check this out.
LikeLike
This is exatlcy what I was looking for. Thanks for writing!
LikeLike