General best practices

This page provides best practices for getting the best performance, durability, and availability from Cloud SQL.

If issues occur with your Cloud SQL instance, review the following during troubleshooting:

Instance configuration and administration

Best practice More information
Read and follow the operational guidelines to ensure that your instances are covered by the Cloud SQL SLA.
Configure a maintenance window for your primary instance to control when disruptive updates can occur. See Maintenance window.
If you delete and recreate instances regularly, use a timestamp in the instance ID to increase the likelihood that new instance IDs are usable.
Don't start an administrative operation before the previous operation has completed.

Cloud SQL instances do not accept new operation requests until they have completed the previous operation. If you attempt to start a new operation prematurely, the operation request fails. This includes instance restarts.

The instance status in the Google Cloud console does not reflect whether an operation is running. The green check mark denotes only that the instance is in the RUNNABLE state. To see whether an operation is running, go to the Operations tab and check the status of the most recent operation.

Configure storage to accommodate critical database maintenance.

If the enable automatic storage increases instance setting is disabled or the automatic storage increase limit is enabled, ensure you have at least 20% available space to accommodate any critical database maintenance operations that Cloud SQL may perform.

To get alerted on available disk space falling below 20%, create a metrics-based alerting policy for the disk utilization metric with an above threshold position and a value of .8. For more information, see Create metrics-based alert policies.
Prevent over-utilization of your CPU.

You can view the percentage of available CPU that your instance is using on the instance details page in the Google Cloud console. For more information, see Metrics. You can also monitor your CPU usage and receive alerts at a specified threshold using Create metric-threshold alerting policies.

To avoid over-utilization, you can increase the number of CPUs for your instance. Changing CPUs requires an instance restart. If your instance is already at the maximum number of CPUs, you must shard your database to multiple instances.
Avoid memory exhaustion.

When looking for signs of memory exhaustion, you should primarily use the usage metric. To avoid out-of-memory errors, we recommend that this metric remains below 90%.

You can also use the total_usage metric to observe the percentage of available memory that your Cloud SQL instance is using, including memory used by the database container and memory allocated by the operating system cache.

By observing the difference between the two metrics, you can identify how much memory is used by processes versus how much is used by the operating system cache. You can repurpose the memory in this cache.

To predict out-of-memory issues, check both metrics and interpret them together. If the metrics appear high, the instance might be low in memory. This can be because of a custom configuration, the instance being undersized for the workload, or a combination of these factors.

Scale your Cloud SQL instance to increase the size of its memory. Changing the instance's memory size requires an instance restart. If your instance is already at the maximum memory size, you must shard your database across multiple instances. To learn more about monitoring both metrics in the Google Cloud console, see Metrics.
Set SQL Server settings so that they work optimally for Cloud SQL. See SQL Server settings.
Tune the instance optimally for test runs. The following table lists configuration values suitable for test runs.
  • vCPU: 40
  • Memory: 262144 MB
  • MAXDOP: 8
  • Cost threshold for parallelism: 120
  • tempdb files: 8. Pre-sized to prevent autogrowth.
  • User database files: Autogrow set in 64-128 MB. Presized to prevent autogrowth.
  • Storage: >= 4TB for the best IOPS
Determine the capacity of the I/O subsystem before you deploy SQL Server.

Test a variety of I/O types and sizes. The size of the I/O issued to the persistent disk storage coming from SQL Server affects the IOPS and throughput. The SQL Server workload is throttled when it reaches the IOPS limit or the throughput limit. The storage type being used in Cloud SQL is PD SSD, which is suitable for high-performance enterprise-level workloads.

Customize the VM to maximize performance as follows:

  • A disk size of 4TB or greater provides more throughput and IOPS.
  • Higher vCPU provides more IOPS and throughput. When using higher vCPU, monitor the DB waits for parallelism, which might also increase.
  • For optimal performance, issue I/O in parallel to achieve a higher I/O queue depth.
Prevent index fragmentation and missing indexes. Reorganize your index or set up a schedule to rebuild your index depending on how often your data is changing. Also, set an appropriate fill factor to reduce fragmentation. Monitor SQL Server for missing indexes that might offer improved performance.
Update statistics regularly. If statistics are outdated, SQL query optimizer might generate suboptimal query plans. Update statistics especially after large amounts of data were changed. Use the query store to monitor and troubleshoot the SQL Server that has suboptimal query plans.
Prevent database files from becoming unnecessarily large.

