Sometimes a Microsoft SQL Server can have a very high CPU usage, but it’s not always obvious how to troubleshoot this. To troubleshoot high CPU usage in SQL Server, take the following steps:
- First establish whether the SQL server with high CPU utilisation is suffering high CPU usage because of SQL or another service. You can do this with the Microsoft Task Manager (Ctrl+Alt+Delete -> Task Manager)
- Once you’ve confirmed that the high CPU utilisation is due to the SQL service, you need to find out what query within SQL is using all the CPU. To do this, start Microsoft SQL Server Management Studio and log into the SQL server. Next right click on the SQL server and click Activity Monitor. Click on Processes and wait (it takes a while) for the SQL queries that are running to show up. From here you should be able to see which queries are causing the high CPU usage
- Once you’ve established which queries are causing high CPU utilisation, these queries / stored procedures need to be examined for logic errors / loops. This is just a case of reading through them and making sure that they are efficient
- If the SQL queries are efficient, the database may need a tweak. Check that the offending query is searching based on indexed keys
- If the query is aligned with the database indexing, perhaps you need a SQL job that updates the stats on the database (you can update stats with a SQL job via a command or through the GUI job builder)
- If update stats isn’t working, it may be that your database and queries are working efficiently and the server is experiencing high CPU usage because the server hardware is under-spec’d for the queries you are running and you need to upgrade your server hardware.
VN:F [1.9.22_1171]Troubleshooting SQL Server High CPU Usage,