Summary for Accidental DBA Book / Chapter 6: Blocking

Chapter 6: Blocking

• Some of the more commonly seen lock modes are:
• Shared: taken by queries that are reading from a table or index.
• Update: is taken as part of an update operation.
• Exclusive: is taken for any data modification (insert, update, delete). For an update, SQL first takes an update lock and then converts it to an exclusive lock to perform the actual update.
• Intent locks: is used to reduce the work SQL must do to tell if a lock can be granted. If a row lock is needed by a query, SQL first takes the appropriate intent lock at the table level, then an appropriate intent lock at the page level and then it will take the necessary row lock. Each lock mode has its associated Intent lock (shown as I). Hence you will see IS (Intent Shared) locks, IU (Intent Update) locks and IX (Intent Exclusive) locks … P195.
• SQL Server can lock a number of different types of resource, the most obvious being tables (OBJECT locks), pages (PAGE locks), and rows (RID or KEY locks). SQL Server automatically chooses locks of the highest possible granularity, suitable for the given workload. However, if too many individual locks are being held on an index or heap, or if forced to do so due to memory pressure, SQL Server may use lock escalation to reduce the total number of locks being held, while this will result in lower overhead on SQL Server, the cost will be lower concurrency … P196.
• In some cases, lock escalation can cause blocking because SQL has escalated locks, and so locked the entire table, with the result that concurrent access to that table is restricted. To identify lock escalations, SQL Trace or Profiler can be used with the Lock:Escalation event, in conjunction with the SP:Started or T-SQL:StmtStarted and SP:Completed or T-SQL:StmtCompleted events, to try to correlate the lock escalation with currently executing statements and procedures … P197.
• Latches can be thought of as light-weight, short-lived locks. Where locks are used to protect the logical and transactional consistency of rows, latches are typically used to protect the physical consistency of pages or memory structures. There are three types of latches that can be encountered in the SQL engine:
• Latch – used to protect various memory structures within SQL Server.
• Page Latch – used when SQL is modifying the structure of the page. So, when the page header gets modified, or a row is added to a page, SQL would take a Page Latch before starting and release it once complete.
• Page I/O Latch – used when pages are moving between disk and memory. So a Page I/O Latch would be taken before a page is fetched from disk, and released once the page is in the cache.
  Latches (usually Page Latch or Page I/O Latch) can cause blocking in much the same way as locks … P203.

Monitoring Blocking

