已封鎖的處理序臨界值伺服器組態選項blocked process threshold Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

使用 blocked process threshold 選項,以秒為單位來指定產生已封鎖處理序報表的臨界值。Use the blocked process threshold option to specify the threshold, in seconds, at which blocked process reports are generated. 此臨界值可設定為 0 到 86,400 的值。The threshold can be set from 0 to 86,400. 預設不會針對已封鎖的處理序產生任何報告。By default, no blocked process reports are produced. 對於系統工作或在等待不產生可偵測死結的資源的工作,並不會產生此事件。This event is not generated for system tasks or for tasks that are waiting on resources that do not generate detectable deadlocks.

您可以定義在產生此事件時要執行的 警示You can define an alert to be executed when this event is generated. 例如,您可以選擇呼叫管理員,以採取適當的動作來處理此封鎖狀況。So for example, you can choose to page the administrator to take appropriate action to handle the blocking situation.

封鎖的處理序臨界值使用死結監視背景執行緒,瀏覽等待時間超過設定的臨界值或是臨界值的好幾倍之工作清單。Blocked process threshold uses the deadlock monitor background thread to walk through the list of tasks waiting for a time greater than or multiples of the configured threshold. 每隔一段報告時間間隔就會為每個已封鎖的工作產生一次此事件。The event is generated once per reporting interval for each of the blocked tasks.

封鎖的處理序報表是以最大速率來執行。The blocked process report is done on a best effort basis. 不保證即時或甚至接近即時的報告。There is no guarantee of any real-time or even close to real-time reporting.

設定立即生效,伺服器不必停止再重新啟動。The setting takes effect immediately without a server stop and restart.

範例Examples

下例範例將 blocked process threshold 設為 20 秒,為每一個封鎖的工作產生封鎖處理序報表。The following example sets the blocked process threshold to 20 seconds, generating a blocked process report for each task that is blocked.

sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 20 ;  
GO  
RECONFIGURE ;  
GO  

另請參閱See Also

sp_trace_setevent (Transact-SQL) sp_trace_setevent (Transact-SQL)
Blocked Process Report 事件類別Blocked Process Report Event Class