SQL Server Performance

How to track sp problem.

Discussion in 'SQL Server 2005 General DBA Questions' started by jn4u, Dec 19, 2007.

  1. jn4u Member

    Yesterday I had problem withthat a sp that contained join that the developer had forgotten the where part. Thesp join very large tables and sp took more 95% dual itanium processor. How dofastest track this down when it accure. Find the sp or sql that running andconsume this much processor resources. I tried with a lot. Sp_who, sp_who2,activity monitor.
  2. anandchatterjee New Member

    Have you checked with profiler and execution plan? I think it may help you out!
  3. Greg Larsen New Member

  4. jn4u Member

    -- Possible to first check sys.dm_exec_sessions, over 50 to get only users?
    select * from sys.dm_exec_sessions where session_id > 50
    -- Cheking start_time, then status=runnig and cpu_time
    select * from sys.dm_exec_requests where session_id > 50
    -- Handel for sql
    select text from sys.dm_exec_sql_text(handel)
    -- Kill process.
    kill process_id

Share This Page