This page describes how to use the Google Cloud Platform Console to view statistics for the SQL queries on a Cloud Spanner database that had the highest total CPU usage during a recent period of time.
If you want to retrieve performance data for those queries using SQL, see Query statistics tables.
Understanding what data Cloud Spanner captures
Statistics that the GCP Console displays are an aggregate for all executions of a query over a given period of time, not information about individual executions of the query. Cloud Spanner might not capture every query execution, and the less frequently a query is run, the higher the probability that Cloud Spanner will not capture metrics for it.
Query statistics can include SQL queries on any table in your database. As a result, if you query the information schema tables, or even the query statistics tables, these queries can appear in the query statistics.
For more information on the underlying tables that the GCP Console uses to display information in the Query Stats tab, see Query statistics tables.
To get details about the performance of individual queries, see Understand how Cloud Spanner executes queries.
Using query parameters to improve aggregation
Using query parameters is a best practice. It both improves query performance and improves the accuracy of query statistics tracking. Cloud Spanner groups the statistics by the text of the SQL query. If a query uses query parameters, all executions are grouped into one row. If the query uses string literals, the statistics are only grouped if the full query text is identical; when any text differs, each query appears as a separate row.
Viewing query statistics in the GCP Console
The GCP Console displays summary statistics for queries on a database that had the highest total CPU usage. The statistics include the total CPU usage, execution count, average latency, and average CPU seconds. From the Query Stats tab, you can select a time period for the statistics and investigate individual queries.
To display the recent queries that used the most CPU, follow these steps:
Go to the Cloud Spanner Instances page.
Click the name of the instance that contains your database.
In the Overview tab, click the name of your database. The Database details page appears.
Click Query stats.
The list shows queries that had the highest total CPU usage for the complete one-minute interval that is the most recent. Below the list, the GCP Console displays the time at which the interval ended.
Click 10 min, 1 hour, or 24 hours to change the time period for the query statistics.
From the Query stats tab, you can view additional metrics for a specific query:
Click the query for which you want to see additional metrics.
The Details panel displays the query metrics.
In the Info panel, click Open query.
The GCP Console and Info panel display several query statistics:
|Total CPU usage||Total CPU time used for all executions of the query.|
|Execution count||Number of times Cloud Spanner saw the query during the interval of time.|
|Average latency||Average length of time, in seconds, for each query execution within the database. This average excludes the encoding and transmission time for the result set as well as overhead.|
|Average CPU time||Average number of seconds of CPU time Cloud Spanner spent on all operations to execute the query.|
|Average rows scanned||Average number of rows that the query scanned, excluding deleted values.|
|Average rows returned||Average number of rows that the query returned.|
|Average bytes scanned||Average number of bytes that the query scanned.|
|Average bytes returned||Average number of data bytes returned from the queries, excluding transmission encoding overhead.|
Learn how to retrieve query statistics using SQL queries.