Uncovering High-CPU Queries: A Guide for Database Optimization

In the world of database management, identifying queries responsible for high CPU usage is paramount for maintaining optimal performance. This article will guide you through two essential SQL statements that can help you pinpoint these resource-intensive culprits, both in real-time and historically.

Identifying Real-Time High-CPU Queries

To get immediate insights into the queries driving CPU activity, execute the following SQL statement:

/*https://www.dbascrolls.com */

    SELECT TOP 10
    s.session_id, DB_NAME(s.database_id) as [database], r.blocking_session_id,
    r.status,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.total_elapsed_time / (1000 * 60) 'Elaps M',
    SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
    COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
    r.command,
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_end_time,
    s.login_time,
    r.open_transaction_count
FROM sys.dm_exec_sessions AS s
    JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

This statement provides crucial information about the top sessions, including session ID, status, CPU time, logical reads, writes, and more. It also reveals the actual text of the query, making it easier to analyze and take necessary actions.

Analyzing Historical CPU-Bound Queries

In case the CPU is not currently under heavy load, you can still gain valuable insights by examining historical data. Execute the following SQL statement:

/*https://www.dbascrolls.com */

/*https://www.dbascrolls.com */
SELECT TOP 10 st.TEXT AS batch_text
	,SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (
			(
				CASE qs.statement_end_offset
					WHEN - 1
						THEN DATALENGTH(st.TEXT)
					ELSE qs.statement_end_offset
					END - qs.statement_start_offset
				) / 2
			) + 1) AS statement_text
	,(qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms
	,(qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms
	,qs.total_logical_reads / qs.execution_count AS avg_logical_reads
	,(qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms
	,(qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY (qs.total_worker_time / qs.execution_count) DESC

This statement delves into historical data, presenting details about batch text, statement text, average CPU time, average elapsed time, and more. Effectively managing CPU usage in your database is pivotal for maintaining optimal performance. By utilizing these SQL statements, you can identify and address resource-intensive queries, ensuring your database runs smoothly even under heavy workloads. Remember, a well-optimized database is the cornerstone of efficient application performance.