krn045 (1/21/2013)
Hi..We are using SCOM 2007 to monitor SQL Server 2008 instances. I have checked the blocked sessions alert details in SCOM console as well as in OperationsManager database. I was not able to find the head_blocker (or lead blocker spid) details when more than 2 processes are involved in blocking.
Eg: 1. SPID 87 is blocked SPID 85 (Details available, lead blocker is 85)
2. SPID 87 is blocked by SPID 90
SPID 90 is blocked by SPID 85 (Details available for SPID 87 and 90, here SPID 85 is blocker. There are no details available like hostname, logintime, cmd etc for SPID 85)
I am not sure if this is the limitation in this product or something we should configure to capture the information when there are more than 2 spids involved in blocking.
Sometimes the blocking session is not doing anything, and there is therefore not much info on it (that info would be coming from sys.dm_exec_requests).
A good example of this would be a user-initiated session in which the user has opened a web page, and that has started a transaction that won't commit until the page is exited (code defect). In that scenario a SQL statement in the transaction could be blocking others indefinitely, even though there is no work being done.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]