Hello All,
Just need a bit of guidnance. I am currently with an organization who are upgrading to SQL 2022 on 3 servers with 6 instances however the supplier setting this up is creating 6 availability groups which I kept questioning as I believe that is unneccessary and was informed having multiple AGs helps with memory sharing between the instances which still doesn't make sense.
Could I be missing something?
Whoever made that statement about AGs and memory sharing between instances is absolutely wrong. Availability Groups will not improve memory utilization, ever. Multiple instances on the same server will not improve memory utilization, ever. SQL Server instances never share memory, they actually compete for the physical server memory unless they are each set to use a maximum megabyte value.
The total maximum amount for all instances should be well under the total server memory, less than 75% is a reasonable margin, unless your server has 256 GB or more of RAM.
Regarding memory usage by Availability Groups/HADR:
If you see a non-zero value for the MEMORYCLERK_HADR type, that means that amount of memory is used strictly for managing availability group functions, and therefore is unavailable for other features, like the buffer pool for data, procedure cache, etc. The MEMORYCLERK_SQLLOGPOOL will likely increase if Availability Groups are used.
If you have instances with and without AGs running, it would be useful to query them both and see the values you get for each memory clerk type, for comparison. It's easy to copy the results into a spreadsheet and do a pivot table or other side-by-side. AGs may affect other clerks than the two I already mentioned.
If this person or persons continue to insist that memory will be improved, ask them to provide their documentation, immediately, and preferably before you share this link or the others I've provided. I suggest you read them both beforehand, and get a good understanding of the terminology, so that you can answer the supplier authoritatively.
If the Availability Groups will be using Windows Failover Clustering, that will require additional system memory taken away from SQL Server. It may not be much, but it's still something.