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 blockingSET [FirstName] = 'Chris', [LastName] = 'Skorlinski'WHERE [BusinessEntityID] = 1WAITFOR DELAY '00:04:00'
--Query 2: Being BlockedUPDATE [Person].[Person]SET [FirstName] = 'Chris', [LastName] = 'Skorlinski'WHERE [BusinessEntityID] = 1
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
Sorry, those don't work, you can query dm_exec_session and dm_exec_requests to look at blocking details.--Who's in my databaseSELECT host_name, login_name, login_time,program_name,status,cpu_time, logical_readsFROM sys.dm_exec_sessions--Show work being performed, blocking, waits, open_transactions, query_hash, query_plan_hashSELECT 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_hashFROM sys.dm_exec_requestsWHERE session_id <> @@spid
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 statementSELECT * 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'%' +replace(left(statement_start_offset/2,else statement_end_offset/2 - statement_start_offset/2end),3000), '[','[') + '%'
In my next posting I'll talk about WHY they may be blocking and WHAT are your next steps to prevent blocking.