Set autogrow in MBs rather than as a percentage, using increments appropriate to the requirement. Also, proactively manage the growth before autogrowth kicks in.

Additionally, ensure that the Cloud SQL Enable automatic storage increases feature is enabled so Cloud SQL can add storage space if the database and the instance run out of space.

Detect database integrity issues by running DBCC CHECKDB at least once a week. DBCC CHECKDB checks the integrity of all the objects in a database. By running DBCC CHECKDB weekly, you can ensure that your databases aren't corrupt. DBCC CHECKDB is a resource-intensive operation that can impact your instance's performance.
Don't run DBCC CHECKDB on a production server.
We recommend that you use one of the following options instead of running DBCC CHECKDB on a production server:
  • Clone a database and run DBCC CHECKDB on the clone database.
  • Restore a backup to another instance and then run DBCC CHECKDB on the restored instance's databases. For more information about restoring an instance, see Restore an instance.

Use the following code snippets to run DBCC CHECKDB on a database:

  • (Recommended) Run DBCC CHECKDB with EXTENDED_LOGICAL_CHECKS. This is a comprehensive but more resource-intensive check. 
          USE DATABASE_NAME
      DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS,
      DATA_PURITY,NO_INFOMSGS, ALL_ERRORMSGS
  • Run DBCC CHECKDB with PHYSICAL_ONLY: 
          USE DATABASE_NAME
      DBCC CHECKDB WITH PHYSICAL_ONLY,
      NO_INFOMSGS, ALL_ERRORMSGS

Data architecture

Best practice More information
Split your large instances into smaller instances, where possible. When possible, using many smaller Cloud SQL instances is better than one large instance. Managing a large, monolithic instance presents challenges not posed by a group of smaller instances.
Don't use too many database tables.

Keep your instance's table count to fewer than 10,000. Too many database tables can impact database upgrade time.

Database collation Whether you're installing a new instance of SQL Server, restoring a database backup, or connecting a server to client databases, it's important to understand the locale requirements, sorting order, and case and accent sensitivity of the data that you're working with. When you select a collation for your server, database, column, or expression, you're assigning certain characteristics to your data. These characteristics affect the results of many operations in the database. For example, when you construct a query by using ORDER BY, the sort order of your result set might depend on the collation that's applied to the database or dictated in a COLLATE clause at the expression level of the query. Read more about database collations and unicode support.
Query design For optimal database or query performance, make sure you are not using a large number of tables within the same query (sixteen or more).
Query monitoring Queries might degrade over time. It's important to monitor your application and query performance over time. One reason for such degradation is hash bailouts.
Recursive hash joins or hash bailouts cause reduced performance in a server. If you see many hash warning events in a trace, update statistics on the columns that are being joined. Read more on hash bailouts.

Application implementation

Best practice More information
Use good connection management practices, such as connection pooling and exponential backoff. Using these techniques improves your application's use of resources and helps you stay within Cloud SQL connection limits. For more information and code samples, see Managing database connections.
Test your application's response to maintenance updates, which can happen at any time during the maintenance window. Try self-service maintenance to simulate a maintenance update. During maintenance, your instance becomes unavailable for a brief period, and existing connections are dropped. Testing maintenance rollouts gives you a better understanding of how your application handles scheduled maintenance and how quickly the system can recover.
Test your application's response to failovers, which can happen at any time. You can manually initiate a failover using the Google Cloud console, the gcloud CLI, or the API. See Initiating failover.
Avoid large transactions. Keep transactions small and short. If a large database update is needed, do it in several smaller transactions rather than one large transaction.
If you are using the Cloud SQL Auth Proxy, make sure you are using the most up-to-date version. See Keeping the Cloud SQL Auth Proxy up-to-date.

Data import and export

Best practice More information
Speed up imports for small instance sizes. For small instances, you can temporarily increase the CPU and RAM of an instance to improve performance when importing large datasets.
If you are exporting data for import into Cloud SQL, be sure to use the proper procedure. See Exporting data from an externally managed database server.

Backup and recovery

Best practice More information
Protect your data with the appropriate Cloud SQL functionality.

Backups and exports are ways to provide data redundancy and protection. They each protect against different scenarios and complement each other in a robust data protection strategy.

Backups are lightweight; they provide a way to restore the data on your instance to its state at the time you took the backup. However, backups have some limitations. If you delete the instance, the backups are also deleted. You can't back up a single database or table. And if the region where the instance is located is unavailable, you cannot restore the instance from that backup, even in an available region.

Exports take longer to create, because an external file is created in Cloud Storage that can be used to recreate your data. Exports are unaffected if you delete the instance. In addition, you can export only a single database or even table, depending on the export format you choose.

When using the export backup feature on an Enterprise or Standard SQL Server instance, avoid creating a GZ archive file because it tries to compress a backup that's already natively compressed by SQL Server.

Protect your instance and backups from accidental deletion.

A Cloud SQL instance that you create in the Google Cloud console or via Terraform enables accidental deletion prevention by default.

Use the export feature in Cloud SQL to export your data for additional protection. Use Cloud Scheduler with the REST API to automate export management. For more advanced scenarios, Cloud Scheduler with Cloud Run functions for automation.

SQL Server settings

Some SQL Server settings are recommended for Cloud SQL. The following topics describe some recommendations.

Global configuration settings

Setting Recommendation
max worker threads Retain the default value of 0. This setting defines the number of threads available to SQL Server based on the number of CPUs. The value is automatically calculated by the SQL Server engine at startup.
max server memory (MB)

This flag limits the amount of memory that Cloud SQL can allocate for its internal pools.

If you don't set a value for this flag, then Cloud SQL manages the value automatically, based on the size of the RAM for your instance. Also, if you resize your instance, then Cloud SQL adjusts the value of the flag automatically to meet our recommendations for the new instance size.

We strongly recommend that you don't specify a value for this flag for your instances. If you set the value to be greater than 80%, then you might experience instability, performance degradation, and database crashes because of out-of-memory issues.

If you prefer to manage the value for this flag, then set it manually. As a result, Cloud SQL deactivates automatic management. If you're resizing your instance, then consider revisiting the value to match the recommended values for a new size.

We recommend using the following formula to set the
max server memory database flag:

  • Reserve 1.4 GB of memory for the OS and agents.
  • If the RAM on the server is less than or equal to 16 GB, then reserve 1 GB of memory for each 4 GB of RAM.
  • If the RAM on the server is greater than 16 GB, then leave 4 GB of memory and reserve 1 GB of memory for each 8 GB of RAM that's greater than 16 GB.

For example, if the RAM for your instance is 104 GB
(106496 MB), then reserve:

  • 1.4 GB of memory for the OS and agents
  • 4 GB of memory, because 104 GB is greater than 16 GB
  • 11 GB of memory, because there's 88 GB of RAM that's greater than 16 GB (104-16=88), and 88 divided by 8 is 11

For this example, you must reserve 16.4 GB of memory. As a result, for the value of this flag, specify 89702 MB
[(104-16.4) * 1024 = 89702].

The following table has recommended values and percentages of total RAM for some popular virtual machine (VM) tiers:

Instance tier (MB) Max server memory (MB) % (Total)
3840 1440 37
4096 1632 39
5792 2912 50
8192 4704 57
11584 7248 62
16384 10848 66
23168 16800 72
32768 25200 76
46336 37072 80
65568 53888 82
92704 77648 83
131136 111248 84
185440 158784 85
262272 226000 86
370880 321056 86
524544 455488 86
741792 645600 87

To monitor the memory usage for your instance, use the following metrics:

  • database/memory/usage
  • database/sqlserver/memory/buffer_cache_hit_ratio
  • database/sqlserver/memory/memory_grants_pending
  • database/sqlserver/memory/page_life_expectancy

For more information, see Monitor Cloud SQL instances.

Database settings to modify

For optimal performance of the SQL Server database, set the following SQL Server settings as suggested below.

Setting Recommendation
cost threshold for parallelism

This is the threshold at which the SQL optimizer executes a query using parallelism. The default value of 5 can cause too many queries to run in parallel, thereby increasing database wait time on parallel threads. To reduce this type of contention, increase the value.

The value is ignored when maxdop is set to 1.

max degree of parallelism (MAXDOP)

To reduce database waits due to parallelism, adjust this value based on specific recommendations about the number of logical processors available. Measure performance carefully if setting this option to 1.

optimize for ad hoc workloads

Avoid having a large number of single-use plans in the plan cache. To improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches, set this option to 1.

