Solved

Server has high processor queue - how to find out which process is using the cpu

Posted on 2014-03-31
3
4,044 Views
Last Modified: 2014-04-16
Hi,

i get every few hours a ticket of one Server. It sais, that the cpu has a very large Queue. Everytime i look on the machine, th cpu is used at 10% (approx.). How can i find out, which Programm takes all the cpu from time to time.
I am using gfi max to Monitor the Server (this generates those tickets).
Server is Server 2008 (SQL on it) fully patched.


Any idea ?

Thanks in advance.

loosain
0
Comment
Question by:loosain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 6

Accepted Solution

by:
Hassan Besher earned 250 total points
ID: 39968020
The CPU - Processor Queue Length alarm becomes active when the number of Windows threads waiting for CPU resources exceeds a threshold. Sustained high processor queue length is a good indicator that you have a CPU bottleneck.
Note that since this is a Windows-based alarm, it can include CPU that was consumed by non-SQL Server processes.
Tip: If you are running the Spotlight Diagnostic Server on the same machine you are monitoring, the Processor Queue Length shown here can be artificially high, especially on single processor machines. Since Spotlight on SQL Server is a multi-threaded application, the process of running its data collection queries causes extra Spotlight client threads to be scheduled for execution, resulting in a higher than normal Processor Queue Length figure and the raising of this alarm.

When this alarm is current, you should:
In Spotlight on Windows, look at the Processor Queue Length chart on the Windows Summary page of the Windows Activity drilldown. This will show you how long the Processor Queue Length has been high.
In Spotlight on Windows, look at the Processes page on the Processes drilldown to see which Windows process is consuming the CPU. SQL Server is shown as the process called sqlservr.
Look at the Summary page on the SQL Activity drilldown to determine how much of the CPU is being consumed by SQL Server.
If you determine from the steps above that SQL Server is consuming most of the CPU, look at the Sessions tab on the SQL Activity drilldown to see which SQL Server users are currently executing SQL, and to determine the exact SQL that is running. If necessary, use the Session Trace sub-tab on active sessions.
On the Sessions page, select the sessions that have the highest CPU figure to see the exact SQL statement that the user is currently running. If necessary, use the Session Trace sub-tab to see further information about the active sessions.
Consider upgrading to a faster CPU or adding processors to your server.
If SQL Server is consuming most of the CPU and your system supports many concurrent users, you might benefit from SQL Server's lightweight pooling option, which causes SQL Server sessions to be scheduled as fibers rather than threads. On some heavily-loaded systems, this can save a small amount of CPU. Ensure you test this properly, because on some systems it can actually increase SQL Server's CPU demands.
This option can be changed by using the Configuration drilldown. Changes to this option do not take effect until the SQL Server instance is stopped and restarted.
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 39970124
I suggest you install and use SQL own Performance Dashboard Reports that can take you to the exact query(ies) or missing index(es) that are causing high CPU utilizations.
We use it and trust it on ALL our SQL PROD servers since it came out on SQL 2005.

Performance Dashboard

http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx
http://blogs.msdn.com/b/arvindsh/archive/2010/06/25/performance-dashboard-reports-in-sql-server-2008.aspx
0
 

Author Closing Comment

by:loosain
ID: 40005245
Thanks, interesting and helpful
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question