Sql Server CPU Control

HKN MZ
1 min readDec 27, 2023

This SQL query retrieves information about the top 10 currently executing sessions along with details about their associated queries. It uses the sys.dm_exec_sessions and sys.dm_exec_requests dynamic management views to gather information about sessions and their corresponding requests.

SELECT TOP 10 s.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 query is useful for identifying and analyzing the top-consuming sessions in terms of CPU time, logical reads, and other metrics. It provides insights into the current activity on the SQL Server instance.

--

--

HKN MZ
HKN MZ

Written by HKN MZ

I am writing about Sql Server, Elasticsearch and Python. İ am an Database Administrator on SQL Server and Elasticsearch more than 5+ years.

No responses yet