--- title: "Get-DbaCpuUsage" description: "Correlates SQL Server processes with Windows threads to identify which queries are consuming CPU resources" url: "https://dbatools.io/Get-DbaCpuUsage/" availability: "Windows only" tags: ["Diagnostic", "Performance", "CPU"] author: "Chrissy LeMaire (@cl), netnerds.net" source: "https://github.com/dataplat/dbatools/blob/master/public/Get-DbaCpuUsage.ps1" last_updated: "2024-01-01" --- # Get-DbaCpuUsage ## Synopsis 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. References: https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/ Note: This command returns results from all SQL instances on the destination server but the process column is specific to -SqlInstance passed. ## Syntax ```powershell Get-DbaCpuUsage [-SqlInstance] [[-SqlCredential] ] [[-Credential] ] [[-Threshold] ] [-EnableException] [] ``` ## Examples ### Example 1: Logs into the SQL Server instance "sql2017" and also the Computer itself (via WMI) to gather information ```powershell PS C:\> Get-DbaCpuUsage -SqlInstance sql2017 ``` ### Example 2: Explores the processes (from Get-DbaProcess) associated with the usage results ```powershell PS C:\> $usage = Get-DbaCpuUsage -SqlInstance sql2017 PS C:\> $usage.Process ``` ### Example 3: Logs into the SQL instance using the SQL Login 'sqladmin' and then Windows instance as 'ad\sqldba' ```powershell PS C:\> Get-DbaCpuUsage -SqlInstance sql2017 -SqlCredential sqladmin -Credential 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 *. --- Part of [dbatools](https://dbatools.io/), a free and open source PowerShell module for SQL Server administration. Full command index: https://dbatools.io/commands/