tempdb

Pre-size tempdb so that it does not need to autogrow. All files in tempdb should be equally sized and have the same file growth set.

The database wait type for tempdb contention appears as PAGELATCH_UP. To reduce the contention, add more files.

If the number of processors is less than or equal to 8, use the same number of files as logical processors. If the number of processors is greater than 8, use 8 data files. If contention continues, increase the number of files by multiples of 4 until there is no further contention.

Depending on your workload, you might want to modify the following settings as well.

Setting Recommendation
Close Cursor on Commit Enabled The default value is off, which means that cursors are not closed automatically when you commit a transaction.
Default Cursor This option controls the scope of a cursor used in T-SQL code. If you change this setting, evaluate the application code for any adverse effects.
Page Verify This option allows SQL Server to calculate a checksum for a database page before it is written to disk and store the checksum in the page header. When a page is read again, the checksum is recomputed to verify the integrity of the page. The recommended value is checksum.
Parameterization The default value is simple. Simple parameterization allows SQL Server to replace literal values in a query with parameters. Microsoft provides guidelines about how to change this value and use it with plan guides.

Database settings to retain

For optimal performance of the SQL Server database, retain the default values of the following SQL Server settings.

Setting Default value to be retained
Auto Close False. This setting, when on, opens and closes connections and flushes the procedure after each connect. This can cause performance degradation in databases that are frequently accessed.
Auto Shrink False. Turning it on can lead to database and index fragmentation and other performance issues, some of which are discussed in this SQL Server blog.
Date Correlation Optimization Enabled False. Enabling it can let the optimizer find and optimize relationships between dates across two related tables. Tracking this in SQL Server comes with some performance overhead.
Legacy Cardinality Estimation False. In some cases, SQL Server cannot accurately calculate cardinalities when this setting is enabled.
Parameter Sniffing ON. Parameter sniffing from database tables can help create execution plans for reuse. If the tables have unevenly distributed data, the resultant execution plans might lead to performance issues. With such data, use other options from the Query Store rather than modify this setting.
Query Optimizer Fixes False. When enabled, it can affect the performance of the SQL Server cardinality estimator. If you choose to enable it, test to ensure that there is no query regression.
Auto Create Statistics True. This option allows SQL Server to create single-column statistics that can improve cardinality estimates for query plans.
Auto Update Statistics True. This option allows SQL Server to update out-of-date statistics using a re-compilation threshold that is based on table cardinality.
Auto Update Statistics Asynchronously False. This option, when enabled, directs the SQL query optimizer to use the stale statistics for the current query execution, while updating the statistics asynchronously to benefit future workloads.

However, if you expect predictable response time for a frequently executed query or if your application frequently experiences client request timeouts while awaiting statistics updates, consider enabling this option and disabling Auto Update Statistics.

Target Recovery Time (Seconds) 60. This setting establishes an upper bound on the recovery time for a database by flushing dirty pages more or less frequently to the disk from the buffer pool. For highly transactional workloads, a lower value for this setting, combined with the storage IOPS near the maximum value, can contribute to a performance bottleneck.

Trace flag settings

Trace flags in SQL Server are used to set certain characteristics, alter the behavior of SQL Server databases, or debug issues in SQL Server.

Some SQL Server trace flags are supported in Cloud SQL and can be set using database flags. The recommended settings are as follows.

Trace flag Recommended
1204 Yes, except for workload-intensive servers that generate a lot of deadlocks.

Returns the resources and types of locks participating in a deadlock and also the command currently affected.
1222 Yes, except for workload-intensive servers that generate a lot of deadlocks.
1224 No. This can result in more memory usage and cause memory pressure on the database.
2528 No. Parallel checking of objects is the default and is recommended. The degree of parallelism is automatically calculated by the database engine.
3205 No. Tape drives for backups is a feature of Cloud SQL for SQL Server.
3226 No, unless you need frequent backups, such as TLOG backups.
3625 No. Because the root account does not have system administrator access, it might not be able to see all error messages.
4199 No. This affects the cardinality estimator and can lead to query regression.
4616 No. This restriction lowers the security around application roles. It needs to be validated based on application requirements.
7806 Yes. If the database server becomes unresponsive, the dedicated admin connection (DAC) might be the only way to make a connection for diagnostics.

What's next

For more information about general practices by database engine, see: