Find Queries Taking Most CPU (Processor)

If your box is CPU bound then this is the script you need. The first query will order the results based on the queries that have used the most CPU time since the SQL Server instance has been restarted (or the server has been rebooted). The second query orders the results based upon the average CPU time that each query takes.

[cc lang=”sql”]
— Find queries that take the most CPU overall
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads — The worst reads, disk reads
,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time DESC

— Find queries that have the highest average CPU usage
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads — The worst reads, disk reads
,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time/qs.execution_count DESC
[/cc]

3 comments
Richard Long 03 Nov 2011 at 4:15 pm

We got some records return FETCH API_CURSOR00000000002DC2F0 in TextData, no database name, object name.

May I know how to find the query behind the API_Cursor?

Thank you in advance.

Jarrah 01 Nov 2011 at 6:31 am

No need to run it on another db. Just run it on master, you can see what db in the query

JimGod 02 May 2011 at 9:13 am

When you run the query, tells me the following error. “Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ‘.’. ”
If I run it on the Master if it works, if I use another DB did not work. Why?

Featured Articles

 Site Author

  • Thanks for visiting!