• The wait type for locks has the form LCK_M_<lock type>. So, a wait to acquire a shared lock appears as LCK_M_S, exclusive lock will appear as LCK_M_X, and a wait for an intent shared lock will appear as LCK_M_IS and so on … P204. Latches blocking appears like Latch, Page Latch or Page I/O Latch wait rather than a LCK wait.
• Use the sysprocesses system view to find blocking as well as blocked processes, as shown in Listing 6.1. The Blocked column shows the SPID that is causing the blocking, it will have a value of 0 for any session that is not blocked. System procedures sp_who and sp_who2 are simply views onto the sysprocesses system view and can be used in much the same way. Once we have the SPIDs of the blocking and blocked sessions, we can take a look at what they are running using DBCC INPUTBUFFER([SPID number]), which will return the first 4,000 characters of the batch that each session is running. If we want to know what locks the process in blocked column that are preventing the process in SPID column from getting the shared read lock that it wants, we can query the syslockinfo system table, or use the sp_lock system stored procedure. The latter is a lot less cryptic and far easier to understand. Combining the value of waitresource column in sysprocesses with the result of running EXEC sp_lock [SPID of the blocking process] we can know the type of blocking … P205-207. 
• DBCC SQLPERF(waitstats) command. It provides the total waiting tasks and total wait time since the instance was started or since the wait stats were cleared. While this command cannot help diagnose individual blocking problems, it can be used to get an overall picture of the most common waits in the system. Above SQL Server 2000, Dynamic Management Views (DMVs) offer more in-depth information for troubleshooting the blocking problem than sysprocesses system view, and the DBCC SQLPERF(waitstats) command … P208.
• Using the sys.dm_exec_requests and sys.dm_exec_sessions DMVs in script in Listing 6.4 shows more information than sysprocesses. after finding the blocking process we can get the last command that it ran by querying sys.dm_exec_connections and using the most_recent_sql_handle column as a parameter to sys.dm_exec_sql_text, as shown in Listing 6.5 … P210-211.
• The sys.dm_os_waiting_tasks DMV is primarily used to show all waiting tasks currently active or blocked. In order to take full advantage of this DMV, JOIN on the sys.dm_exec_requests DMV and CROSS APPLY to sys.dm_exec_sql_text, like in listing 6.6. The result of this doesn't give us much more information than the result of the query in listing 6.5 , though it provides more detail on the resource on which blocking is occurring … P212-213.
• Cumulative wait statistics using  sys.dm_os_wait_stats: Any time an executing task is forced to wait for a resource in the engine, the time spent waiting is tracked, accumulatively, in the  sys.dm_os_ wait_stats  DMV. The values provided in this DMV are running totals, accumulated across all sessions since the server was last restarted or the statistics were manually reset. While the information in this DMV cannot be used on its own to diagnose individual blocking problems, it can be used to find out whether locking waits are one of the most common waits in the system. Simply run the query in  Listing 3.2 (Chapter 3)  and look for high incidences of the  LCK_*  wait types ... P215.
• As with the aggregated wait statistics, PerfMon data can be used to indicate that there may be a problem, but it is insufficient on its own to identify the cause of the problem. The main counters of interest  are  Avg  Wait  Time  (ms),  Lock  Waits/sec, and  Number  of  Deadlocks/sec ... P216.

Automated Detection and Notification of Blocking

• SQL Trace is a powerful feature that allows for the creation of event traces within SQL Server, using a set of stored procedures. SQL Trace should be utilized only when there is a need, as it will consume a great deal of resources in order to log all the events … P218.
• The blocked process report is implemented as an event that is fired every time blocking occurs that exceeds in duration the threshold value, configured by the "blocked process threshold" sp_configure option. The default value for the "blocked process threshold" is zero, meaning that the database engine doesn't perform the additional checks of waiting tasks and won't generate the blocked process reports. The blocked process report can be captured in a number of ways. SQL Server Profiler can be used to actively capture the Blocked Process Report event class, or alternatively you can create a server-side SQL Trace script, using the sp_trace_* stored procedures. When the blocked process threshold is met, the trace will log the event in the trace file. The fn_trace_gettable function can be used to read the file into a tabular format in SSMS. For further information on setting up and using the blocked process report event, see http://www.simple-talk.com/content/article.aspx?article=671  or http://bit.ly/qC6zz3 ... P219-222.
• Event notifications offer the ability to capture, in real-time, most DDL events and trace events in SQL Server. Event notifications use Service Broker queues in order to capture log the events that you wish to monitor. When used in conjunction with the BLOCKED_PROCESS_REPORT event and the LOCK_ESCALATION event, this would allow for instances of both events to be logged, asynchronously, as they happen. This asynchronous model means that event logging has a lower impact on overall server performance than when using SQL Trace or Profiler. For examples check P223-225.
• For more information on Extended Events, Jonathan's An XEvent a day series is well worth reading: http://www.sqlskills.com/blogs/jonathan/category/XEvent-a-Day-Series.aspx.
• Causes for blocking:  Bad database design (P229), Inappropriate isolation level (P229), Poorly written queries (P230), Missing indexes (P231), Poor application design (P231), User input within transactions (P231), Reading too much data (P232), Chatty applications (P232), or Outdated hardware (P232). If fixing the previous did not work, there are hints (isolation level hints, lock mode hints, or lock granularity hints … P233-235) and Trace Flags (Trace Flags 1211 and 1224 … P235-236) that influence SQL Server's locking behavior. These should be used only as short-term, temporary measures while more permanent solutions are investigated.

Reference: http://www.amazon.com/Troubleshooting-SQL-Server-Guide-Accidental/dp/1906434786