Workload Manager best practices for SQL Server

This document lists the best practices that Workload Manager supports for evaluating SQL Server workloads running on Google Cloud. To learn about Workload Manager, see Product overview.

Best practices for SQL Server workloads

The following table shows the Workload Manager best practices for evaluating SQL Server workloads that run on Google Cloud.

Note that to enable Workload Manager for evaluating your SQL Server workloads, you must set up Google Cloud's Agent for SQL Server on the host VMs.

Category Best practice name and description Severity
Stability SQL: Perform backup regularly

When taking regular database backups, be careful not to consume too many persistent disk IOPS. Use the local SSD to stage your backups and then push them to a Cloud Storage bucket.

For more information, see Backing up in Best practices for SQL Server VMs. 		Severity: Medium Medium
Performance SQL: Enable the buffer pool extension

The buffer pool extension feature lets you push clean pages to a local SSD, instead of dropping them. This works along the same lines as virtual memory, which is to say, by swapping, and gives you access to the clean pages on the local SSD, which is faster than going to the regular disk to fetch the data.

For more information, see Enabling the buffer pool extension in Best practices for SQL Server VMs. 		Severity: Medium Medium
Performance SQL: Format secondary disks

Formatting a disk with a 64 KB allocation unit lets SQL Server read and write extents more efficiently, which increases the I/O performance of the disk.

Severity: Medium Medium
Failover Clustering SQL: Enable failover clustering

To enable failover clustering in the Compute Engine agent, you need to add the flag enable-wsfc=true to your VM metadata.

For more information, see Enabling failover clustering in Compute Engine. 		Severity: Medium Medium
Performance SQL: Avoid index fragmentation

If one or more indexes are 95%+ fragmented, we recommend scheduling defrag jobs.

Severity: Medium Medium
Performance SQL: Move data files and log files to a new disk

By default, the preconfigured image for SQL Server comes with everything installed on the boot persistent disk, which mounts as the `C:` drive. Consider attaching a secondary SSD persistent disk and moving the log files and data files to the new disk.

For more information, see Place data and log files on separate drives in SQL Server documentation. 		Severity: Medium Medium
Stability SQL: Match max server memory setting with available physical memory on the instance

We recommend that the max server memory setting matches the available physical memory on the instance.

Severity: Medium Medium
Performance SQL: Set the power profile to High-Performance

To configure SQL Server for optimal performance on Google Cloud, we recommend that you set the power profile to High-Performance instead of Balanced.

For more information, see Setting the power profile. 		Severity: Medium Medium
Performance SQL: SQL: Match max degree of parallelism to the number of CPUs on the server

Google Cloud recommends that the default setting for max degree of parallelism matches the number of CPUs on the server. In practice, 8 works as a good default value.

For more information, see Parallel query processing in Best Practices for SQL Server VMs. 		Severity: Medium Medium
Performance SQL: Use compressed tables

Compressing tables could make your system perform faster.

For more information, see Using compressed tables in Best Practices for SQL Server VMs. 		Severity: Medium Medium
Performance SQL: Use Local SSDs for tempDB

Create new SQL Server instances with one or more local SSDs to store the tempDB and Windows paging files.

Severity: Medium Medium
Performance SQL: Set log file to fixed amount and schedule regular backups

Consider disabling autogrowth and setting your log file to a fixed size.

Severity: Medium Medium
Performance SQL: Optimize Virtual Log Files

Monitor Virtual Log File growth and take action to prevent log file fragmentation.

For more information, see Optimizing virtual log files in Best Practices for SQL Server VMs. 		Severity: Medium Medium
Cost Optimization SQL: Disable simultaneous multithreading (SMT)

Disabling SMT reduces the number of vCPUs for each core by half, which might reduce your licensing costs. For more information, consult your licensing agreement.

An industry best practice for an optimal TCO for most SQL Server workloads is to set the number of threads per core to one, and then to right-size the VM shape according to the workload requirements. Controlling the number of cores is further possible using custom visible cores. Consult your technical account manager for further details.

For more information, see how to set the number of threads per core. 		Severity: Medium Medium