SQL takes more memory and CPU usage

Boopathi Subramaniam 3,196 Reputation points
2021-09-17T18:48:34.463+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,601 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
12,049 questions
Microsoft Configuration Manager

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-09-17T21:50:02.83+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. Colin Ford 1,026 Reputation points
    2021-09-19T20:39:40.743+00:00

    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://learn.microsoft.com/en-us/mem/configmgr/core/plan-design/configs/site-size-performance-guidelines

    1 person found this answer helpful.
    0 comments
  3. Amandayou-MSFT 11,041 Reputation points
    2021-09-22T09:30:30.78+00:00

    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

    0 comments
  4. Boopathi Subramaniam 3,196 Reputation points
    2021-09-22T10:58:15.753+00:00

    Hello @Amandayou-MSFT , @Colin Ford , @Erland Sommarskog

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

    WSUS is in WID and not in SQL.