SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Who caused the CPU peg?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cryogeneric
Starting Member

6 Posts

Posted - 09/11/2013 :  18:48:57  Show Profile  Reply with Quote
Good day,
We had a major CPU peg this morning and I was able to use the below query to determine WHAT caused it. However, does anyone know if there is a way to determine WHO initiated the query?

Thanks!!

--CPU
SELECT top 100
QT.TEXT AS STATEMENT_TEXT,
QP.QUERY_PLAN,
QS.TOTAL_WORKER_TIME AS TOTAL_CPU_TIME,
QS.LAST_WORKER_TIME AS CPU_TIME_LAST_RUN,
QS.last_execution_time, qs.*
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP
WHERE QS.last_execution_time BETWEEN '9/11/13 13:35:00' and '9/11/13 16:30:00'
ORDER BY QS.LAST_WORKER_TIME DESC

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/11/2013 :  20:01:39  Show Profile  Reply with Quote
try this:
sys.dm_exec_sessions
Go to Top of Page

Cryogeneric
Starting Member

6 Posts

Posted - 09/12/2013 :  13:56:47  Show Profile  Reply with Quote
I'm aware of this table, but I'm not sure how to use it to determine who ran the query I see in sys.dm_exec_query_stats. Can you please give me a little more information on what you're suggesting I try? Thanks!

Edited by - Cryogeneric on 09/12/2013 13:59:04
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36641 Posts

Posted - 09/12/2013 :  14:13:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Cryogeneric

I'm aware of this table, but I'm not sure how to use it to determine who ran the query I see in sys.dm_exec_query_stats. Can you please give me a little more information on what you're suggesting I try? Thanks!



Join to sys.dm_exec_requests on sql_handle and then join sys.dm_exec_sessions on session_id.

But this will only help if the query is still running. Do you happen to have an sp_WhoIsActive job in place? If you don't, I'd highly recommend it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Cryogeneric
Starting Member

6 Posts

Posted - 09/12/2013 :  18:58:38  Show Profile  Reply with Quote
Just installed sp_WhoIsActive and have been playing with it. Great tool. Thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36641 Posts

Posted - 09/13/2013 :  10:10:09  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.61 seconds. Powered By: Snitz Forums 2000