How to track sp problem. | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
How to track sp problem.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.
Have you checked with profiler and execution plan? I think it may help you out!
I’ve written a SQL Server DBA Dashboard tool that is free that helps identify resources usage by SPs. You might give it a shot and the information you need. Here is an article on this site about the dashboard:
— 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.