patternsqlMinor
SQL Server memory requirements
Viewed 0 times
sqlrequirementsmemoryserver
Problem
At this moment I run into some database performance problems for a SaaS application. During the day the RESOURCE_SEMAPHORE wait stats shoot up to 30 to 60 seconds for 1 or 2 minutes. During that time I also receive one or more Severity 17 alert mails from our server with the warning "There is insufficient system memory in resource pool 'internal' to run this query."
We've already resolved the most inefficient queries that had large memory grants (1,5 to 2,5 GB grants with only 5% or less usage). To pinpoint these queries we used Brent Ozar's sp_BlitzCache. Unfortunately the performance issues still occur after these changes.
Mind you at this moment we have a agent job that runs DBCC FREEPROCCACHE every 5 minutes. Doing this makes the problem more sporadic. Change this job to run every half hour seems to make the problems worse. Of course running this job has other implications like higher compilations/sec and a higher CPU utilization but at this moment is a "best of both worlds" kinda solution.
I'm afraid the memory pressure issues are a result of the server configured with not enough RAM memory? Is this assumption correct or are these problems created by something else?
Server stats
We've already resolved the most inefficient queries that had large memory grants (1,5 to 2,5 GB grants with only 5% or less usage). To pinpoint these queries we used Brent Ozar's sp_BlitzCache. Unfortunately the performance issues still occur after these changes.
Mind you at this moment we have a agent job that runs DBCC FREEPROCCACHE every 5 minutes. Doing this makes the problem more sporadic. Change this job to run every half hour seems to make the problems worse. Of course running this job has other implications like higher compilations/sec and a higher CPU utilization but at this moment is a "best of both worlds" kinda solution.
I'm afraid the memory pressure issues are a result of the server configured with not enough RAM memory? Is this assumption correct or are these problems created by something else?
Server stats
- SQL Server 2022 (16.0.4085.2)
- 6 logical processors (max DOP = 4)
- 16 GB total RAM, configured as 14 GB max server memory for SQL Server and 2 GB for OS
- A total of 1381 databases
- Total database size: 302GB
Solution
I'm afraid the memory pressure issues are a result of the server configured with not enough RAM memory? Is this assumption correct or are these problems created by something else?
That's most likely the current main issue you're running into, but with 6 cores and 1381 databases you're also running into CPU issues it's just that the memory issues are more prominent in the environment.
Just saying this out loud might help make sense. Let's say each database that is online requires some basic level of setup from SQL Server, let's say that takes 4 MB of memory (this isn't the actual value, it'll change per various settings and versions). There are 1381 databases, so just to bring the databases online and do nothing you're going to eat up 5.5 GB of memory for control and allocation structures. This doesn't count the cpu needs - for example a recovery thread, GC threads, CLR threads, log writer threads, IOCP, etc., all on 6 cpus. This leaves very minimal amounts of memory or cpu for the workload, after taking into account Windows and any other services loaded onto the server (anti* whatever crap, 50 different agents, etc.).
There is essentially 230 databases per cpu and 11 MB per database. That, to me, is woefully oversubscribed as a server. How much you'll need is unknown, but with 1381 databases (unless it's a hosting situation where 99% of them are idle all day almost every day) I'd be looking at double digit cpu and triple digit memory as a starting point.
That's most likely the current main issue you're running into, but with 6 cores and 1381 databases you're also running into CPU issues it's just that the memory issues are more prominent in the environment.
Just saying this out loud might help make sense. Let's say each database that is online requires some basic level of setup from SQL Server, let's say that takes 4 MB of memory (this isn't the actual value, it'll change per various settings and versions). There are 1381 databases, so just to bring the databases online and do nothing you're going to eat up 5.5 GB of memory for control and allocation structures. This doesn't count the cpu needs - for example a recovery thread, GC threads, CLR threads, log writer threads, IOCP, etc., all on 6 cpus. This leaves very minimal amounts of memory or cpu for the workload, after taking into account Windows and any other services loaded onto the server (anti* whatever crap, 50 different agents, etc.).
There is essentially 230 databases per cpu and 11 MB per database. That, to me, is woefully oversubscribed as a server. How much you'll need is unknown, but with 1381 databases (unless it's a hosting situation where 99% of them are idle all day almost every day) I'd be looking at double digit cpu and triple digit memory as a starting point.
Context
StackExchange Database Administrators Q#332816, answer score: 7
Revisions (0)
No revisions yet.