We are trying to understand below behavior happening in SQL Server and need some guidance:-
After migrating from older version SQL2012 (running on bare metal P Server) to newer SQL2017 running inside VM with matching Hardware specs we are seeing this interesting behavior:
On SQL2012 Phy server:– For server with 700 GB RAM; MAX memory setting of 500, after any flush of cache/restart activity on sql instance , approx. in 20-30 mins we see SQL server quickly able to reach that MAX memory setting and can be seen using 500 GB instantly
On SQL2017 server (VM): -For server with 700 GB RAM; MAX memory setting of 500, after any flush of cache/restart activity on SQL instance , it is about 4-5 hours and we still see almost 50% of that max memory usage.
Memory reservation on VM is set to 750 GB
Yes we see little slowness in app response and our top 2 waits are generally PAGEIOLATCH and WRITELOG
Please let me know what additional info i can provide to help me understand why allocation of MAX memory between 2 is vastly diff.
Note:- Yes we have same load and database running as we did load test on both Physical and VM’s. Above is one thing we are noticing.