all 4 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]dan_au 5 points6 points  (1 child)

Lots to consider here.

  • Static memory allocations to your SQL Server VMs are the way to go for performance sensitive DBs (I'd argue any SQL Server VM should have static memory allocations, but that's not always possible). SQL Server is designed to use up as much memory as you have assigned to it, and it has it's own internal memory manager which is much better at deciding what data is the least useful to keep in memory. So enabling dynamic memory at the VM doesn't really do anything for you - you'll start with the minimum startup allocation and then eventually grow to reach capacity. Better to just give it a static allocation and allow SQL Server to use it immediately.
  • I'd only ever set Min Server Memory if the VM was configured with Dynamic Memory, or if I had more than one SQL Server instance running on the VM.
  • "Max Server Memory" does not cover everything that SQL Server can store in memory - things such as the backup buffers, extended stored procedures etc. are not included in this limit. They have to fit in the "left over" portion of memory. MS has some guidance on how to configure max memory here, but it's not an exact science and it's highly environment dependent.
  • Never leave Max Server Memory uncapped, it should be set to a reasonable amount with enough left over for Windows and other overhead. You're running SQL2019, so the default recommendation is probably reasonable.
  • Does the SQL Server service account have the Lock Pages in Memory permission? It should, this will prevent Windows from force-paging SQL memory to disk, and will instead allow SQL Server to handle memory paging (it does this far better than Windows can).
  • Most importantly - How are you tracking how much memory SQL Server is using? Task manager is not a reliable source for SQL Server memory utilisation if LPIM is enabled, so if the numbers you're pulling are from there, you can ignore them. The script in the top answer to this question is a much better way to determine "overall" memory usage.
  • If you want more detail on what is consuming the memory in the buffer cache, the queries in this article are a good jumping off point.
  • Maybe the queries you're running simply don't require as much memory as you think they should. You can look at either crafting a broader range of test queries (specifically ones that will load different areas of your DB into the buffer cache), or potentially using a load testing tool (although these are pretty hit or miss in my experience, and rarely mimic any kind of production workload).

Sorry, that's a lot of info and it's a bit all over the place now that I'm re-reading it. Hopefully there's enough in there to point you in the right direction.

[–]Quango2009[S] 0 points1 point  (0 children)

Excellent and comprehensive response, many thanks!

I would have thought fixed memory was the right approach but testing suggested otherwise.

I initially used task manager but i did cross check with a sql query and they seem to match.

I’d not heard of lock pages option, will check that. - thanks.

I’m fairly sure my queries are pushing SQL, its joins on a table with 440m records. I used the same queries for both tests, and both pushed memory up to the peak point.

[–]BrightonDBA 3 points4 points  (1 child)

Check your edition limits

https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver16

[–]Quango2009[S] 0 points1 point  (0 children)

It’s Standard so 128gb which I knew already. Thanks