Who caused the CPU peg? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Who caused the CPU peg?

Author

Topic

Cryogeneric
Starting Member

6 Posts

Posted - 2013-09-11 : 18:48:57

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

549 Posts

Posted - 2013-09-11 : 20:01:39

try this:
sys.dm_exec_sessions

Cryogeneric
Starting Member

6 Posts

Posted - 2013-09-12 : 13:56:47

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!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-12 : 14:13:21

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

Cryogeneric
Starting Member

6 Posts

Posted - 2013-09-12 : 18:58:38

Just installed sp_WhoIsActive and have been playing with it. Great tool. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-13 : 10:10:09

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources