3 ms·

> 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" privileg

> 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.

pocketarc 4y ago
> Add in a few other similar tuning settings Have you ever written up some of the things you do to improve performance, or is that something you're unable to share?
- eptcyka 4y ago
  And lose a source of income? This isn't how business is done in MS lands.
- speed_spread 4y ago
  I'd assume the biggest turbo buttons are already on stackoverflow. Some may depend on specific database usage patterns.
- Rimintil 4y ago
  Lots of information at: https://www.sqlshack.com/category/sql-server-performance-tuning/ https://www.sqlshack.com/category/sql-server-performance-tun...
- jiggawatts 4y ago
  The bare list of safe "turbo buttons" is below. Just google them for the specifics and the benefits: - 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.
hosteur 4y ago
Would love to hear about the other tuning settings.
- jiggawatts 4y ago
  See my comment here: https://news.ycombinator.com/item?id=34618514 https://news.ycombinator.com/item?id=34618514

pocketarc 4y ago

> Add in a few other similar tuning settings Have you ever written up some of the things you do to improve performance, or is that something you're unable to share?

eptcyka 4y ago
And lose a source of income? This isn't how business is done in MS lands.
speed_spread 4y ago
I'd assume the biggest turbo buttons are already on stackoverflow. Some may depend on specific database usage patterns.
Rimintil 4y ago
Lots of information at: https://www.sqlshack.com/category/sql-server-performance-tuning/ https://www.sqlshack.com/category/sql-server-performance-tun...
jiggawatts 4y ago
The bare list of safe "turbo buttons" is below. Just google them for the specifics and the benefits: - 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.

hosteur 4y ago

Would love to hear about the other tuning settings.

jiggawatts 4y ago
See my comment here: https://news.ycombinator.com/item?id=34618514 https://news.ycombinator.com/item?id=34618514