Query Tuning in SQL: Fixing Instance-CPU Consumption Issue

There are several SQL Server hosts containing numerous instances while a database management system operates. These instances consume resources - some of them significantly more than others - causing CPU issues.

However, it can be difficult to locate the exact cause of this problem while performing query tuning in SQL. In this article, we will uncover which SQL Server instance is taking up most of the CPU resources.

A common indicator of excess CPU pressure is when one or more applications run too slow, so get it checked by a professional if you face such an issue. It is easier for them to detect what's wrong by logging into the database server.

Here's what they do when they have multiple SQL instances running on the database and they have to determine which one among them is utilizing the maximum resources:

The first step is to open the Task Manager to check the database server's CPU usage. You will be able to locate all the executables and their CPU usage here. If all of them show the same percentage, a quick option at this point to find the culprit is to operate every instance using a separate domain account, such as SQLTest or SQLDev.

However, if they're all running under a single domain account, we would have to perform the next step - adding the PID or process identifier column in the task manager.

Every process getting executed on the system has a Process Identifier or PID assigned to it in Windows. You only have to enable that column by making sure the "Processes" tab is visible and clicking on the "Select Process Page Columns" section from the View option to select the PID option.

After you have clicked on the PID checkbox, click OK to save your changes and return to the Processes window of the Task Manager so you can resume database query optimization.

Now, you will be able to look at the unique PID of every 'sqlservr.exe' process - and find out how much CPU resources each one is consuming. Once we're able to do this, the next step is to uncover the exact SQL Server instance that is behind the PID, which we can do with the help of two simple ways.

The first method involves opening the SQL Server configuration manager, whereas the alternative is to take a look at the SQL Server error log.

Note: Earlier versions of the SQL Server - SQL Server 2000 and older - didn't have straightforward techniques to locate the PID for SQL Server instances. These methods were made available through the SQL Server manager from SQL Server 2005 onwards.

The next step in the path towards query tuning in SQL, and after finding the PID or the Server process ID, and the exact SQL Server instance that has the particular PID, is to open the SQL Server error log. This is because every time a SQL Server instance is created, the server process ID provided to that instance is recorded in this log. Moreover, this information is among the initial entries recorded in the error log.

If you wish to find the right PID of each SQL Server instance using the SQL Server configuration manager, simply launch the same and select the option that says "SQL Server Services", it is likely to be located on the top left side of the menu. Right next to it, you will be able to spot the PID numbers linked to each and every instance.

Using the information you have just gathered, you can determine which instance is taking up the most resources during query performance tuning. All you have to do now is correlate the data collected from Windows Task Manager and the application you chose among the SQL Server configuration manager and the SQL Server error log.