TencentDB for SQL Server allows you to record blocking and deadlock events and download corresponding log files. This helps you locate and optimize SQL statements that cause blocking and deadlock.
This document describes how to query and download blocking and deadlock events.
Note:
Blocking and deadlock events are supported only in 2012, 2014, 2016, 2017, and 2019 Enterprise Editions, which are not available in 2008 R2 Enterprise Edition.
The log is stored in the Beijing time zone (UTC+8) by default. If the default time zone is modified, it will be stored in the time zone of the instance server, but its storage time will still be in Beijing time in the console.
Prerequisites
To query and download blocking and deadlock events, you need to enable the collection of blocking events and deadlock events first.
Background
To ensure data consistency in a database, a resource is not released while it is still being modified, so that it can’t be accessed or modified by other concurrent sessions. However, it may be occupied for an extended period of time if there are slow SQL queries or other exceptions, resulting in a blocking event where it can't be accessed by other sessions.
On the other hand, if multiple transactions compete for resources, for example, when two transactions hold resources without releasing them and attempt to access the resource held by each other, a deadlock event will occur.
To address these issues, TencentDB for SQL Server provides a feature to record the blocking and deadlock events in the console. Once this feature is enabled, you can quickly identify blocking and deadlock events occurring in the database, helping you locate and optimize the execution SQL statements that caused the problem.
Use Limits
By default, the collection of blocking and deadlock events is disabled, but it can be enabled manually. Once enabled, such events will be logged in the log files, along with the details of the SQL execution that caused the blocking and deadlock.
After the collection of blocking and deadlock events is enabled, the collection threshold is set to 1000 ms (or 1s) by default, which can be set to anywhere between 1000 and 86,400,000 ms. A SQL running beyond the threshold will be logged as a blocking SQL and a deadlock SQL.
By default, blocking and deadlock events are collected every 5 minutes. This means that any SQL query that exceeds 1s (the default threshold) will be recorded within a 5-minute interval.
Blocking and deadlock events are retained for 7 days by default and automatically deleted upon expiration.
Enabling the collection of blocking and deadlock events
Note:
The collection of blocking events and deadlock events can only be enabled or disabled simultaneously. It is currently not possible to configure the collection settings separately for each event type.
2. Select the region at the top, find the target instance, and click the instance ID or Manage in the Operation column to enter the instance management page.
3. On the Instance Management page, select the Operation log tab.
4. Click Operation Log Settings.
5. In the pop-up window, complete the following configuration and click Save.
Parameter
Description
Collection
Toggle on this fswitch.
Collection Threshold
Set a collection threshold to anywhere between 1000 and 86,400,000 ms.
Collection Frequency
It is every 5 minutes by default and can't be modified.
Retention Period
It is 7 days by default. The events will be automatically deleted upon expiration.
Option 2. Enable by setting the parameter blocked process threshold
1. Log in to the TencentDB for SQL Server console. In the instance list, click an instance ID to access the instance management page.
2. On the Instance Management page, select Parameter Configuration > Parameter Settings tab. Then, find the parameter blocked process threshold, and click
to set a non-zero value on its Current Value column.
Note:
The default value for the parameter blocked process threshold is 0, which means that blocking events and deadlock events are not collected.
The blocked process threshold parameter can be set to a value between 0 and 86,400s.
If the current running value of the parameter blocked process threshold is not 0, it indicates that blocking and deadlock events are being collected. The collection switch is toggled on in the [Operation Log Settings] correspondingly.
Querying and downloading blocking and deadlock events
1. On the Instance Management page, select Operation Log > Blocking Event, or Operation Log > Deadlock Event to view the corresponding event lists.
You can view the following fields: File Name, Start Time of File Generation, End Time of File Generation, File Size, and Operation (Download).
You can search for slow logs generated in the last 5/15/30 minutes, last 1/3/24 hours, today, yesterday, last 3/7/30 days, or a custom time range.
2. Click Download in the Operation column to download the blocking events or deadlock events file.
Yes
No
Was this page helpful?