Return to Question

I'm running on SSQL Server 2019 standard edition and it has 48GB of physical memory and 44GB of SQL Server's max memory.

There is nothing on the host but SqlServer.

All of request from application servers are just calling stored procedures and there are thousands of procedures.

The problem is that DB CPU usage hits 80% above very often.

Whenever DB CPU reaches 50% above, stored Procedure recompiling is soaring up and dm_exec_cached_plans view table is refreshed every time I scan it.

SELECT sum(CAST (size_in_bytes as decimal (14,2))) / 1048576 AS [Cache plan size MB] 
  FROM sys.dm_exec_cached_plans

And sys.dm_os_memory_clerks are showing like below...

Is this small cache plan size normal?

If this is not, how can I handle this situation?

Any advice would be appreciated.

I'm running MSSQL Server 2019 standard edition and it has 48GB of physical memory and 44GB of SQL Server's max memory.

There is nothing on the host but SqlServer.

All of request from application servers are just calling stored procedures and there are thousands of procedures.

The problem is that DB CPU usage hits 80% above very often.

Whenever DB CPU reaches 50% above, stored Procedure recompiling is soaring up and dm_exec_cached_plans view table is refreshed every time I scan it.

SELECT sum(CAST (size_in_bytes as decimal (14,2))) / 1048576 AS [Cache plan size MB] 
  FROM sys.dm_exec_cached_plans

And sys.dm_os_memory_clerks are showing like below...

Is this small cache plan size normal?

If this is not, how can I handle this situation?

Any advice would be appreciated.

I'm running on SSQL Server 2019 standard edition and it has 46GB of physical memory and 44GB of SQL Server's max memory.

There is nothing on the host but SqlServer.

All of request from application servers are just calling stored procedures and there are thousands of procedures.

The problem is that DB CPU usage hits 80% above very often.

Whenever DB CPU reaches 50% above, stored Procedure recompiling is soaring up and dm_exec_cached_plans view table is refreshed every time I scan it.

SELECT sum(CAST (size_in_bytes as decimal (14,2))) / 1048576 AS [Cache plan size MB] 
  FROM sys.dm_exec_cached_plans

And sys.dm_os_memory_clerks are showing like below...

Is this small cache plan size normal?

If this is not, how can I handle this situation?

Any advice would be appreciated.

I'm running on SSQL Server 2019 standard edition and it has 48GB of physical memory and 44GB of SQL Server's max memory.

There is nothing on the host but SqlServer.

All of request from application servers are just calling stored procedures and there are thousands of procedures.

The problem is that DB CPU usage hits 80% above very often.

Whenever DB CPU reaches 50% above, stored Procedure recompiling is soaring up and dm_exec_cached_plans view table is refreshed every time I scan it.

SELECT sum(CAST (size_in_bytes as decimal (14,2))) / 1048576 AS [Cache plan size MB] 
  FROM sys.dm_exec_cached_plans

And sys.dm_os_memory_clerks are showing like below...

Is this small cache plan size normal?

If this is not, how can I handle this situation?

Any advice would be appreciated.

I'm running on SSQL Server 2019 standard edition and it has 46GB of physical memory and 44GB of SQL Server's max memory.

There is nothing on the host but SqlServer.

All of request from application servers are just calling stored procedures and there are thousands of procedures.

The problem is that DB CPU usage hits 80% above very often.

Whenever DB CPU reaches 50% above, stored Procedure recompiling is soaring up and dm_exec_cached_plans view table is refreshed every time I scan it.

SELECT sum(CAST (size_in_bytes as decimal (14,2))) / 1048576 AS [Cache plan size MB] 
  FROM sys.dm_exec_cached_plans

And sys.dm_os_memory_clerks are showing like below...

Is this small cache plan size normal?

If this is not, how can I handle this situation?

Any advice would be appreciated.

I'm running on SSQL Server 2019 standard edition and it has 46GB of physical memory and 44GB of SQL Server's max memory.

There is nothing on the host but SqlServer.

All of request from application servers are just calling stored procedures and there are thousands of procedures.

The problem is that DB CPU usage hits 80% above very often.

Whenever DB CPU reaches 50% above, stored Procedure recompiling is soaring up and dm_exec_cached_plans view table is refreshed every time I scan it.

SELECT sum(CAST (size_in_bytes as decimal (14,2))) / 1048576 AS [Cache plan size MB] 
  FROM sys.dm_exec_cached_plans

And sys.dm_os_memory_clerks are showing like below...

Is this small cache plan size normal?

If this is not, how can I handle this situation?

Any advice would be appreciated.