Troubleshooting Blocking in SQL Database - the who

Chris Skorlinski
Microsoft SQL Server Escalation Services

In this blog I’ll walk through troubleshooting blocking in SQL Database.  As you’ll see, we’ll many of the same techniques used for on-premises SQL Servers with one exception, will not have use of SQL Server Profiler to capture server activity.  While Profiler is often used, started with SQL Server 2005, the SQL DMVs provide the details about blocking.

For blocking, that is one user is holding resources need by one or more users, first step is to identify the “who”, that is head blocker and see what resources they hold.  Next is understanding “why” they are holding resources followed by “what” option do we have to solve this problem.  I’ll cover the “who” in this posting.

Start by making 2 connection to SQL Database. Execute the 2 scripts in each query.  These examples are using the AdventureWorks2012 sample we installed earlier.

 

 --Query 1: Start UPDATE blocking

BEGIN TRANSACTION

   UPDATE [Person].[Person] WITH (holdlock)

       SET [FirstName] = 'Chris', [LastName] = 'Skorlinski'

      WHERE [BusinessEntityID] = 1

    WAITFOR DELAY '00:04:00'

ROLLBACK TRANSACTION

 --Query 2: Being Blocked

BEGIN TRANSACTION

   UPDATE [Person].[Person]

       SET [FirstName] = 'Chris', [LastName] = 'Skorlinski'

      WHERE [BusinessEntityID] = 1

ROLLBACK TRANSACTION

In this example Query 1 performs update using a "holdlock", okay not most elegant, but it works, then goes into a 4 minute WAIT before rolling back the transactions.  If you need more time while exploring the DMVs, just re-run these queries.

Now, in a 3rd query window connect again to same AdvenstureWorks2012 and executes queries below.

 SELECT * 

FROM sys.sysprocesses

SP_WHOSP_WHO2 

Sorry, those don't work, you can query dm_exec_session and dm_exec_requests to look at blocking details.

--Who's in my database

SELECT host_name, login_name, login_time,program_name,status,cpu_time, logical_reads

FROM sys.dm_exec_sessions

WHERE status <> 'sleeping' AND session_id <> @@spid

--Show work being performed, blocking, waits, open_transactions, query_hash, query_plan_hash

SELECT session_id,start_time,status,logical_reads,cpu_time,total_elapsed_time,

    command,blocking_session_id,wait_type,wait_time,last_wait_type,wait_resource,connection_id, 

   sql_handle,plan_handle,query_hash,query_plan_hash

FROM sys.dm_exec_requests

WHERE session_id <> @@spid

ORDER BY blocking_session_id -- blocking_sessions_id = 0 = not blocked

From dm_exec_requests look at the blocking_session_id column.  Values = 0 indicate no blocking and often are the "head blockers".  For my example, you'll see query 1, "running" with command "WAITFOR", and wait_type "WAITFOR", normally you'll find more normal SQL commands such as an UPDATE, DELETE, INSERT.  For our WAITER, you can see "suspended" with wait_type LCK_M_U, an update lock request.  These guys aren't going anywhere real fast.

When troubleshooting your blocking, look closer at the logical_reads column, high values here (10K+) often indicate a busy query consuming too many resources and focus for tuning opportunities.  I'll cover this more in another post.

Identifying WHO also allows us to retrieve the sql_handle and plan_handle from dm_exec_requests.  We can use built-in system functions to retrieve SQL statement and query plans for queries shown.

 --Retrive XML Query Plan for the UPDATE statement

SELECT * FROM sys.dm_exec_query_plan(0x06001F00C5B3ED0EE0FE084D0400000001000000000000000000000000000000000000000000000000000000)

Results is the query_plan you can click-open in SQL Server Management Studio.  Again, more on query plans later.

 

If you search the web, you'll find more example of using dm_exec_requests.  Try some of these queries against our SQL Database AdventureWorks2012 example.  Here is one I pull then modified from Stuart Ozer and Connor Cunningham (link in my "favorite links" posting I did earlier.

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

-- View Currently Executing (dm_exec_requests)

--        wait_type, wait_resource, statement_text, statement_plan

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

CROSS APPLY statement_level_query_plan(plan_handle) as pln

where 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)

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

 

In my next posting I'll talk about WHY they may be blocking and WHAT are your next steps to prevent blocking.