4

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

  • 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
4
  • 8
    16GB seems quite low for a prod server hosting 1K+ databases. My laptop has more memory.
    – Dan Guzman
    Nov 6, 2023 at 13:07
  • Agreed with Dan. I've ran into Memory pressure issues in the past on instances with 16 GB of Memory split across 300-500 databases. Doubling the Memory to 32 GB resolved most of the issues. I can't imagine against 1,400 databases (unless most of them are offline or not in use). You may need 64 GB or more, but this is just a complete guess in the dark based on anecdotal evidence. It really depends on the workload and you'd have to incrementally add Memory to find the sweet spot.
    – J.D.
    Nov 6, 2023 at 13:37
  • 1
    SQL Server is a memory hog. You'd need 32-64 gb minimum, depending on the databases it's handling. I've run into memory problems with 1 database with only 16gb RAM.
    – Monty Wild
    Nov 7, 2023 at 1:16
  • Set a minimum memory setting too. i ran into an issue with carbon black hogging os memory and my 32 GB server was running mssql with just 2 GB. that's changed.
    – smoore4
    Apr 11 at 16:07

1 Answer 1

7

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.

2
  • Aren't those extra system threads shared between databases? Nov 6, 2023 at 23:17
  • Some are, some aren't, depends on features used and settings. Nov 7, 2023 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.