October 15, 2019 at 4:09 am
No much activity on the server and CPU usage is not dropping to normal CPU Usage. Would restarting the server would be the only option? However, one of the other dba tried to change the max memory settings thinking it will help without investigating into much and later reverted back to it's previous memory settings would that have any impact?
October 15, 2019 at 10:03 am
can you post the contents of an sp_who2 - we don't have much to go on
also is it sql that is using the CPU or another process - how are you measuring the CPU?
MVDBA
October 15, 2019 at 10:24 am
Please also share the screenshot of the Task Manager processes (Sort by CPU usage Descending).
October 15, 2019 at 6:23 pm
Download and install http://whoisactive.com/
It's far more useful than sp_who2. It will help you pinpoint the specific process that may be causing CPU spikes.
When you say "not much activity", do you realize that is not enough information to make any kind of recommendations?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 15, 2019 at 9:08 pm
If you aren't sure if the CPU usage is by SQL Server or something else, you can look in the ring buffer to see recent history:
DECLARE @ms_ticks bigint
SELECT @ms_ticks = ms_ticks
FROM sys.dm_os_sys_info
SELECT DATEADD(ms, ([timestamp] - @ms_ticks), GETDATE()) AS notification_time,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS CPUIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS CPUSQL,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'int') AS PageFaults,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'int') AS MemoryUsed
FROM (SELECT timestamp, convert(xml, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR') rb
WHERE [timestamp] > @ms_ticks - 3650000 --last hour
If the CPU is being consumed by SQL, then you can see what queries are using the most CPU like this:
SELECT TOP 25
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.total_worker_time / qs.execution_count / 1000000.0 AS avg_cpu_seconds,
qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
qs.total_logical_reads, qs.total_logical_writes,
qs.total_logical_reads / qs.execution_count AS average_logical_reads,
qs.total_logical_writes / qs.execution_count AS average_logical_writes,
qs.execution_count, qs.last_execution_time, qs.creation_time,
OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid) AS schema_name, OBJECT_NAME(qt.objectid, qt.dbid) AS object_name, o.modify_date,
qs.sql_handle, qs.plan_handle,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
ORDER BY total_cpu_seconds DESC;
October 17, 2019 at 5:30 pm
Thanks to everyone. So i found the problem and resolve the issue. However, i have another question and see what you'r thoughts on it? If ever there is a CPU usage is high my understanding is first identify the cause of CPU and if possible find the process causing the cpu usage high. However, someone has tried to increased the sql memory because of high cpu usage issue, even though the cache is not low. My understanding is that it seems like a bad idea to increase the SQL memory on the fly because your seeing high CPU Usage. Also, I would believe that that could cause more problems. Let me know what your thoughts on this? Thanks in advance!
October 17, 2019 at 6:27 pm
this is hugely complicated. But i would say 90% is not related to max memory.
MVDBA
October 17, 2019 at 6:30 pm
high cpu is most likely missing indexes, causing the cpu to sort. Or a rogue function.
it's not memory
MVDBA
October 17, 2019 at 6:33 pm
No much activity on the server and CPU usage is not dropping to normal CPU Usage. Would restarting the server would be the only option? However, one of the other dba tried to change the max memory settings thinking it will help without investigating into much and later reverted back to it's previous memory settings would that have any impact?
Look at Task Manager for starters. If it's not SQL Server taking most of the CPU, then you'll have to fix whatever it is that's consuming it. VS and anti-malware are two of the biggest consumers, especially on "mixed" boxes".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2019 at 5:12 pm
I agree with you, i have seen in the past adding indexes has resolved high CPU Usage. But never came across adding more memory to SQL Server would help high CPU Usage. I am trying to understand why would someone do this? Yes, task manager does show SQL Server is taking most of the CPU not anti-malware.
October 18, 2019 at 5:42 pm
high cpu is most likely missing indexes, causing the cpu to sort. Or a rogue function.
it's not memory
Broad statements such as this may cause more issues than they solve.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 18, 2019 at 6:33 pm
I agree with you, i have seen in the past adding indexes has resolved high CPU Usage. But never came across adding more memory to SQL Server would help high CPU Usage. I am trying to understand why would someone do this? Yes, task manager does show SQL Server is taking most of the CPU not anti-malware.
in one scenario, let's say that high CPU is being caused by slow reads or writes to the disk subsystem. Or, an overloaded disk subsystem. Adding memory increases the size of the cache, which in turn (should) reduces the amount of disk reads and writes. CPU then goes down.
Yes, adding indexes may help. Assuming it's an index that will be used, and it will not cause other issues such as with the write operations, Disk IO should be reduced, and CPU is then reduced.
From the beginning of time, the standard (horrible) advice to fix a slow SQL server was "add memory". That's not really a fix, except where the amount of RAM is simply too low, for a poorly written query, or bad indexes, or a mis-configured server.
The key is finding the root cause, and taking appropriate action. I'm betting the cause of your issues are all of the above. There's probably no magic bullet to fixing this.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 18, 2019 at 7:37 pm
Thanks! I agree with the scenario you mentioned, If CPU is being caused by slow reads or writes to the disk subsystem. Adding memory to server increases the size of the cache, which in turn reduces the amount of disk reads and writes. What if only the max memory was increased for SQL Server not on the box(because you would need restart of server depends on how much memory you adding), so that means when you increased the max memory for SQL Server only then there could be issue on OS side right and which can cause OS paging issues and that could decrease the size of SQL Server cache right? Let me know if my theory is not right?
October 21, 2019 at 2:38 pm
I could see an issue where if someone increases SQL Server's Max Server Memory too high, such that there is not enough free RAM left for the OS to run smoothly that it could cause OS level paging to the swap file. If RAM that is assigned to SQL Server gets sent to OS swap file, then your system is doing an extra write and extra read to disk for something that SQL Server thinks is "in memory". You do want to avoid excessive OS level paging to swap file. I don't think OS paging will decrease the size of SQL Server's cache though, SQL Server will not know the difference between virtual memory read from OS swap file or from actual RAM. You will see the difference though.
SQL Server will do better at managing it's own memory than the OS will. Here's an article that has some good guidelines for setting the Max Server Memory for SQL Server:
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
November 17, 2019 at 8:58 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply