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..

• Brandie Tarvin

  SSC Guru

  Points: 173080

  More actions

  July 29, 2010 at 10:47 am

  #1199933

  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

  July 29, 2010 at 11:07 am

  #1199947

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

• Brandie Tarvin

  SSC Guru

  Points: 173080

  More actions

  July 30, 2010 at 6:02 am

  #1200233

  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

  July 30, 2010 at 7:08 am

  #1200287

  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]