{"id":71691,"date":"2017-07-17T13:57:00","date_gmt":"2017-07-17T13:57:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=71691"},"modified":"2021-08-24T13:39:23","modified_gmt":"2021-08-24T13:39:23","slug":"investigating-cause-sql-server-high-cpu-load-conditions-happen","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/investigating-cause-sql-server-high-cpu-load-conditions-happen\/","title":{"rendered":"SQL Server High CPU: Investigating the Causes"},"content":{"rendered":"

Catching them in the act<\/h2>\n

There are plenty of ways of finding out what processes are consuming CPU, killing your database\u2019s performance. Beginning with SQL Server 2016, it\u2019s become so much easier to monitor performance in SQL Server with Query Store, but there are plenty of techniques that are still useful to investigate SQL Server high CPU, particularly if you don\u2019t have SQL Server 2016 Query Store on the database that you\u2019re checking.\u00a0<\/p>\n

I have my favourite techniques for doing this: I like to use both process explorer and sp_whoisactive<\/strong> to find the queries that are taking too much CPU in SQL Server, but I like to run them at the actual moment that the heavy usage happens, even if I\u2019m actually at home relaxing. In this article, I will show you how to use both these tools and, especially, how to do it in real time when the CPU needle is \u2018hitting the red\u2019. It is like catching a close-up of a villain in the floodlights at the very moment of the crime. I am talking about a technique where you set up an event-driven solution that will be fired by a WMI event when the CPU is high, thereby avoiding pooling and jobs having to running every few seconds. When it fires, the process checks if the source of the WMI event is SQL Server and then logs in and executes the statements for you. Yes, you don\u2019t need to be in the front of your workstation; you can check later and then perform whatever corrective action needs to be done in the light of the data you\u2019ve captured.<\/p>\n

One of these techniques, using Process Explorer, is very nicely described here<\/a> by Michael J Swart: Note that he updated the post, explaining that now he uses sp_whoisactive. I will show how to do it using both (Michael J Swart – Identifying High CPU SQL Processes<\/a>)<\/p>\n

Whatever the query you prefer to use, the big question will be how to do it in real time when the problem is actually happening, and log whatever information you need, even on the unattended server. There are plenty of times you need to do this, especially if you don\u2019t have a full-time DBA or if you are running in the cloud and needs some support from the cloud provider. You can help the support Engineer by sending him the queries that are breaking your system. In AWS, this kind of service is out of scope of support, but if you have luck to find an Engineer that knows SQL Server and is willing to help you, as I was, it will, help him or her to help you to tune the queries. You just need to leave the solution and then get the CSV log with the queries.<\/p>\n

This is how the solution works:<\/p>\n

\"SQL<\/p>\n

Because the PowerShell script needs to modify the registry to change the output from the Get-Counter [1], the account that will runs the PowerShell Script will need to have administrative rights; the SQL Server Agent Service account in this case.<\/p>\n

In the case of AWS EC2 with SQL Server ( but not RDS, since this solution does not works in RDS), the account that is installed By default is the NT Service\\SQLServerAgent, which does not have the appropriate permissions. For that we have two options :<\/p>\n

    \n
  1. Change the SQL Server Agent Account to LocalSystem or an appropriate service account with elevated rights. Server Configuration – Service Accounts<\/a><\/li>\n
  2. Create a SQL Proxy account to run the PowerShell Script. That is what we will use.<\/li>\n<\/ol>\n

    [1] See the following references \u2013 The Process object in Performance Monitor can display Process IDs (PIDs)<\/a> and Handling Duplicate Instance Names<\/a><\/p>\n

    Configuring Permissions to run the Script<\/h2>\n

    Open SSMS :<\/p>\n

    Create a Credential<\/h3>\n

    \u2018Security\u2019<\/em> -> \u2018Credentials\u2019<\/em>->\u2019New Credential\u2019<\/em> and define a name and identity for the credential<\/p>\n

    <\/p>\n

    <\/p>\n

    Create a SQL Proxy Account<\/h3>\n

    \u2018SQL Serv<\/em>e<\/em><\/strong>r Agent\u2019<\/em>->\u2019Proxies<\/em>\u2019->\u2019Operating System(CmdExec)<\/em>\u2019->\u2019New Proxy\u2019<\/em> (Yellow box)<\/p>\n

    Type the ProxyName<\/strong> and associate this with the previous credential account created (Green box)<\/p>\n

    Check box what the account can do -> \u2018Operating System(CmdExec)<\/em>\u2019 (checking \u2018PowerShell\u2019<\/em> is optional because the SQL Server job that will be created is a CmdExec Job and not PowerShell job)<\/p>\n

    <\/p>\n

    Create the SQL Server Agent Job to call the PowerShell Script<\/h2>\n

    Create a path and save the HighCPU.ps1<\/em><\/strong>and ConfigHighCpu.csv<\/em><\/strong> files in this path ie: C:\\temp<\/p>\n

    Go to \u2018SSMS<\/em>\u2019->\u2019SQL Server Agent\u2019<\/em> -> \u2018Jobs<\/em>\u2019 -> \u2018New Job\u2019<\/em> (Red)<\/p>\n

    In the tab \u2018General\u2019<\/em> (on the left side), type the name of the Job and click in the Step tab (Yellow box)<\/p>\n

    Type (see the Green box):<\/p>\n