Cloud SQL for SQL Server: Database administration best practices

Cloud SQL for SQL Server is a fully-managed database service that makes it easy to set up, maintain and administer SQL Server databases on Google Cloud Platform. While Cloud SQL reduces much of the operational toil, the SQL Server Databases running on Cloud SQL still require administration in order to perform optimally. This blog covers key database administration activities, and should be reviewed along with existing Internal DBA checklists, policies, procedures, runbooks and IT Operations guides.

Common DBA responsibilities and a DBA checklist 

As a SQL Server database administrator (DBA), you will be involved in various tasks including:

1. Creating databases.

2. Managing Database Objects (Tables, Views, Stored Procedures, etc.).

3. Managing users and security.

4. Backing up the database and performing recovery operations when necessary.

5. Cloning and provisioning database instances for specific customers.

6. Monitoring the state of the instance and taking preventive or corrective action as required.

7. Monitoring and tuning database performance.

8. Diagnosing and reporting critical errors to GCP Support Services.

In large, enterprise environments, the job is often shared by multiple DBAs. In a small or medium-sized database environment, a single person might perform all database administration tasks.

DBA checklist 

The following DBA checklist should help you get started with managing your SQL Server databases on your Cloud SQL Instance. Some checklist tasks may be performed daily and others weekly or monthly, depending on database utilization and your business requirements.

1. Review error logs including SQL Server Error Log and SQL Server Agent Log.

2. Review and set Cloud SQL Maintenance Windows.

3. Backup and Restore: Review configuration, job success and alignment with RTO and RPO requirements.

4. High availability (HA) and disaster recovery(DR): Review configuration and alignment with your organization’s HA and DR requirements.

5. Test database restores weekly, run DBCC CheckDB weekly.

6. Monitor Workloads (transactional vs batch), scheduled jobs, processing and maintenance windows: review workloads, schedules and runtimes. Ensure you are tracking each workload including schedule, runtime duration (Min, Max, Avg, stDev).

7. Review applications connecting to the Cloud SQL Instance. Understand connection patterns as well as read isolation requirements such as read uncommitted vs. read committed.

8. Review standard database maintenance jobs such as index defragmentation and statistics updates.

Cloud SQL configuration

Cloud SQL for SQL Server is configurable at the instance level and at the database level. Most Instance-level configuration options can be managed through the Cloud Console. It’s worth taking time to review instance-level configurations as they impact the performance of all databases. 

Instance-level configuration

Instance configuration including vCPUs, Memory and Storage are managed through the Cloud Console. Use the Cloud Console to make any changes as required.

Tempdb

Tempdb is a system database in SQL Server that is used for internal processing of temporary objects in SQL Server. Currently tempDB is created with 4 or 8 data files, dependent on instance vCPU count.  Formerly, the default configuration of one data file was often not adequate for optimal performance. Microsoft recommends in this link adding additional files to reduce contention in tempdb. All of the files in tempdb need to be equally sized and have the same file growth settings. It is also recommended to pre-size tempdb so that it does not need to autogrow. For less than 8 vCPUs, the number of tempdb files should match the vCPU count. For more than 8 vCPUs, use 8 tempdb files. Make changes to your tempdb configuration using Microsoft’s SQL Server Management Studio (SSMS) or T-SQL statements. 

To view tempdb configuration, run the query below.

Database flags (also known as trace flags)

Cloud SQL database flags, also known to the SQL Server community as trace flags, influence instance behavior and performance. Review supported flags for a list of all supported Database Flags. To view the database flags currently implemented for an instance, run the following T-SQL statement.

Configuration settings 

Lastly, for a comprehensive summary of your Cloud SQL for SQL Server instance, review Glen Berry’s SQL Diagnostic queries for SQL 2017, which include queries to support the collection of instance-level and database-level information.  

Database-level configuration

Database-scoped configurations

Database-scoped configurations and database properties allow a DBA to view and set database-level settings for a specific database. To view all database-scoped configurations used for a specific database, run the following sql statement:

To change database-scoped configurations for a specific database, modify the following example syntax.

