Windows 10 Hwo to Know What Processor You Have

How to discover out how much CPU a SQL Server procedure is really using

Past:   |   Updated: 2011-08-09   |   Comments (60)   |   Related: 1 | 2 | iii | 4 | More than > Functioning Tuning

Free MSSQLTips Webinar: Identify SQL Server Functioning Issues Rapidly and Efficiently

Optimal SQL Server performance remains a high priority for DBAs and Developers. Unfortunately, information technology is very difficult to identify the root cause of the result, optimize the query, and validate the changes. SQLGrease can provide a tremendous amount of insight, time savings and functioning improvements. SQLGrease is a performance monitoring tool that provides deep insight into performance bug down to the query level beyond all of your instances in the deject and on-premises.

Problem

Someone has reported a functioning issue for your SQL Server application. When you look into the database server you see CPU utilization is very high and the SQL Server process is consuming nigh of the CPU. You lot launch SSMS and run sp_who2 and notice that there are a few SPIDs taking a long fourth dimension to complete and these queries may be causing the loftier CPU pressure. At the server level you can only meet the overall SQL Server process, but inside SQL Server you can see each private query that is running. Is there a fashion to tell how much CPU each SQL Server procedure is consuming? In this commodity I explain how this can be done.

Solution

Normally we monitor this type of outcome using sp_who2, Action Monitor or DMVs. If at that place are a lot of processes running on your instance and the CPU is very high, then it's hard to discover the exact process eating upward your CPU using just the SQL Server tools. One way to correlate the data between what is running inside SQL Server and at the Windows level is to use SPID and KPID values to get the exact process.

Before discussing how to do this, nosotros should hash out these terms:

• SPID is the SQL Server Process ID number and is assigned past SQL Server to each new connection. It starts with one and is globally unique. SPID 1 through 50 are reserved for organisation uses and are not used for any user connections.
• KPID is the kernel-process ID. Under SQL Server for Windows this is the thread ID number, also known as "ID Thread," and is assigned by Windows when the thread is created. The Thread ID number is a system-wide identifier that uniquely identifies the thread. KPID is visible by querying the KPID column of master..sysprocesses. It is merely filled in for spid numbers four and higher. You can also get KPID/ID Thread from Windows Perfmon using the "Thread" object and the "ID Thread" counter.

I am sure you are familiar with the SPID value, but have you ever noticed KPID? Probably non unless you queried the sysprocesses view directly. Let's wait at example of how this works.

---

Instance

Step 1
If we look at Job Manager on the server nosotros tin run across the CPU usage. So the outset step is to verify that the SQL Server process (sqlservr), and not some other process, is responsible for the excessive CPU utilise. Remember, the "sqlservr" process is made up of many different threads, each one by and large (but non e'er) representing a specific user connectedness. In the screenshot below nosotros see the sqlservr procedure is taking approximately 70 percent of CPU.

At present that we know SQL Server is the principal culprit and responsible for the high CPU, the next pace is to find out which SQL Server process is causing the high CPU.

Step ii
At present we need to find out what SQL Server thread is causing the high CPU. Once nosotros take this ID Thread we tin can correlate that ID thread (KPID) to the SPID in SQL Server.

We will utilise Performance Monitor to get this info. Type perfmon in a Windows CMD prompt or launch from Control Panel.

Click on Add together counters and select the "Thread" object in the drop down.

Select these counters at the same time:

• % Processor Fourth dimension
• ID Thread
• Thread State
• Thread Wait Reason

In the right pane, you will see multiple instances from multiple applications, which are running on that server. Since we are looking for "sqlservr" select all of the instances that begin with "sqlservr" from the list box every bit shown beneath and click Add together. You tin add together other counters as needed and volition encounter below that I added a few boosted counters.

Footstep 3
Printing (Ctrl+R) or click on the view Report tab to change from graphical to report view every bit shown below. Here y'all should be able to find which ID Thread is eating upwardly your CPU resource by using the scroll bar to see all instances.

Below nosotros take identified which thread is causing the problem. Hither you tin see ID Thread 30 is taking 46.841% of the full CPU. You can besides find the ID Thread which is 872. At present that nosotros know the ID Thread, nosotros can run a query in SSMS to find the actual query.

Footstep four
Our final step is to correlate the Thread ID (KPID) identified in the last step to the SPID. To do this, run the following query in Query analyzer:

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=872

Step 5
From the query above we can run across SPID 71 is causing the event. To find how many threads and open up transactions this is running nosotros can run this query.

SELECT spid, kpid, status, cpu, memusage, open_tran, dbid FROM sysprocesses WHERE spid=71

Footstep 6
To get the exact query that is running, we can run DBCC INPUTBUFFER using the SPID. The below output shows usa that a backup job is causing our CPU issues on our server.

Side by side Steps

• Utilise this procedure to find the primary culprit in SQL Server which is eating up your CPU. Y'all can use this correlation in many performance related tasks and can rails many Perfmon counters to an individual thread and with that you can optimize your SQL Server.
• If yous have a lot of SQL Server processes, you can apply the line graph in Perfmon to observe the procedure that is using high CPU and hover over the line in the chart and it will show you the SQL Server thread. Then you tin can change to the Study view and roll over to that SQL Server process to encounter the details for the counters y'all selected.

Related Manufactures

Pop Manufactures

About the author

Manvendra Singh has over 5 years of feel with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips

Article Last Updated: 2011-08-09

groveners1996.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/

Share this post