May 21, 2022 at 5:16 am
Hi All,
Now a days we are receiving high CPU alerts from our monitoring tool. In general does seeing High CPU on SQL Server box , call for any action from 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
May 22, 2022 at 4:36 am
A single CPU going to 90-95% is expected. Are you saying that the total of ALL CPU's is what's going to 90-95% or just one now and then?
If so, check the connection for the software you've built or bought... we had a similar issue and it turned out that the connections that were supposed to default to having M.A.R.S. (Multiple Active Result Sets) in the .net connects were all defaulting to ON for M.A.R.S. Explicitly stating the M.A.R.S. is disabled fixed our problem. Disclaimer. That was 5 or 6 years ago... I don't know if they made any changes to .net to correctly disable M.A.R.S. as a default since then.
And, yeah... it could also be a shedload of performance challenged code and, yeah, I'd be concerned there. Of course, if you have something like 100 connections and your box only has 4 core, well... you know the real problem then. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2022 at 12:11 pm
I would check for any correlation between excessive IO and high cpu. Insufficient paging file space will cause sql server excessive page swapping and therefore high IO. Second is there enough memory, memory pressure will force sql server to discard and/or swap pages causing high IO.
Far away is close at hand in the images of elsewhere.
Anon.
May 22, 2022 at 12:21 pm
How to identify if it is M.A.R.S and disable it?
May 22, 2022 at 12:24 pm
Hi David,
You mean to say, I/O and CPU goes hand in hand?
May 22, 2022 at 12:44 pm
As a hard and fast rule no. But high cpu can sometimes be caused by high io and that can definitely be a symptom of poor queries as Jeff indicated. I have known Jeff for many years and he is an expert on the effects of poor performing queries. I only posted something that sometimes can be overlooked. End user performance can be a very subjective term, how would they know without some benchmark. Your issues could be that increases in transactions, data or databases means you are reaching the point of insufficient resources for your needs. If cpu is spiking then it could be what is running at that time. However if cpu is high constantly then it could be an increase in workload or one or more frequent queries now performing badly. As Jeff mentioned fix any poorly performing queries. If cpu is still constantly high then you might need more cores.
Far away is close at hand in the images of elsewhere.
Anon.
May 22, 2022 at 5:17 pm
How to identify if it is M.A.R.S and disable it?
There may be another way but we had our developers check the actual connection strings they were using. It was plain as day there and specifically said to enable "Multiple Active Result Sets". I'm not the one that did the checking and, after more than 2 decades of not being a front-end developer, couldn't even tell you where to look specifically for connection strings but our Developers had them in files (IIRC) on the Web Servers.
I also asked some folks in our old PASS chapter to check their stuff. They confirmed that they had the same issue (it defaulted to ON rather than OFF) and were experiencing similar issues that went away when they explicitly disabled M.A.R.S. in their connection strings.
Like I said before, it can also be caused by performance challenged code and simply having too few CPUs for the number of connections being serviced.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 24, 2022 at 7:02 am
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]([qs].[sql_handle]) 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]([qs].[sql_handle]) 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]([qs].[sql_handle]) 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 ?
June 7, 2022 at 4:12 pm
You can try to check the Query Store for "regressed queries". These are ones that have chosen less optimal plans. There could be a few procedures this is afflicting but that account for most of your calls to the database.
----------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply