3 ms·

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

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

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