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:
http://www.sql-server-performance.com/software/review/dba_dashboard_p1.aspx

— 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

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |