Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
It's recomended to enable trace flag 834 to use large-page on buffer pool for SQL Server running on VMware?
The tuning options kb of Microsoft (https://support.microsoft.com/en-us/kb/920093) don't mentions anything about virtualization and I have read somewhere that is mostly for dedicated machines.
Let's assume that the VM is more than 64GB in RAM and it's presented a topology of 2 v-sockets with 8 v-cores per socket for a 16 v-core VM.
Is the trace flag of help inside the hypervisor?
Large Pages change allocation of memory from 4KB to 2MB (normally), which means that the TLB (translation look-aside buffer) is improved in the CPU increasing the performance.
I've successfully used Trace flag 834 on production server in VMware for over a year. One important part before enable trace flag is that in VMware you should reserve at least the amount of memory dedicated to SQL Server, this is because the lock-pages in memory privilege required to enable large pages, "locks" the memory so it can't be swapped (paginated) to disk. So if the balloning driver kicks in, it won't try to force to reclaim memory from SQL Server.
Additionally the host should not be overcommitted in memory, if so it will degrade performance.
It should be carefully tested if there is a performance gain, so if unsure, just don't enable it. Also I have read that "Large Pages May Be Harmful on NUMA Systems" but I'm not sure if it applies to Windows/SQL Server
Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.
I'd be very careful using this flag on a VM, as their memory has an extra level of abstraction.
Had more than enough trouble with it on physical servers with lots of RAM dedicated to SQL. Example: with 3 2008R2 instances co-hosted when restarting one of them it took forever to come back because it could not find contiguous memory segments anymore.
The performance benefits were neither here not there (lets say 'statistically insignificant overall). I treat it as a 'TPC special'.
Also consider that 834 doesn't play nice with columnstores either.
