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