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