Analyze database performance with Extended Events

This page describes how to use Extended Events (XEvents) on your instances.

Cloud SQL supports using XEvents for monitoring, identifying, and troubleshooting the performance of your database. To do this, you create XEvent sessions and read XEvent output files. You can read the XEvent output files either on the instance or by downloading the XEvent files from a Cloud Storage bucket.

Before you begin

If you want to upload your XEvent files to a Cloud Storage bucket, then create a bucket in your Google Cloud account.

Note: You need to manage log retention in the Cloud Storage bucket. For information about adding a retention policy to specify a retention period, see Retention policies and retention policy locks.

Best practices

• To avoid transferring large files across regions, we recommend that you do one of the following:
  • Create multi-regional Cloud Storage buckets
  • Set up the destination Cloud Storage bucket in the same region as the instance
• Before using XEvents, enable automatic storage increases. XEvent output files, depending on their total size and retention intervals, might need significant disk space.
• To avoid retaining too many XEvent files for a long time, set up a data retention policy for your Cloud Storage bucket.
• If you create an XEvent session by using the SSMS UI, but some boxes for input values don't appear, then resize the Create XEvent Session window.

How to create XEvent sessions

XEvents is enabled on your instances automatically.

Use the CREATE EVENT SESSION command to define new XEvent sessions. You also can use the user interface of SQL Server Management Studio (SSMS) to create XEvent sessions.

Cloud SQL enforces the following parameters for all XEvent sessions:

Parameter	Description	Allowed values
FILENAME	Specifies the filename and path for storing XEvents Session data.	Any path that starts with /var/opt/mssql/xevents/
MAX_FILE_SIZE	Specifies the maximum size of each output file before a new file is created.	A minimum of 10 MB A maximum of 1 GB
MAX_MEMORY	Specifies the maximum amount of memory to allocate to the session for event buffering.	A minimum of 4 MB A maximum of 8 MB
MAX_DISPATCH_LATENCY	Specifies the maximum time (in seconds) that events can remain in the event dispatch queue before being sent to the target.	A minimum of 1 sec A maximum of 30 sec
MAX_EVENT_SIZE	Sets the maximum size of each event, in bytes.	A minimum of 4 MB A maximum of 8 MB

XEvent session configuration parameters

You can configure XEvent sessions by using the following parameters as custom flags:

Parameter	Description
cloud sql xe bucket name	Specify the Cloud Storage bucket to which you want to upload the XEvent session output. If this isn't specified, then Cloud SQL doesn't upload the XEvent session output data to Cloud Storage.
cloud sql xe output total disk size (mb)	Specify the amount of disk space to be reserved for the output of all XEvent sessions. If this isn't specified, then Cloud SQL uses a default disk space of 512 MB. If the XEvent output files use disk space more than the value of cloud sql xe output total disk size (mb), then Cloud SQL removes these files from the disk.
cloud sql xe file retention (mins)	Specify the retention time on the instance in minutes for files generated by the file targets of XEvent sessions. If this isn't specified, then Cloud SQL uses a default value of 7 days.
cloud sql xe upload interval (mins)	Specify the upload interval for XEvent files being uploaded to Cloud Storage. If this isn't specified, then Cloud SQL uses a default value of 10 minutes.

For more information about setting these flags, see Configure database flags.

How to read XEvent session output files

You can read XEvent session output files from either a Cloud SQL instance or a Cloud Storage bucket.

Read XEvent output files from an instance

To retrieve the data from an XEvent file that an XEvent session creates, use the sys.fn_xe_file_target_read_file function.

Here's an example:

SELECT * FROM sys.fn_xe_file_target_read_file('/var/opt/mssql/xevents/filename*, NULL, NULL, NULL);

Read XEvent output files from a bucket

To read XEvent output files from a Cloud Storage bucket, download the files from the bucket to a SQL Server instance. You can download from one of the following instances:

• A Compute Engine Windows instance
• A Compute Engine Linux instance
• Another type of instance that runs SQL Server

Then, transfer the XEvent output files from the bucket to a location accessible to that instance, such as its local disk. Next, to return information from the XEvent output file, run the sys.fn_xe_file_target_read_file function with an account that has the VIEW SERVER STATE permission on the server. For more information about permissions, see Permissions.

Monitoring metrics

You can monitor the following xevent-related operations as part of the Cloud SQL metrics, available through Metrics Explorer:

• database/sqlserver/xevents_upload_count: indicates the total of the successful and failed xevent file upload attempts. Check this metric to monitor if your uploads were successful.
• database/sqlserver/xevents_size.: indicates how much disk space is used by xevent files stored on the instance, in bytes.

Monitor XEvent sessions

You can monitor XEvent session-related activities through the xevents.log file for the following operations:

• The XEvent output files removed from the disk, which were not uploaded to Cloud Storage and were occupying disk space higher than the value associated with cloud sql xe output total disk size (mb).
• The XEvent output files removed from the disk, which were not uploaded to Cloud Storage and are older than the value associated with cloud sql xe file retention.

Considerations for read replicas

This section contains information about XEvent sessions for new and existing read replicas.

For new read replicas

• All XEvent session configuration parameters that are passed through database flags on the original instance are available on the newly created read replica.
• All XEvent sessions present on the original instance when the read replicas are created are available on the read replica. Note that XEvent sessions on the read replica need to be started manually, unless they're configured to start automatically. For more information, see How to create XEvent sessions.
• If you clone a primary instance, then any database flags that appear on the primary instance are propagated to the cloned instance. Also, you must grant permissions to the service account of the cloned instance to access the Cloud Storage bucket again.

For existing read replicas

• XEvent session configuration parameters passed through database flags to the original instance aren't propagated to read replicas.
• XEvent sessions created on the original instance aren't propagated to read replicas.

Note: If an XEvent session is created on a read replica, but the replica is recreated after the session is created, then the session no longer appears on the replica.