One of the first things to do is gather baseline metrics so you can actually tell if your CPU is really “high”. As a rule of thumb, anything over 80% for 10 seconds is high. There is a short list of items that are usually ok to cause high CPU and a long list that is not so good. Fortunately this is something that can usually be fixed without an outage. If you found out about your high CPU from your users it might be time to invest in some monitoring software.
If you can connect to the instance the first thing you’ll probably want to do is sp_who. This is a very light duty way of looking at the current connections to the instance.
Next, remotely take a look at the server’s event log and the sql logs. They are another, minimally invasive, way to collect hints to your problem quickly.
If you don’t have any stand-out items to investigate, you might consider logging into the server to verify that the sqlserver process is the culprit. This will rule out things like virus scan or backup software running a muk. At this point, check all of your drives free space %. A full log file can cause high CPU.
If you have determined that the sqlserver process is the problem, your job gets a bit difficult. If sp_who, sp_who2, the activity monitor or my favorite sp_whoisactive don’t show you anything out of the ordinary, you are in this for the long haul. Based on the server, now might be a good time to ask for the “reboot” option from your users. Its worth a shot and at least it lets your users know you are willing to admit there is a problem and try something.
Most of the databases I have worked with I had no control over the design. It was a third party application that had a support line that someone would call if the reboot option didn’t work. Often times support would ask for a trace. SQL Server Profiler is under performance tools and can be run remotely on your sql instance. This data is vital debugging data that I do not claim to be an expert in. To get started check out this article
Some of the more difficult items to locate are mentioned at the bottom of the article:
“High CPU problems may indicate large numbers of stored procedure recompilations, ad-hoc query compilations, or intensive use of hash and merge joins.”