sp_who2 only CPU increases no change in I/O

Question

sp_who2 only CPU increases no change in I/O

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
May 27, 2011 at 9:52 pm

#251816

i occasionally see a long running job. I have noticed that after certain time only CPU usage increases an I/O is constant from sp_who2. Any idea what could be wrong?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

Sounds like cache is doing it's job; no need to hit the disk. I wouldn't see this as much of a problem. Perhaps TSQL could be tuned to use less CPU, but no IO is a good (and rare) thing.

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

here is the thing.Occasionally a select inside this sproc is hung.Now today i found the sql where it is hung using trace , when i run that sql manually from management studio results come back very quickly but occasionally it is hung which is run through a job/ssis like today? This behaviour is so weird.And whenever it is hung i see that CPU increases and IO doesn't increase at all. What does this mean to? Keep in mind the same query when i run from management studio runs in few mins at the same time.

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

Its good there is no any IO

My Blog
www.aureus-salah.com

LutzM SSC Guru Points: 107049 More actions · Answer 4

Syed Jahanzaib Bin hassan (5/28/2011)
Its good there is no any IO

What new information does this post provide?

To quote Jim:

... but no IO is a good (and rare) thing. ...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 5

So don't use sp_who2. That's the old approach anyway. Dive into the DMOs and get lots of good information.sys.dm_exec_requests will show you what's currently running. You can combine that with sys.dm_exec_sql_tex and sys.dm_exec_query_plan to pull the query and plan right out of the cache for currently executing procedures. You'll get good info.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 6

SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

WHERE STATUS !='SLEEPING'

ORDER BY CPU DESC

Use this query for CPU and IOs

My Blog
www.aureus-salah.com

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Get a copy of Adam Machanic's free sp_whoisactive (v11.00 is current I think). READ THE DOCUMENTATION at the top of the sproc code!! AWESOME STUFF!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service