• Management Pack:  SQL Server 2005
  • MP Version:  6.1
  • Released:  8/5/2010
  • Publisher:  Microsoft

Blocking Sessions Monitor

  • ID:  Microsoft.SQLServer.2005.DBEngine.BlockedSPIDsMonitor
  • Description:  Monitors blocked sessions for a SQL instance.
  • Target:  SQL Server 2005 DB Engine
  • Enabled:  No

Operational States

Name State Description
Sessions Blocked Error  
Sessions Not Blocked Success  

Overridable Parameters

Parameter Name Default Value Description Override
Interval (sec) 300  
Synchronization Time    
Wait Time (min) 1  
Number of blocked sessions 1  
Timeout (sec) 300  

Alert Details

Monitor State Message Priority Severity Auto Resolution
Sessions Blocked (Error) Blocked session(s) High Critical Yes

Run As Profiles

Name
Default

Monitor Knowledgebase

Summary

The Blocking Sessions Monitor detects if a blocking situation exists.

Blocking occurs when one session holds a lock on a specific resource and a second session attempts to acquire a conflicting lock type on the same resource. Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. However, too much blocking can cause performance issues.

Causes

When locking and blocking increase to the point where there is a considerable impact on system performance, it is usually due to one of the following reasons:

  • A session holds locks on a set of resources and never releases them. Usually caused by:

    • Cancelled queries that are not rolled back

    • Orphaned transactions

  • A session (identified by a session_id or “SPID”) holds locks on a set of resources for an extended period of time before releasing them. Usually caused by:

    • Long-running transactions

    • Lack of appropriate indexes

    • Inappropriate use of locking hints

    • Other issues related to poor application design

Resolutions

  • Kill the session at the head of the blocking chain

  • Shorten transaction times

  • Create proper indexes

  • Use locking hints. See SQL Server Books Online

  • Use row versioning-based Isolation Levels

  • Configure SQL Server settings (memory settings, lock timeouts, etc.)

  • Change the thresholds on the monitor for this specific database or all databases

Alternatively if blocked sessions are not a concern for the database:

  • Disable the monitor for this specific database or all databases

See also: “INF: Understanding and resolving SQL Server blocking problems

External References
This monitor does not contain any external references.

See Also for SQL Server 2005 Management Pack


Downloads for SQL Server 2005 Management Pack

AZURE OPTIMIZATION ASSESSMENT GET STARTED
MIGRATION TO AZURE GET STARTED
SYSTEM CENTER MIGRATION TO AZURE GET STARTED
MIGRATION TO AZURE FOR SQL AND WINDOWS 2008 GET STARTED