Before you can start your normal SQL performance tuning with profiler, DMVs, or whatever your normal routine is, you’ll need to be sure which SQL instance on that box is the cause of the trouble.
With just a couple of SQL instances on a box (maybe two different versions of SQL), it isn’t too hard to figure out where the trouble lies. But with the increased use of virtualization and consolidation environments like Polyserve, servers with 5 or more distinct SQL instances aren’t that far-fetched.
This makes troubleshooting a “High CPU on HOST123″ alert a little difficult. And if someone is complaining that a certain application is slow, how do you know if that application is really at fault, or if that slowness is simply a symptom of very high CPU on an entirely different instance?
The key involves knowing the ProcessID for each running instance of SQL. Here are a few ways to find out the ProcessID:
1. Look in your SQL server log, back to when the server was last restarted. It should contain a row that looks something like: “Server Process ID is 6404.”
2. Run a query on each server:
SELECT serverproperty('ServerName') AS Instance, serverproperty('ProcessID') AS PID
3. Use a cool PowerShell script that Allen White describes in this blog post
THE FAST WAY
If you need to see what’s using up the most CPU on the server right now, just use the Processes tab in Task Manager.
The trick is to go into View, Select Columns, and check the “PID (Process Identifier)” column. I also check the “CPU Time” column so I can see how much cumulative time each instance has used (although if the instances haven’t been all running the same length of time, this may not be a good comparison).
Now sort the process list by name, and you should be able to tell which SQL server instance is using the most CPU currently:
THE HARD WAY
If you need to see CPU usage over a period of time, or in a specific time window, you will have to use Performance Monitor. (See Brent’s excellent Perfmon Tutorial and video here to get you started).
In this case, the trick is to capture the “Process: % Processor Time” counters for each of the “sqlservr” processes. Also make sure you capture the “Process:ID Process” counter, so you can match the ProcessID in the log to the ProcessID of each SQL instance as we’ve described above.
NOTE: Some have reported that the sqlservr processes are always listed in ascending order by ProcessID. I have found this not to be true. Not sure why it worked for him but not for me, but if you include the “ID Process” counter, you won’t have to guess.
In actuality, I save my Perfmon counter logs to CSV files so I can easily open them up in Excel. For the sake of this article, though, I’ll show a sample of the perfmon graph view:
So during at least the first part of this monitoring period, sqlservr#2 is the instance using the most CPU.
Normally all you need to determine is which one is highest, but if you are a stickler for accurate numbers, and you noticed that the individual Process:% Processor Time numbers add up to more than the total Processor:% Processor Time, that’s because the Process values are actually out of 100*(#of CPUs). In my case, this means that the max is 400%. (Or, if you want to be really pedantic, its actually a percentage of the “Processor:% User Time” counter value. See this thread for additional formulas and maths and stuff.)