Skip to main content
1 of 6
KIM
  • 111
  • 3

SQL Server with small Cache plan size

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

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

enter image description here

Is this small cache plan size normal?

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

Any advice would be appreciated.

KIM
  • 111
  • 3