SQL 2017 High CPU utilization MS technote error.

Question

Hello folks!

SQL High CPU troubleshoot: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues having Step 2: Identify queries contributing to CPU usage in which SQL Query # 2 which helps in "To identify the queries that are responsible for high-CPU activity currently, run the following statement:"

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

the SQL query does not run and gives error for

sys.dm_exec_requests

not found.

Need assistance. thanks in advance.

SQL Version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Answer 1

the SQL query does not run and gives error for

I can't confirm, the query works; somehow you are doing something wrong.

Answer 2

Hi @Ritesh Desai

Try cumulative update to latest version: SQL Server 2017 CU 31 (Latest).

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi Ritesh,

Welcome to Q&A Forum, this is a great place to get support, answers and tips.

Thank you for posting your query, I'll be more than glad to help you out.

I tried to find your problem... using a SQL Server 2017 RTM Developer (Developer = Enterprise) and execute the same query... but I have the same result as Olaf...

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) 
	Aug 22 2017 17:04:49 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)

(1 row affected)

session_id status                         cpu_time    logical_reads        reads                writes               Elaps M     statement_text                                                                                                                                                                                                                                                   command_text                                                                                                                                                                                                                                                     command                          login_name                                                                                                                       host_name                                                                                                                        program_name                                                                                                                     last_request_end_time   login_time              open_transaction_count
---------- ------------------------------ ----------- -------------------- -------------------- -------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ----------------------

(0 rows affected)


Completion time: 2023-03-07T12:14:57.1736175+01:00

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!