Sql-server – Lock pages in memory and Virtual SQL Server

Testing isolated queries isn't going to help you make this decision. As you have an existing database live, use it to capture a representative workload.

• Capture a profiler trace from your live, dedicated server
• Use the RML toolset to create a replay file
• Execute against the virtual server using ORCA

I posted a brief summary of the RML tools in an earlier question which you may find useful. To compare results across both runs, capture a trace of the first trace being run against the virtual server and run both through ClearTrace.

I'm not sure I believe there was ever an actual issue. Just because max server memory was set to 2 petabytes doesn't mean it would ever use that much. This is the functional equivalent of setting it to infinity, and just means you trust SQL Server to use as much memory as it needs.

Setting it to 500 MB seems artificially low to me. While your database is currently 128 MB on disk, you are probably not running any query patterns that lead to any performance issues even with that limit on memory. But when SQL Server needs more than 500 MB of memory, you're going to start noticing. SQL Server is designed to behave as if it is the only server on the machine, so it will take memory as it needs it, and won't release it unless the operating system needs it and applies pressure to SQL Server (discussions of LPIM aside). Restricting SQL Server's memory this way (or taking it away) will just cause performance degradation as it is much more expensive to page data in and out of memory, onto disk, etc. which will most certainly be required as your database grows. Think about trying to put a pig into a tube sock - eventually you're going to need a bigger sock. Why wait until the current sock rips?

I suggest a higher limit, but I can't tell you what that would be. You didn't disclose how much memory is on the server, whether it is x86 or x64, or what else is going to be running there. If the server is dedicated to SQL Server, I see absolutely no reason to save memory - what are you saving it for?

For a bit of an anecdotal observation, I don't think I've ever seen any instance of SQL Server on any kind of physical or virtual machine limited to 500 MB, and I have very serious doubts that this could possibly be the optimal setting for you in the long run.

I'd also suggest doing research into SQL Server settings and then changing them, instead of the other way around. This situation sounds a lot like saying "What does this button do?" after pressing it...