CODEWITHSUNDEEP
sql-serversql-server-2005
gbro3n
gbro3n

Reputation: 6967

SQL Server 2005 Express - Activty Monitor - Show details is empty, how can I tell what the SQL statement that started the process is?

Trying to fix a problem on our server - a very expensive query is using a lot of our CPU.

When I use activity monitor, sometimes you can use the show details for the process, and it will show you the sql that is running the query. Not always though.

I can see the process now, but how can I tell what query text is responsible? I can identify the source if I have the sql, but at the moment I have nothing!

Upvotes: 1

Views: 1147

Answers (1)

John Sansom
John Sansom

Reputation: 41879

Try using the following query.

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
   st.text,
   qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

Then may I suggest that you get yourself a copy of the awesome sp_WhoIsActive stored procedure written and shared for free courtesy of Adam Machanic. You can use the procedure to identify all currently running queries, including a wealth of associated information such as the amount of CPU used, IO, the execution plan, query text, blocking etc.

Upvotes: 2

Related Questions