Sysprocesses Question

Question

Sysprocesses Question

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
July 29, 2010 at 9:39 am

#221826

I am running the below query to find the cause of CPU spike. I am running this query when there is CPU spike for couple mins.
Mentioned below are couple questions.
select spid,uid,login_time,last_batch,stmt_start,stmt_end,cpu,hostname,program_name,loginame,status,sql_handle from sysprocesses where status !='sleeping' and spid>51 order by CPU desc
i) Does login_time means the time when query was started to execute? ---
ii) What does stmt_start and stmt_end means?Can we actually convert into time like in secs
please answer accordingly..thanks in advance..

Viewing 5 posts - 1 through 4 (of 4 total)

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

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 1

stmt_start and stmt_end means when the query started running and when it stopped running.

Look up Profiler in BOL. All your column names are similar enough events & columns in Profiler that you can probably get more details on what you're looking at by reading up on the Profiler GUI descriptions. If these fields are what I think they are, they probably can be converted but I don't know the code for that off the top of my head.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

in that case stmt_start shows 440 and stmt_end shows -1, what does this mean?

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 3

Look up 'sysprocess system table' in Books Online.

BOL
stmt_start
Starting offset of the current SQL statement for the specified sql_handle.
stmt_end
Ending offset of the current SQL statement for the specified sql_handle.
-1 = Current statement runs to the end of the results returned by the fn_get_sql function for the specified sql_handle.

I'm not quite sure what "offset" means. I have to do more research into it, but there's your -1 explanation.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 4

Really you should be using the new DMV's rather than the old system style tables.

One that you might be interested in is: Sys.DM_Exec_Query_Stats

you can link this dmv to sys.dm_exec_sql_text and sys.dm_exec_query_plan and it will show you the text of the query as well as the query plan used.

It's much more powerful - and you get to learn something new.

win - win

Rich

Hope this helps,
Rich

[p]
[/p]