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
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
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
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 blockedSELECT request_session_id, request_status,resource_type, resource_description,resource_associated_entity_id, request_mode, request_statusFROM sys.dm_tran_locksWHERE request_owner_type = N'TRANSACTION'AND (request_session_id = 317 -- Head BlockerOR request_session_id = 878) -- Being Blocked
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_idSELECT object_name(object_id) 'Table Name',object_id, index_id, row_count, in_row_data_page_count, lob_reserved_page_countFROM sys.dm_db_partition_statsWHERE 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.
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:
SELECT a table 1
UPDATE a table 1
SELECT table 2
UPDATE table 2
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.'%' +replace(left(statement_start_offset/2,else statement_end_offset/2 - statement_start_offset/2end),3000), '[','[') + '%'
--Written by: Jonathan Kehayias SQLskills.com MVP--Modified by: Chris Skorlinski, Microsoft SQL Server Escalation Servicesselect 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,WHEN resource_type = 'object'THEN object_name(dm_tran_locks.resource_associated_entity_id)ELSE object_name(partitions.object_id)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_modefrom sys.dm_tran_locksleft join sys.dm_db_partition_stats partitions on partitions.partition_id = dm_tran_locks.resource_associated_entity_idjoin sys.indexes on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_idwhere resource_associated_entity_id > 0and resource_database_id = db_id()
You’ll find more details on these in My Links blog posting.