Find out which SQL Server instance is consuming most of the CPU

By:   |   Updated: 2012-09-17   |   Comments (13)   |   Related: 1 | 2 | 3 | 4 | > Performance Tuning


Problem

We have a number of SQL Server hosts with multiple SQL Server instances.  From time to time we have CPU issues, but we are not sure which instance is causing the issue.  How do you find which SQL Server instance is causing CPU pressure on machine with multiple SQL Server instances? Check out this tip for ideas on how to find the correct SQL Server instance which is causing CPU pressure.

Solution

Today, some of the users reported that one of their applications is running very slow. I logged in on the database server to check what is going wrong on the database server. The issue was CPU pressure. One of the SQL Server Instance was taking most of the CPU, but we had three SQL server instances on this box, so it was quite difficult to find the correct SQL Server instance which is utilizing all of the CPU.

Steps to identify the SQL Server instance utilizing most of the CPU

Step 1:- First launch Windows Task Manager to find out the CPU utilization for your database server. Below is screenshot of the Task Manager at the time of the issue I experienced. You can see all three instances have the same executable i.e. 'sqlservr.exe' and also you can see 76% of the CPU is being utilized by the first sqlservr.exe process.

Windows Task Manager to see CPU usage

Based on reviewing Windows Task Manager, one immediate option to determine which SQL Server instance is using all of the CPU is to run each SQL Server instances with a different domain account.  For example, "Domain\SQLDev" for the development environment, "Domain\SQLTest" for the test environment, etc.  Unfortunately, in my circumstance all of the SQL Server instances were running under same domain account. In the image above the accounts have been erased, but they would be found in the fourth column i.e. "User Name". 

Step 2:- Now we will add the PID (Process Identifier) column in Windows Task Manager to in order to find out the PID for each process.  The PID is the Windows Operating System Process Identifier assigned to each process running on the machine. In order to enable this column, ensure the "Processes" tab is active then click on the "View" menu, then choose the "Select Columns..." option.  Once on the "Select Process Page Columns" screen click the check box for the option PID and then press the "OK" button to return to the Processes tab of Windows Task Manager.

Add PID column in Windows Task Manager

Step 3:- Now you can see the PID for the 'sqlservr.exe' process which is utilizing most of the CPU. In our example, the PID of this SQL Server instance is 2352.

Task Manager with PID value for SQL Server

Now our next step is to determine which SQL Server instance is running this PID. We have two methods to get this information. First, is the SQL Server configuration manager and second method is the SQL Server error log.

From SQL Server 2005 onwards, it is very easy to find the PID for the SQL Server instances using the SQL Server Configuration manager.  However, with SQL Server 2000 and earlier, it is not as straight forward.  We will proceed with the assumption that community members are using SQL Server 2005 and beyond.

Step 4:- Whenever we start a SQL Server instance, a PID which is also know as the "Server process ID" is assigned to that instance and this information is logged in the SQL Server error log. You can see an example of this in the screenshot below.  The "Server process ID" is normally one of the first entries in the log.

SQL Server error log to determine the PID for the SQL Server processes

Step 5:-Another option is identify the correct PID for your SQL Server Instance is by using the SQL Server configuration manager. This can be accomplished by launching the  SQL Server Configuration Manager and clicking on the "SQL Server Services" option in the left pane. On right side of this interface, you can see the Process ID values associated with the SQL Server services.

Use SQL Server Configuration Manager to determine the PID per SQL Server instance

By correlating the information from Windows Task Manager and the SQL Server Error Log\SQL Server Configuration Manager you can correctly determine the SQL Server instance which is utilizing most of the CPU.  At this point you can review the SQL Server processes on the aforementioned instance to determine the culprit process(es) which are causing CPU pressure.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-09-17

Comments For This Article




Friday, April 3, 2015 - 2:40:39 PM - Nagesh Back To Top (36819)

Dear Manvendra,

Thanks for the nice article.

 

Regards,

Nagesh

Friday, July 11, 2014 - 2:35:49 AM - how to find task manager or PID in old version of window Back To Top (32647)


Friday, May 9, 2014 - 8:08:31 AM - ravi Back To Top (30699)

Nice one

Wednesday, March 20, 2013 - 2:29:25 PM - Rob Volk Back To Top (22914)

Or just query each instance:

SELECT @@SERVERNAME, SERVERPROPERTY('ProcessID')

Glenn Berry also has a query for sys.dm_os_ring_buffers to determine CPU utilization by SQL and other processes, it's easy to Google.

Wednesday, February 27, 2013 - 4:10:38 PM - JustHappenedToBeHere Back To Top (22452)

Good article. Instead of PID column in Windows Task Manager, I just add the "Command Line" column to it and then scroll to the right to find out what SQL Server instance name is running. 

 

 

Monday, February 25, 2013 - 11:11:20 AM - Image Path Back To Top (22404)

As Joachim mentioned, the article is quite out of date.

If you're running on Windows 2000, yes, you might need to do this, find an old version of Sysinternals Process Explorer (12 does still work, and has both Image Path and Command Line options), or use

wmic /node:"server" process

from a more current machine (using an domain permissioned account, or using wmic flags to use a local account and password with sufficient permissions), which gives image path, command line, and processid.

Windows 2003 R2, same as the above, but wmic will run locally as well.

Windows 2008 is probably like 2008 R2.

Windows 2008 R2, in Task Manager, select View, Select Columns, and pick either or both of Image Path Name, Command Line.

Windows 2012 is also probably like 2008 R2.

In no case should trolling through multiple SQL Server log files be required or efficient.

 

 

 

Thursday, September 27, 2012 - 8:56:54 AM - Joachim Back To Top (19706)

I just select the column Command Line in the task manager.  The -s option gives the instance.   That works from Windows 2008 on.  On Windows 2003 I use procexp  (sysinternals taskmanager) .  It can also be done on the commandline with wmic process,  but it is a long time ago I did that.

Wednesday, September 26, 2012 - 10:15:42 AM - Manvendra Back To Top (19695)

Agree with Alan and Tim. But this tip is useful in a env where your all Instances are using same account to run all sql server services.

Wednesday, September 26, 2012 - 9:18:33 AM - Alan Back To Top (19691)

Tim, that's exactly what we do. By having the service account mimic the instance name you never have to go digging through logs and such just to identify the culprit. I believe I read somewhere that Microsoft reccommends do this as well.

Wednesday, September 26, 2012 - 4:43:35 AM - Labib Back To Top (19685)

Good post, this issue might be faced frequently.

Wednesday, September 19, 2012 - 1:32:45 PM - Raj Back To Top (19575)

VERY NICE

Monday, September 17, 2012 - 1:37:03 PM - Umair Back To Top (19537)

Great post.

 

Monday, September 17, 2012 - 9:39:55 AM - Tim Back To Top (19534)

Another technique is to create Service accounts mimicing the instance name.  At that point, you can just scroll over in Task Manager and see what instance is causing the issue.














get free sql tips
agree to terms