Querying and Downloading Slow Query Logs
Baixar
Modo Foco
Tamanho da Fonte
The slow query log is used to record queries in SQL Server whose execution time exceeds the specified threshold. Through the slow query log, you can identify which queries have low execution efficiency for optimization purposes. Typically, the SQL Server slow query log is SQL statements for troubleshooting issues and an important feature for checking the current SQL Server performance.
This article introduces you to Querying and Downloading slow query logs via the console.
Note:
The log time defaults to Beijing time. If the default time zone is modified, the storage time of log data will be displayed in the time zone of the instance server, while the console will show Beijing (UTC+8) time.
SQL Server Slow Queries Related Instructions
The collection of slow query logs is enabled by default, and disabling it is not supported.
The threshold for slow query log collection is 1,000 milliseconds (namely, 1 second) by default, which indicates that SQL queries will be logged as slow queries when their execution time exceeds 1,000 milliseconds. Modification of the threshold (1,000–60,000 milliseconds) for slow query log collection is supported.
A file is generated for every 10 MB of slow query log data. The maximum size of a single file is fixed at 10 MB. The time span for generating a single file is not fixed but depends on the speed at which logs are generated.
The retention period for slow query logs defaults to 7 days, and they will be automatically deleted upon expiration.
Slow Query Log File Generation and Download Policy
You can download slow query log files to view log data and troubleshoot issues. Before downloading files, you can learn about the policies for file generation and download from the following descriptions to query and download files more efficiently.
When the slow query log data of an instance accumulates to 10 MB, the system automatically splits the data and generates a new file. The file can be downloaded after its status becomes "Completed".
When the slow query log data of an instance has not reached 10 MB, the status of the corresponding log file is "Writing". If the file already contains log data, you can download it from the corresponding log list in the console. The system will generate a file for download immediately without waiting until it reaches 10 MB.
If the instance does not generate any slow query log data, no log file will be generated and download is not supported.
Slow Log File Field Descriptions
Field Name | Description |
client_app_name | Client Application Name. |
client_hostname | Client Hostname. |
cpu_time | CPU time consumed by Remote Procedure Calls (RPC), unit: microseconds. |
duration | Time required to complete Remote Procedure Calls (RPC), unit: microseconds. |
last_row_count | The number of rows returned by the last executed SQL statement. |
line_number | Line number of the SQL statement in the source file. |
logical_reads | The number of logical reads performed during execution. |
offset | The starting offset of the SQL statement in the source file. |
offset_end | The ending offset of the SQL statement in the source file. |
parameterized | Indicates whether the SQL statement has been parameterized. |
physical_reads | The number of physical reads performed during execution. |
row_count | The number of rows returned. |
spills | The number of pages written to the database by overflow during query execution. |
sql_text | The text content of the SQL statement. |
statement | The SQL statement that is executed. |
username | Username performing the operation. |
writes | The number of writes performed during query execution. |
connection_reset_option | Options for resetting the connection. This section applies only to reset connection RPC. |
client_connection_id | Optional identifier provided by the client during connection. |
data_stream | Binary stream for replay tracing of Remote Procedure Call. |
database_name | Database name. |
object_name | Object name referenced by the remote procedure call. |
output_parameters | Output parameters returned from the remote procedure call. |
query_plan_hash | Query plan hash, which can be used to identify similar query execution plans. You can use the query plan hash to locate queries with similar execution plans and determine their accumulated cost. |
result | Return value of the remote procedure call. |
Operation Steps
Setting the Threshold for Slow Query Log Collection
1. Log in to the SQL Server console.
2. Select the region above, find the instance that needs to be queried or to download slow query logs, and click Instance ID or click Manage in the Operation column to go to the instance management page.
3. On the instance management page, choose Operation Log > Operation Log Settings.
4. Set the Collection Threshold in the pop-up window and click Save.
Note:
The default value for the slow query log collection threshold is 1000 ms. Please adjust it according to actual requirements.
To set the collection thresholds for blocking and deadlock events during this operation, see Query and Download Blocking and Deadlock Events.
Querying and Downloading Slow Query Log
1. Log in to the SQL Server console.
2. Select the region above, find the instance that needs to be queried or to download slow query logs, and click Instance ID or click Manage in the Operation column to go to the instance management page.
3. On the instance management page, choose Operation Log > Slow Query Log to view the slow query log list.
The supported fields include: File Name, Start Time, Last Updated Time, File Size, Operation (Download).
Supports searching slow logs based on time range: last 5 minutes, last 15 minutes, last 30 minutes, last 1 hour, last 3 hours, last 24 hours, today, yesterday, last 3 days, last 7 days, last 30 days, and custom time period.
4. In the Operation column, click Download to download the slow query log file.
Note:
When Download is clicked, the following scenarios may occur.
If the file status is "Completed", after clicking Download, you can copy the private network address and run the command to download it. The command format is: wget -c 'File download address' -O 'Custom filename', or download it locally.
If the file status is "Writing", when the file has no log data, clicking Download will prompt "Log file is being prepared", and the download cannot be performed at this time. When the file has log data, you can copy the private network address and run the following command to download the file: wget -c 'file download address' -O 'custom file name'. Alternatively, you can download the file locally.
Related APIs
API | Description |
The interface (DescribeSlowlogs) is used to obtain slow query log file information. |
Ajuda e Suporte
Esta página foi útil?
Você também pode entrar em contato com a Equipe de vendas ou Enviar um tíquete em caso de ajuda.
comentários