dbatoolsGet-DbaCpuUsage
View SourceSynopsis
Correlates SQL Server processes with Windows threads to identify which queries are consuming CPU resources
Description
When CPU usage is high on your SQL Server, it can be difficult to pinpoint which specific SQL queries or processes are responsible using standard SQL Server tools alone. This function bridges that gap by correlating SQL Server process IDs (SPIDs) with Windows kernel process IDs (KPIDs) through system DMVs and Windows performance counters.
The function queries both SQL Server’s process information and Windows thread performance data, then matches them together to show you exactly which SQL queries are consuming CPU at the operating system level. This is particularly valuable during performance troubleshooting when you need to identify the root cause of high CPU usage.
Results include detailed thread information such as processor time percentages, thread states, wait reasons, and the actual SQL queries being executed. You can also set a CPU threshold to focus only on processes exceeding a specific percentage.
Note: This command returns results from all SQL instances on the destination server but the process
column is specific to -SqlInstance passed.
Syntax
Get-DbaCpuUsage
[-SqlInstance] <DbaInstanceParameter[]>
[[-SqlCredential] <PSCredential>]
[[-Credential] <PSCredential>]
[[-Threshold] <Int32>]
[-EnableException]
[<CommonParameters>]
Examples
Example: 1
PS C:\> Get-DbaCpuUsage -SqlInstance sql2017
Logs into the SQL Server instance “sql2017” and also the Computer itself (via WMI) to gather information
Example: 2
PS C:\> $usage = Get-DbaCpuUsage -SqlInstance sql2017
PS C:\> $usage.Process
Explores the processes (from Get-DbaProcess) associated with the usage results
Example: 3
PS C:\> Get-DbaCpuUsage -SqlInstance sql2017 -SqlCredential sqladmin -Credential ad\sqldba
Logs into the SQL instance using the SQL Login ‘sqladmin’ and then Windows instance as ‘ad\sqldba’
Required Parameters
-SqlInstance
The target SQL Server instance or instances.
| Property | Value |
|---|---|
| Alias | |
| Required | True |
| Pipeline | true (ByValue) |
| Default Value |
Optional Parameters
-SqlCredential
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-Credential
Allows you to login to the Windows Server using alternative credentials.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-Threshold
Filters results to only show SQL Server threads with CPU usage at or above this percentage.
Use this to focus on high-CPU consuming processes and ignore idle or low-activity threads during performance troubleshooting.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
-EnableException
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Outputs
Win32_PerfFormattedData_PerfProc_Thread (with added properties)
Returns one object per Windows thread of SQL Server processes with CPU usage at or above the specified threshold.
Default display properties (via Select-DefaultView):
- ComputerName: The computer name of the SQL Server instance
- InstanceName: The SQL Server instance name
- SqlInstance: The full SQL Server instance name (computer\instance)
- Name: The thread identifier in format ‘ProcessName_ProcessID_ThreadID’
- ContextSwitchesPersec: Number of context switches per second for this thread
- ElapsedTime: Time in seconds since the thread was created
- IDProcess: Windows process ID (PID) of the SQL Server process
- Spid: SQL Server session ID (SPID) associated with this thread
- PercentPrivilegedTime: Percentage of time thread spent in privileged mode
- PercentProcessorTime: Percentage of total processor time consumed by this thread
- PercentUserTime: Percentage of time thread spent in user mode
- PriorityBase: The base priority of the thread
- PriorityCurrent: The current priority of the thread
- StartAddress: Memory address where the thread code begins execution
- ThreadStateValue: Human-readable description of the thread state (e.g., ‘Running’, ‘Waiting’)
- ThreadWaitReasonValue: Human-readable description of the wait reason if thread is waiting
- Process: Associated SQL Server process object from Get-DbaProcess
- Query: The last T-SQL query executed by the process
Additional properties available (from Win32_PerfFormattedData_PerfProc_Thread):
- IDThread: Windows thread ID
- ThreadState: Numeric value representing thread state (0=Initialized, 1=Ready, 2=Running, 3=Standby, 4=Terminated, 5=Waiting, 6=Transition, 7=Unknown)
- ThreadWaitReason: Numeric value representing the reason the thread is waiting All properties from the Win32_PerfFormattedData_PerfProc_Thread WMI class are accessible using Select-Object *.