Not coincidentally, Microsoft's primary use case for 1GB pages on Windows is MS SQL server.
It is automatically enabled, but only if you have the "Lock pages in memory" privilege assigned to the database engine service account...
... which the default SYSTEM account has enabled by default...
... but not if you set up a typical cluster with a domain user account.
So in other words, the scenario where you would want this enabled the most -- large expensive enterprise clusters -- is where it is accidentally disabled with no warning.
I use this as a free 10-20% performance boost. Add in a few other similar tuning settings and I can get a 50% pref improvement on just about any "enterprise" cluster without having to get clever.
Turbo buttons are fun to press.
Have you ever written up some of the things you do to improve performance, or is that something you're unable to share?
- Enable the "Lock pages in memory" privilege.
- Enable the "Perform volume maintenance" privilege.
- Enable the "Ad hoc query optimisation" setting.
- If on-prem, set power management to "High Performance", ideally in the hardware BIOS, but failing that, at the hypervisor level.
- Upgrade the OS and DB engine version if this is compatible with the apps.
- Run sp_Blitz and implement any critical recommendations.
The below recommendations apply to cloud-hosted servers only:
- Upgrade to the latest gen DB-optimised VM sizes. In Azure I use Ebds_v5 and Eads_v5.
- Use the "temporary storage" SSD volume for tempdb.
- Consolidate storage. Striped a single large logical volume across multiple 1 TB disks. NEVER use the 1990s era many-small-volumes scheme of having separate data, log, tempdb, and templog drives!
- Move the DB VM and the App VMs into a single "Proximity Placement Group" or the equivalent construct.
In my experience the above list generally yields a total performance improvement of anywhere from 40% to 300% with minimal risk.
If you sprinkle some light tuning on top, such as judiciously creating a handful of indexes with a high predicted performance benefit can take you even further.
Past that, you have to get elbow deep into the schema, which is typically only possible with "in house developed" databases.
https://www.sqlshack.com/category/sql-server-performance-tun...