Other common database-scoped configurations for consideration dependent on your database compatibility level, performance and workload may include the following: PARAMETER_SNIFFING, OPTIMIZE_FOR_AD_HOC_WORKLOADS, LEGACY_CARDINALITY_ESTIMATION

To view a database setting for a specific database, run the following sql statement:

To change a setting for a specific database, run the following sql statement:

Auto-shrink and auto-close

To avoid performance issues related to database mounting and fragmentation, auto-close and auto-shrink should be set to OFF for all databases.

Maximum degree of parallelism (MAXDOP)

SQL Server will apply the max degree of parallelism option to limit the number of processors to use in parallel plan execution.  For example, if a Cloud SQL instance is provisioned with 40 vCPU’s the SQL Optimizer may decide to use all 40 vCPU’s when executing a parallel query. 

Sometimes excessive parallelism may lead to performance problems, normally exhibited by long query times. If you are experiencing multiple parallel threads running when executing queries and some threads appear to be waiting much longer than others before returning data, consider adding an OPTION (MAXDOP 8) hint to your query. For example, if the Query contains the hint OPTION (MAXDOP 8), SQL Server will limit parallelism to only 8 vCPU’s. 

MAXDOP is set at the database level for SQL Server on Cloud SQL. Run the T-SQL statement below to set and then view the MAXDOP setting for a specific database.

Database growth and datafile growth

Database growth must be enabled at the Instance Level and at the database level. 

1. At the instance level, enable autogrowth on the cloud sql instance by selecting the checkbox “Enable automatic storage increases”.

2. At a database level, it is the responsibility of the DB owner to enable autogrowth. View Microsoft’s documentation on configuration parameters for more information. 

Update database file auto-growth settings to use a MB increment rather than a % growth rate. Depending on your database, start with a 128MB or 256MB  growth increment for data files and a 256MB growth increment for transaction log, and increase the file growth increment if required.  In some cases, larger growth increments such as 1024MB for datafiles and 4096MB for log files may be applicable. Regardless, understanding the IO behavior of your database and application requirements will allow you to choose the best growth rate based on your workload. 

Backups, high availability and database clones

Backups

It’s critical to understand and document your organization’s Recovery Time Objective (RTO) and Recovery Point Objective (RPO) for each database on your Cloud SQL instance. Databases on Cloud SQL operate in SIMPLE recovery mode. Review your Instance backup schedule and retention settings. Instance Backups may also be run on demand and also can be scheduled as required.  Database backups are known in CloudSQL as database Exports. You can export a database to a gcp bucket as a .bak file using a gcloud command or the Cloud Console. If you are importing or exporting large amounts of data into database tables, use the Microsoft bcp Utility.

DBCC CHECKDB

DBCC CHECKDB is used to check the integrity of all the objects in the specified database. By running DBCC CHECKDB weekly, DBA’s can ensure that the database is free of corruption. Rather than running DBCC on a production server, clone a database and run DBCC CHECKDB on the clone, or restore a backup to another instance and then run DBCC CHECKDB on the instance.

High availability

Instance High Availability is enabled using the Cloud Console. Unlike various SQL Server HA technologies such as replication or mirroring, Cloud SQL uses regional persistent disks (RePDs) to replicate data across two zones in a single region. It is recommended to enable High Availability for business critical instances.

Database clones

Cloning a Cloud SQL instance creates a new instance that is a copy of the source instance. The new instance is completely independent from the source instance. Cloning is useful for the following reasons:

1. Fork a database before upgrading a database or application.

2. Blue/Green code deployments, application deployments.

3. Provisioning instances for other teams and departments for development, testing or other purposes.

Clones may be created using the Cloud Console or the gcloud command line.

Database maintenance

Instance maintenance

Cloud SQL instances need occasional updates to fix bugs, prevent security exploits, and perform upgrades. Maintenance is performed, on average, once every one to two months, and results in downtime for an instance. After applying updates, Cloud SQL restarts, which can cause a disruption in service. Restarting Cloud SQL normally takes less than 90 seconds. During a restart, HA primary instances do not fail over and applications will not be able to connect. Review the Cloud SQL database maintenance schedule and ensure that it aligns with your existing IT Operations schedules as well as other scheduled jobs, such as backups and database maintenance.

Index maintenance

SQL Server Indexes play a key role in minimizing the amount of IO required to retrieve data. Optimally designed and managed indexes can dramatically decrease the IO required to retrieve data and allow SQL Server to serve a higher number of concurrent users. Un-managed or poorly designed indexes can have the opposite effect. A few key points to consider when developing an indexing strategy:

1. Before creating indexes, DBA’s must carefully consider and analyze a host of data points including data structures, table types, existing indexes, query execution plans, data access patterns and query response requirements.

2. While Indexes can speed up data retrieval (Select), Indexes can also slow down Insert, Update and Delete operations.

3. Many factors contribute to designing the right type of index including the type of index to create, the underlying table type, index columns and the ordering of columns.

4. Once Indexes are created they must be maintained, monitored and optimized as data volumes, application features, use cases and query patterns change.

Consider reviewing and installing the SQL Server Maintenance Solution which includes index optimization routines. Remember to install the objects in a non-system database. You can also review Glen Berry’s SQL Diagnostic queries for SQL 2017, which contain several useful queries to monitor index structure, missing indexes and unused indexes. 

Statistics Maintenance 

SQL Server Statistics are used by the SQL Server Optimizer to create an execution plan for a query. When data changes frequently, statistics may become outdated, resulting in suboptimal query plans. In many cases, updating statistics daily for frequently changing tables may make sense.  A few key points to remember about SQL Server statistics follow:

1. Updated statistics provide the SQL Optimizer with current information from which to create an execution plan. Stale statistics may result in poor query performance and variable query runtimes.

2. SQL Server will update statistics automatically when a variable % of rows change. 

3. SQL Server will create statistics on columns under certain conditions, though it is recommended that DBA’s explicitly create statistics. 

4. Creating statistics is an iterative process performed by a DBA or Database Developer and normally involves capturing and analyzing a workload to determine where statistics could provide benefit, creating statistics, and then monitoring query performance.

In addition to enabling Auto Create statistics for each database, implement the database maintenance solution for maintaining statistics and use SQL Server Agent to schedule statistics maintenance jobs.

Monitoring and Alerting

Monitoring 

Monitoring Active SQL Server queries can be a challenge on a busy system, especially when collecting and analyzing execution plans is required. Install sp_WhoIsActive, an activity monitoring stored procedure developed by SQL Server MVP Adam Machanic, in a non-production database and use it for troubleshooting and analyzing queries.

1. Append the database name when calling the procedure. 

1. ex: exec dbtools.dbo.sp_WhoIsActive @get_plans=1

2. Use the parameter @get_plans=1 to capture the execution plan. Save this plan (which is in XML format) with a .sqlplan file extension and view it using SQL Server Management Studio

3. Consider logging sp_WhoIsActive output to a table for further analysis. 

2. Enable SQL Server Query Store

3. Review and run Wait Stats queries by Paul Randall to understand top waits for your instance and databases.

1. Current Waits

2. Overall Waits (Since last database restart) 

Reviewing SQL Server Log files helps DBA’s anticipate the potential problems that could occur, diagnose existing issues and take corrective actions. Use the Cloud Console to access SQL Server error logs and SQL Server Agent logs.

Alerting

Cloud Monitoring Alerts may be configured to notify administrators of specific  performance issues. Review GCP Monitoring and Alerting for details on how to set up alerts. 

Database DevOps

SQLPackage.exe is a Microsoft Utility which supports database deployments and is used for deploying code and objects to SQL Server.  SQLPackage.exe runs on Windows, macOS, and Linux.  Microsoft Visual Studio is a popular IDE which includes SQL Server database project templates, which support managed SQL Server database code. 

If you are developing and deploying custom applications on Cloud SQL, it’s worthwhile to understand how SQLPackage.exe and SQL Server Dacpac’s can be integrated into your existing DevOps build, test and release processes.

We hope this blog helps you to optimize your SQL Server databases. To learn more about best practices for Cloud SQL for SQL Server check out the documentation here.