High cpu on sql server box 85-90% utilization

Question

Hi,

Now a days we are receiving high CPU alerts from our monitoring tool. Does seeing High CPU on the box , do we need to do anything/check on the sql server side.

So far, no user complained about slowness of anything of such sort. Any action needs to be taken when we are seeing such alerts?

Thanks,
Bob

Answer 1

Hi @Bob sql ,

You can follow the below steps to isolate the problem and mitigate it.

Please refer to below MS document and blog to get detail troubleshooting methods. Such as performance monitor, T-SQL script to get the top queries consuming High CPU, etc.

Troubleshoot high-CPU-usage issues in SQL Server
How to troubleshoot high CPU utilization issue in SQL Server?

---

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

Answer 2

So far, no user complained about slowness of anything of such sort. Any action needs to be taken when we are seeing such alerts?

If all queries are well-tuned, supporting indexes exist, execution plans optimal, and users are happy with existing response time, then CPU utilization reflects percent compute capacity. If the workload increases significantly, response time will degrade and your phone will start ringing.

A proactive DBA would examine execution plans for efficiency for the most expensive and frequently executed queries for query and index tuning, even if users are not yet complaining.

Answer 3

Cathyji-msft,

If available please post a link / reference for a Windows PC (Windows 11 or Windows 10).

In case there is no available link how could the commands in the above link be modified for Windows 11 or Windows 10?

Answer 4

1 way of fetching high cpu queries
-- top 10 queries by worker time

SELECT TOP 10
[qs].[last_worker_time],
[qs].[max_worker_time],
[qs].[total_worker_time],
[qs].[execution_count],
stmt_start = [qs].[statement_start_offset],
stmt_end = [qs].[statement_end_offset],
[qt].[dbid],
[qt].[objectid],
SUBSTRING([qt].[text], [qs].[statement_start_offset] / 2,
(CASE WHEN [qs].[statement_end_offset] = -1
THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text])) * 2
ELSE [qs].[statement_end_offset]
END - [qs].[statement_start_offset]) / 2) AS statement
FROM [sys].[dm_exec_query_stats] qs
CROSS APPLY [sys].dm_exec_sql_text AS qt
ORDER BY [qs].[total_worker_time] DESC; --- i.e. cpu time in microsecs

Alternate way,

--- I am grouping the queries by query hash and based on that i am getting total worker time
SELECT [qs].[last_worker_time],
[qs].[max_worker_time],
[qs].[total_worker_time],
[qs].[execution_count],
stmt_start = [qs].[statement_start_offset],
stmt_end = [qs].[statement_end_offset],
[qt].[dbid],
[qt].[objectid],
SUBSTRING([qt].[text], [qs].[statement_start_offset] / 2,
(CASE WHEN [qs].[statement_end_offset] = -1
THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text])) * 2
ELSE [qs].[statement_end_offset]
END - [qs].[statement_start_offset]) / 2) AS statement
FROM [sys].[dm_exec_query_stats] qs
CROSS APPLY [sys].dm_exec_sql_text AS qt
ORDER BY [qs].[total_worker_time] DESC;

-- Plug in query hash
SELECT SUBSTRING([qt].[text], [qs].[statement_start_offset] / 2,
(CASE WHEN [qs].[statement_end_offset] = -1
THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text])) * 2
ELSE [qs].[statement_end_offset]
END - [qs].[statement_start_offset]) / 2) AS statement,
[qs].[total_worker_time],
[qs].[execution_count],
[qs].[query_hash],
[qs].[query_plan_hash]
FROM [sys].[dm_exec_query_stats] qs
CROSS APPLY [sys].dm_exec_sql_text AS qt
WHERE [qs].[query_hash] = 0x88B8B513764CB9F4C;

Question here, how can I know in which stored procedure these sql stmts are being used and what parameter values being used at runtime ?