question

BoopathiSubramaniam-6294 avatar image
0 Votes"
BoopathiSubramaniam-6294 asked ErlandSommarskog commented

SQL takes more memory and CPU usage

Hello,
Below are the configuration of the server
Processor : Intel(R) Xeon(R) CPU E7-4870 v2 @ 2.30GHz, 2294 Mhz, 1 Core(s), 1 Logical Processor(s)
System Model : VMware Virtual Platform
OS Name : Microsoft Windows Server 2012 Standard
Installed Physical Memory (RAM): 24.0 GB

SQL Server : Microsoft SQL Server Standard (64-bit)
Installed Location : D:\Program Files\Microsoft SQL Server\
Processor : 8
Memory : 24575 (MB)
Minimum Server memory in MB : 8192
Maximum server memory in MBB : 12044

SCCM version 2010 uses the SQL Database. CPU and Memory usage becomes high and alert generated.
CPU Load: 100 %
%Memory Used: 77 %

This is started at 6:57AM and became normal at 9:19AM. This happens at random time interval.
Please help me to troubleshoot and fix the issue

sql-server-generalwindows-servermem-cm-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello BoopathiSubramaniam,


By default, a SQL Server instance may over time consume most of the available Windows operating system memory in the server. Once the memory is acquired, it will not be released unless memory pressure is detected.

This is by design and does not indicate a memory leak in the SQL Server process. Use the max server memory option to limit the amount of memory that SQL Server is allowed to acquire for most of its uses.



For more information, see the Memory Management Architecture Guide.

https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver15#changes-to-memory-management-starting-with-


For more information, do follow the below link

https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver15#changes-to-memory-management-starting-with-

Hope this answers all your queries, if not please do repost back.
If an Answer is helpful, please click "Accept Answer" and upvote it : )

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

Why would it be a problem that SQL Server uses 77% of the memory? SQL Server is designed to grab as much memory as it can for its buffer cache, because it is always better to read data from cache than from disk.

Are you running other applications on the machine as well? 24 GB of RAM is quite modest in these days.

As for the CPU, I guess that there are some slow queries. If you are on SQL 2016 or later, they can easily be tracked with Query Store. Then again, if this is for SCCM, I am not sure how you can do to improve queries that come from the application.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ColinFord-6663 avatar image
1 Vote"
ColinFord-6663 answered

As mentioned already, it is not unusual for your site server to use most resources available. But one of the keys to sizing is how many clients you are going to manage, which you don't mention. An old but good article on sizing and optimization especially for SQL is this one by Steve Thompson. https://stevethompsonmvp.wordpress.com/2017/06/21/properly-configure-sql-server-configmgr-database/

Microsoft also published their recommendations, which go quite in depth https://docs.microsoft.com/en-us/mem/configmgr/core/plan-design/configs/site-size-performance-guidelines

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Amandayou-MSFT avatar image
0 Votes"
Amandayou-MSFT answered

Hi,

Haven't heard from you for some time, is ErlandSommarskog and ColinFord-6663's answers helpful to you? If it is helpful, please accept answer. It will make someone who has the similar issue easily find the answer.

If you have any other issues, please don't hesitate to let us know.

Thanks and have a nice day.

Best regards,
Amanda

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BoopathiSubramaniam-6294 avatar image
0 Votes"
BoopathiSubramaniam-6294 answered ErlandSommarskog commented

Hello @Amandayou-MSFT , @ColinFord-6663 , @ErlandSommarskog

This happens when the WSUS completes and "sync: SMS performing cleanup" started.

WSUS is in WID and not in SQL.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Now, that is an essential piece of information. Why did you not include this in your initial post?

Not that I can really assist, because I am not familiar with WSUS myself, but including this type of information is essential to get help from people who knows WSUS and what can be done with it.

0 Votes 0 ·