Archive for May, 2012

Identifying high CPU SQL Server threads

Some great tips are available to those of us who subscribe to the SQLSkills Insiders email list ( The 29th April 2012 email featured a video by Jonathan Kehayias demonstrating how to use Process Explorer to identify high CPU SQL Server threads. Jonathan showed that once you’ve identified the thread IDs you can use the following SQL to obtain the query and plan details:

-- Source: SQLSkills Insiders email 29/04/2012
-- Use the system thread id to find the executing session information
SELECT r.session_id, st.text, qp.query_plan
FROM sys.dm_os_threads AS ot
JOIN sys.dm_os_tasks AS t
ON t.worker_address = ot.worker_address
JOIN sys.dm_exec_requests AS r
ON t.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE os_thread_id = <YourThreadID>

Today’s blog post presents a Powershell script that can be used in multi-instance environments (eg clusters) to identify high CPU SQL Server threads. The script uses the performance monitor counters for threads.

The long standing issue for multiple instances is that performance monitor differentiates each running sqlservr.exe process using a hash followed by a number. Trying to relate these hashes to the actual SQL Server instances can be difficult. For example, if you have three instances of SQL Server the instance names for the threads performance counters will be as follows:

For the first SQL Server instance: \\computername\thread(sqlservr/0)\<countername> to \\computername\thread(sqlservr/xxx)\<countername> where xxx is the number of threads you have for this instance

For the second SQL Server instance: \\computername\thread(sqlservr/0#1)\<countername> to \\computername\thread(sqlservr/xxx#1)\<countername>

For the third SQL Server instance: \\computername\thread(sqlservr/0#2)\<countername> to \\computername\thread(sqlservr/xxx#2)\<countername>

Using the registry change listed in the following Microsoft support document

it is possible to replace the hashes with process IDs. We then have meaningful names that we can relate back to our SQL Server instances. Along the same lines as ProcessNameFormat, another document

mentions ThreadNameFormat. Using both these format keys we are able replace the meaningless hash symbol and /0, /1, /2 with meaningful names. Our full counter names will now appear as follows:


With these registry changes in place the Powershell script simply fetches five samplings of the thread % processor time. For each distinct process ID it groups on processID/threadID (eg 3012/4896 for the example above) and calculates the average, minimum, maximum and count for the five samplings. It finally sorts by the average descending and selects the first five rows.


$PerfCounter = "Thread(sqlservr*)\% Processor Time"

$MaxSamples = 5

$ThreadData = Get-Counter -ComputerName $ComputerName -Counter $PerfCounter -MaxSamples $MaxSamples -ErrorAction:SilentlyContinue |
ForEach-Object {$_.CounterSamples | Select-Object Timestamp,CookedValue,Path}

# Distinct process IDs
$PerfCounterPrefix = $ThreadData | Select-Object -Unique @{Name="PerfCounterPrefix";Expression={ $_.Path.SubString(0, $_.Path.IndexOf("/"))}}

# For each of the distinct process IDs select the five threads with the highest average % processor time
ForEach ($Prefix in $PerfCounterPrefix) {
$ThreadData |
Where-Object {$_.Path -match (Select-Object -ExpandProperty $Prefix)} |
# This groups by ProcessID/ThreadID
Group-Object -Property { $_.Path.SubString($_.Path.IndexOf("_") + 1, $_.Path.IndexOf(")") - $_.Path.IndexOf("_") - 1) } |
Select-Object `
@{Name="ProcessID";Expression={($_.Name -split "/")[0]}}, # Left side of / is ProcessID
@{Name="ThreadID";Expression={($_.Name -split "/")[1]}},  # Right side of / is ThreadID
@{Name="PercentProcessorAvg";Expression={[Math]::Round(($_.Group | Measure-Object -Property CookedValue -Average).Average,2)}},
@{Name="PercentProcessorMin";Expression={[Math]::Round(($_.Group | Measure-Object -Property CookedValue -Minimum).Minimum,2)}},
@{Name="PercentProcessorMax";Expression={[Math]::Round(($_.Group | Measure-Object -Property CookedValue -Maximum).Maximum,2)}},
@{Name="NumberOfSamples";Expression={[Math]::Round(($_.Group | Measure-Object).Count,2)}} |
Sort-Object -Property PercentProcessorAvg -Descending |
Select-Object -First 5 |
Format-Table -Auto

Please take note of the line continuation back ticks.

Example output:

For a multi-instance server you’ll have five rows for each process ID. You can identify the named instance by plugging the process ID into this (replace xxxx):

(Get-WmiObject Win32_Service -ComputerName $ComputerName -Filter “ProcessId=xxxx”).Name

You can use this same approach to identify other performance metrics at the thread or process level.

Leave a comment

Using Powershell to obtain storage utilisation

From SQL 2008 R2 SP 1 onwards the sys.dm_os_volume_stats dynamic management function returns the storage details for disk volumes:

It’s possible to generate various type of storage utilisation reports using this DMF.

For prior versions we would have to query the file systems using non-SQL methods (eg VBScript, CLR stored procedures, extended stored procedures). We’d combine this file system data with our database usage data to get an overall storage picture. Powershell along with the SQL Server Management Objects (SMO) provides another way to obtain this information. This post shows that the approach is not complicated and, as a bonus, provides us with a very flexible way of presenting the results. The code is as follows:

# Tested using Powershell V2

# SQLServer can be default instance, named instance or [server,port]

Param([string]$SQLServer = "YOURSERVER,1433")

$WinServer = ($SQLServer -split {$_ -eq "," -or $_ -eq "\"})[0]

# Fetch volume information

$VolumeTotalGB = @{Name="VolumeTotalGB";Expression={[Math]::Round(($_.Capacity/1GB),2)}}
$VolumeUsedGB = @{Name="VolumeUsedGB";Expression={[Math]::Round((($_.Capacity - $_.FreeSpace)/1GB),2)}}

$VolumeArray = Get-WmiObject -Computer $WinServer Win32_Volume | Where-Object {$_.FileSystem -eq "NTFS"} | `
Select-Object Name, $VolumeTotalGB, $VolumeUsedGB

# Fetch data and log file information

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$Instance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer

$FileTypeData = @{Name="FileType";Expression={"Data"}}
$FileTypeLog = @{Name="FileType";Expression={"Log"}}
$FileTotalGB = @{Name="FileTotalGB";Expression={[Math]::Round(($_.Size*1KB/1GB),2)}}
$FileUsedGB = @{Name="FileUsedGB";Expression={[Math]::Round(($_.UsedSpace*1KB/1GB),2)}}
$Volume = @{Name="Volume";Expression={}}

# Data

$FileArray = $Instance.Databases | Where-Object { $_.IsAccessible } | `
ForEach-Object { $_.FileGroups | ForEach-Object { $_.Files | Select-Object $FileTypeData, FileName, $FileTotalGB, $FileUsedGB, $Volume } }


$FileArray += $Instance.Databases | Where-Object { $_.IsAccessible } | `
ForEach-Object { $_.LogFiles | Select-Object $FileTypeLog, FileName, $FileTotalGB, $FileUsedGB, $Volume }

# Append volume information to data file information

$FileArray | ForEach-Object {
$FileName = $_.FileName
$_.Volume = $VolumeArray | Where-Object { $FileName -Match [regex]::Escape($_.Name)} | Sort-Object Name -Descending | Select-Object -First 1

# Sum the file size and space used by volume and file type and output the results

$SystemName = @{Name="SQLServer";Expression={$SQLServer}}
$VolumeName = @{Name="Volume";Expression={($_.Name -split ", ")[0]}}
$AggVolumeTotalGB = @{Name="VolumeTotalGB";Expression={($_.Group)[0].Volume.VolumeTotalGB}}
$AggVolumeUsedGB = @{Name="VolumeUsedGB";Expression={($_.Group)[0].Volume.VolumeUsedGB}}
$FileType = @{Name="FileType";Expression={($_.Name -split ", ")[1]}}
$AggFileTotalGB = @{Name="FileTotalGB";Expression={($_.Group | Measure-Object -Property FileTotalGB -Sum).Sum}}
$AggFileUsedGB = @{Name="FileUsedGB";Expression={($_.Group | Measure-Object -Property FileUsedGB -Sum).Sum}}
$AggFileCount = @{Name="FileCount";Expression={($_.Group | Measure-Object).Count}}

$FileArray | Group-Object -Property {$_.Volume.Name},{$_.FileType} | `
Select-Object $SystemName, $VolumeName, $AggVolumeTotalGB, $AggVolumeUsedGB, $FileType, $AggFileTotalGB, $AggFileUsedGB, $AggFileCount | Out-GridView

Please observe the line continuation back ticks.

There are very few actual executable lines in this script. The only parameter we set is the one for the SQLServer variable. Any backslash in the name (for a named instance) or comma (for a port separator) is stripped away to give us the Windows server name. This will work for clustered SQL Servers where the WinServer variable will take the virtual server name. The WMI Win32_Volume class is used to retrieve the capacity and free space for each NTFS volume. This class will cater for root volumes as well as well as mount points. We store the data in an array named VolumeArray

SMO is used to fetch data file and log file information (file name, size and space used). The IsAccessible property is checked to ensure that we process data for online databases only. This data is stored in an array named FileArray.

The only complicated part of the code is where we append the volume data to the file data. The idea is to create one array holding file as well as volume data. You can see that we’re aiming for the same result set we see for the screenshot for SQL Server 2012. The code achieves this by doing regex matches between the full file names and the volume names. The volume name will appear in the leading part of the full file name. If you are using mount points you’ll have two matches – one for the root volume (eg D:\) and another for the mount point (eg D:\SQLDATABASE\DATA). We obtain the mount point by doing a descending sort on matched volume names and selecting the first one. This matched volume’s data is stored in the Volume property (PSCustomObject) alongside all the other file properties for this file.

You could finish at this point and simply output FileArray to the screen, CSV, XML or HTML. The code listing goes a little further and does the equivalent of a SQL Server SUM(FileSize), SUM(FileUsedSpace) GROUP BY VolumeName, FileType. (FileType represents Data or Log). Group-Object and Measure-Object are very useful aggregation commandlets. I’m using them here to return just one of many possible result sets:

The script analyses database and log files only. Backup space utilisation could be analysed using a similar approach.

Leave a comment