Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

very high CPU, 0 I/O, no blocking, NULL in wait type? What could be possibly going wrong? Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 10:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 6:05 PM
Points: 1,283, Visits: 2,960

I have a select query which is in a "hung" state. I checked there is no blocking. From the activity monitor i see that Wait type is NULL. I do see very very high CPU and 0 on I/O. What could be wrong? Any advice?
Post #1379426
Posted Wednesday, October 31, 2012 11:02 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:21 AM
Points: 33, Visits: 325
Has this query been regularly as OK? or is it a new query?
Are the statistics up to date?
Have you checked sys.dm_exec_requests for more detailed information?


Jack Vamvas
sqlserver-dba.com
Post #1379449
Posted Thursday, November 1, 2012 1:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
can you please post the Query.

is it linked server related query ?


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1379653
Posted Friday, November 2, 2012 6:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:34 PM
Points: 1,276, Visits: 2,803
Find out which SPID it is using and do a SP_WHO2 xxx where xxx is the SPID of the running query. Run this a few times to see if the CPU and I/O are increasing.

Also, do an explain plan on it to see if it is using indexes or table scans. Then see if you can add tuning indexes to make it run more efficiently.

Also, change the SELECT statement to SELECT COUNT(1) to see if it will, somewhat quickly tell you how many rows it should return. Does it return 1 row, 500 rows, 20 million....

Other questions....

Has this query run before... on a regular basis... did it run quickly before and all of a sudden gone crazy.

How big is the table(s), are stats up-to-date... could reindexing the tables be an option?




Post #1380317
Posted Friday, November 2, 2012 10:58 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 736, Visits: 3,742
sqldba_newbie (10/31/2012)

I have a select query which is in a "hung" state. I checked there is no blocking. From the activity monitor i see that Wait type is NULL. I do see very very high CPU and 0 on I/O. What could be wrong? Any advice?


you can do .. select * from sys.dm_os_waiting_Tasks and join with sys.dm_exec_requests and sys.dm_exec_sessions to see whats going on ..

Also, Adam Mechanic's sp_whoIsActive is another good stuff that will tell u whats going on currently.

without giving more info about your environment and what steps u have taken to troubleshoot, its very difficult to help you.


______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor
Post #1380512
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse