I am troubleshooting an issue where a domain controller pins its CPU at 100% for extended periods and as a result, LDAP authentication requests fail causing organization wide application failures. I recently posted a HOWTO on setup and configuration of the Server Performance Advisor tool from Microsoft. This is the tool that allows you to determine which specific machines and even specific LDAP queries are being performed on a domain controller. You can find more on that HOWTO here.
The problem now is that the tool generates a tremendous amount of data and so it’s not feasible to leave it running all the time. I wanted to find a way that the SPA could be triggered only during periods of high CPU utilization. While I unfortunately unable to find an off-the-shelf solution to this, thanks to PowerShell I was able to quickly develop a solution for this problem.
The script below is a rough-around-the-edges purpose built script that is designed to be run on an offending domain controller. It requires that the Server Performance Advisor has already been setup and configured. Assuming that’s done, the idea is that you simply run this script on your domain controller for an extended period of time. What the script does is check for CPU utilization (by default every 60 seconds) and if high CPU usage is detected, it automatically triggers the SPA.
Once you let this run over time, you can then use the SPA Report Explorer to determine which specific clients and queries were the most expensive and take action to resolve them. An example of what the script can do for you is shown below. Each report shown was automatically triggered during periods of high CPU usage and each capture 2 minutes worth of detailed logs.
If you find this useful, let me know in the comments below!
# This script is designed to be run on a Domain Controller that is experiencing high CPU utilization
# This script assumes that an SPA Database has already been deployed and configured
$SPAPAth = "D:\SPA" # The location of the extracted files for the Server Performance Advisor
$AdminUser = "domain\username" # A user account that has "Logon as Batch Job" permission on the target server
$SecondsToWait = 60 # How long to wait between checks. Recommended: 60 (seconds)
$CPUThreshold = 95 # When CPU usage reaches this level as a percent, trigger the SPA. Recommended: 95 (%)
$LogDuration = 120 # The duration of time the SPA should run once triggered. Recommended: 120 (seconds)
$SQLServer = "SQLSERVER01" # The hostname of the server that is hosting SQL Server
$SPADatabase = "SPA1" # The name of the SPA Database that will be used. This will be created in advance when you first run the wizard from SPAConsole.exe
# Import the SPA cmdlets, specifically Start-SPAAnalysis
Import-Module -Name $SPAPAth\SpaCmdlets.dll
# Function to display countdown progress bar. This is useful as this is designed to run for long periods of time and this helps validate the script is still functioning
Function Wait-CountDown ($WaitTimeInSeconds) {
$IntervalPercent = $WaitTimeInSeconds / 100
while($SecondsToWait -gt 0) {
Write-Progress -Activity "Waiting for next execution..." -status "$SecondsToWait seconds remaining..." -PercentComplete ($SecondsToWait/$IntervalPercent)
Start-Sleep -Seconds 1
$SecondsToWait-- }
}
# Create prompt to type in password for the account with log on as batch job rights to server
$Cred = Get-Credential -UserName $AdminUser -Message "Account with 'log on as batch job right' on destination server:"
# Loop indefinitely or until the user stops the script
While(1 -eq 1) {
# Get the current CPU usage as a percent
$ProcCounter = Get-Counter -Counter "\Processor(_Total)\% Processor Time" -SampleInterval 2
$CPUUsage =[math]::Round(($ProcCounter.readings -split ":")[-1])
# If the threshold is met or exceeded, it means the domain controller is busy. Start the SPA logger to determine exactly why it is busy
if($CPUUsage -ge $CPUThreshold)
{ Start-SpaAnalysis -ServerName "localhost" -AdvisorPackName Microsoft.ServerPerformanceAdvisor.AD.V1 -Duration $LogDuration -SqlInstanceName $SQLServer -SqlDatabaseName $SPADatabase -Credential $Cred }
Wait-CountDown $SecondsToWait
}
