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 500GB, 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 500GB, after any flush of cache/restart activity on SQL instance , it is taking around 4-5 hours yet we still see almost 50% of that max memory usage. This seems that memory allocation extension somehow is very slow on this server
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.
Edit:- 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. Database migrated to 2017 is still on compat 110 i.e. SQL2012
Best Answer
More likely that you had query plans on SQL 2012 that required scanning more data than the plans on SQL 2017, and a side-effect of those scan-heavy plans is that they loaded most of your database into memory.
Or your disks could be slower, requiring more time (PAGEIOLATCH wait time) to to load the database pages into the page cache.