Viewing and Downloading Slow Query Logs

Scenarios

Slow query logs record statements that exceed the long_query_time value (1 second by default). You can view log details to identify statements that are executing slowly and optimize the statements. You can also download slow query logs for service analysis.

You are advised to use the Slow Query Logs function of DBA Assistant to view slow queries over time, slow query log details, and template statistics. Using this function, you can optimize SQL statements and export slow query logs. For details, see Slow Query Log.

Parameter Description

Viewing Slow Query Logs

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page and choose Databases > Relational Database Service.
4. On the Instances page, click the target DB instance.
5. In the navigation pane on the left, choose Logs. On the Slow Query Logs page, click to enable the slow query log function.
6. The generated slow query logs are displayed.
   Figure 1 Slow query logs
   
   

   Enabling slow query log slightly affects DB instance performance.

7. Connect to the DB instance through the SQL Server client. For details, see Connecting to a DB Instance Through a Public Network.
8. After the DB instance is connected, run the following command to view slow query log details:

   select * from ::fn_trace_gettable('D:\SQLTrace\audit\XXX', default)

   

   XXX indicates the name of the slow query log recorded in 6.

   Example:

   select * from ::fn_trace_gettable('D:\SQLTrace\audit\SQLTrace.trc', default)

   The result is shown in Figure 2.

   Figure 2 Slow query log details
   

Downloading a Log

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page and choose Databases > Relational Database Service.
4. On the Instances page, click the target DB instance.
5. In the navigation pane on the left, choose Logs. On the Slow Query Logs page, click to enable the slow query log function.
   

   Enabling slow query log slightly affects DB instance performance.

6. Locate a log to be downloaded and click Download in the Operation column.
   1. The system automatically loads the downloading preparation tasks. The loading duration is determined by the log file size and network environment.
      • When the log is being prepared for download, the log status is Preparing.
      • When the log is ready for download, the log status is Preparation completed.
      • If the preparation for download fails, the log status is Abnormal.
   2. You can determine how to download a log file depending on the file size.
      • Only logs no more than 40 MB can be downloaded directly from this page. The time range is calculated from the time you download the logs back to the time when the accumulated file size reaches 40 MB.
      • It is impossible to generate a log file much larger than 40 MB, like 100 MB or 200 MB. If a log file that is a little larger than 40 MB is required, use OBS Browser+ to download it by referring to Method 1: Using OBS Browser+.
   3. In the displayed dialog box, click OK to download the log whose status is Preparation completed. If you click Cancel, the system does not download the log.

      The download link is valid for 5 minutes. After the download link expires, a message is displayed indicating that the download link has expired. You can close the window and repeat the procedure 6 to try to download a log again.

      

      After downloading slow query logs to a local PC, you can use SSMS to connect to the local database and run the following SQL statement to view the slow query log details:

      select * from ::fn_trace_gettable('XXX', default)

      In the preceding command, XXX indicates the local path for storing slow query logs.