Troubleshooting Blocking in SQL Database–the why

Article
03/28/2013

Chris Skorlinski
Microsoft SQL Server Escalation Services

In my last blog posting I explored SQL DMVs used to find who is blocking queries from running. By Who, I mean other queries. We calls these "head blockers". Our goal is to get those head blockers finished as quickly as possible to release resources for other queries.

For the "the Why" investigation we'll look at finding out what resources the head blocker is consuming, using, or waiting. We'll use some of the same AdventureWorks2012 sample database from previous blog posting.

Most often head blocker research falls into a few common categories.

Head blocker is long running query\stored procedure
Head blocker running transactions within stored procedures and NOT releasing locks.
Head blocker waiting on system resources like CPU, Memory, or Disk

Long Running

Queries running a long time will hold resources preventing other queries from selecting, updating, deleting, basically getting their work done. Examine the blocking query's "logical reads". ( Troubleshooting Blocking in SQL Database - the who ). Is head blocking reading more then 10,000 pages? more then 100,000? more then 1,000,000? As the number of pages processed by the query increases, more time will be spent retrieving rows from disk, reading through memory, consuming CPU, etc. Fast queries release resources quickly, less time to block.

Compare Logical Reads of head blocker to other queries running. Are they all about the same, all equally small, all equally bad? If head blocker has high reads, you'll want to examine the query plan and\or code logic to drop those logical reads. I'll give more examples in future blog postings on query plans. Searching BING for SQL MVP Query Plans you'll find great posts from SQL MVP community like Grant Fritchey, Kalen Delaney, Paul White and others on analyzing query plans

Waiting

Okay, so the head blocker is NOT running through 1 million pages. Why is it holding pages? Like on premises SQL Server, running SQL Database we need to examine locks. Run the blocking example in my previous post, scroll through output and look at wait_type column. This begins investigating into WHY query is taking a long time. It is waiting on resources for 216 seconds!

 --Show Blocked Queries

SELECT * FROM sys.dm_exec_requests

WHERE session_id > 50 and session_id <> @@spid

ORDER BY blocking_session_id -- blocking_sessions_id = 0 = not blocked

In this example its waiting on a lock. BING search will show you details on the different wait_types.

Locate the Head Blocker session_id

Next query sys.dm_tran_locks to see all the SQL system resources that are locked by this head blocker. Look through the output to get an understanding of what resources is the head blocker holding. One or many tables? One or many pages?

 --Show LOCKS for blocking and blocked

SELECT request_session_id, request_status,resource_type, resource_description,

   resource_associated_entity_id, request_mode, request_status

FROM sys.dm_tran_locks 

WHERE request_owner_type = N'TRANSACTION'

    AND (request_session_id = 317 -- Head Blocker

   OR request_session_id = 878)  -- Being Blocked

ORDER BY request_session_id

GO

This query shows both resources GRANT and WAIT. How many rows are held by the GRANT?

Using the Partition_ID we can find out table name for the WAIT resource.

 -- resource_associated_entity_id

SELECT object_name(object_id) 'Table Name',

   object_id, index_id, row_count, in_row_data_page_count, lob_reserved_page_count

    FROM sys.dm_db_partition_stats

    WHERE partition_id=72057597391601664

Table Name object_id index_id row_count in_row_data_page_count lob_reserved_page_count

----------------- ----------- ----------- ------------ ---------------------- -----------------------

Person 997578592 1 19972 2747 0

Results here show waiting on the Person table, index #1. Now compare the query or Stored Procedure code for both the head blocker and the blocked to see where they overlap on the Person table. Maybe you can tune one query to run faster reducing the blocking time, or change the programming logic. to query then release the table.

Release Locks

Stored procedures coded to release locks reduce the opportunity for blocking. I've seen many procedures where code written to hold the table locks longer then needed. For example:

Begin Transaction

SELECT a table 1

UPDATE a table 1

SELECT table 2

UPDATE table 2

Commit Transaction

If the 2nd SELECT runs longer then expected, the UPDATE LOCKS on the first table will not be released. Another user will be blocked waiting for Table 1 while problem is the long running SELECT on Table 2. Like my dog Roscoe, you'll be chasing your tail all day long.

Using No Locks not always the answer. Try coding stored procedures to be as lean as possible. Review the Isolation Levels, load data into TempDB or table variables. When developing, add WAITFOR DELAY '00:05:00' as last line of the SP then examine sys.dm_tran_locks to learn what is locked when the stored procedure ends. Any thing unexpected in the list?

Waiting on System Resources

If the head blocker is waiting on system resources you may need to scale out your SQL Database deployment, but first look at your overall work load. Do you have other queries consuming large resources? Who is the highest logical reader? One or two queries with high logical reads will load up buffer pool and tie up worker threads needed by other queries. A query updating a million rows which need to be committed on your failover secondary servers could be causing SE_REPL waits on other queries.

Start by polling sys.dm_exec_sessions and sys.dm_exec_requests over time looking at physical writes and logical reads. Here are couple queries to help.

 -- Stuart Ozer, Connor Cunningham, Chris Skorlinski (revised)

-- View Currently Executing (dm_exec_requests)

--        wait_type, wait_resource, statement_text, statement_plan

--        execute user-defined statement_level_query_plan in the current DB.

select pln.*, req.* from sys.dm_exec_requests as req

CROSS APPLY statement_level_query_plan(plan_handle) as pln

where session_id <> @@spid AND statement_text like

'%' +

replace(

left(

    substring((select text from sys.dm_exec_sql_text(sql_handle)),

            statement_start_offset/2,

            1+      case when statement_end_offset = -1

                    then LEN((select text from sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2

                    else statement_end_offset/2 - statement_start_offset/2

                    end)

        ,3000)

, '[','[[]') + '%'

 --Written by: Jonathan Kehayias SQLskills.com MVP

--Modified by: Chris Skorlinski, Microsoft SQL Server Escalation Services

select dm_tran_locks.request_status,

       dm_tran_locks.request_session_id,

            dm_tran_locks.resource_database_id,

            db_name(dm_tran_locks.resource_database_id) as dbname,

            CASE

                  WHEN resource_type = 'object'

                        THEN object_name(dm_tran_locks.resource_associated_entity_id)

                  ELSE object_name(partitions.object_id)

            END as ObjectName,

            partitions.index_id,

            indexes.name as index_name,

            dm_tran_locks.resource_type,

            dm_tran_locks.resource_description,

            dm_tran_locks.resource_associated_entity_id,

            dm_tran_locks.request_mode

from sys.dm_tran_locks

left join sys.dm_db_partition_stats partitions on partitions.partition_id = dm_tran_locks.resource_associated_entity_id

join sys.indexes on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_id

where resource_associated_entity_id > 0

  and resource_database_id = db_id()

order by request_session_id, resource_associated_entity_id

You'll find more details on these in My Links blog posting.

Comments

Anonymous
May 20, 2014
we have raised a great site for SQL, Oracle, and other databases troubleshooting online, including complicated correlations, strongly recommended xnetbrain.com

Share via