Resolving Blocking in SQL Server

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | May 26, 2016

Resolving Blocking in SQL Server


Users who access a resource at the same time are said to be accessing the resource concurrently. Concurrent data access requires mechanisms to prevent adverse effects when multiple users try to modify resources that are actively being used by other users.

Users modifying data can affect other users who are reading or modifying the same data at the very same time often leading to blocking or deadlocking. Users who access a resource at the same time are said to be accessing the resource concurrently. Concurrent data access requires mechanisms to prevent adverse effects when multiple users try to modify resources that are actively being used by other users. Note that different levels of concurrency control have different side effects. With the help of this blog, readers will learn about different types of blocking in SQL and best approach to resolve and prevent the same.

Prevent SQL Blocking
Common causes of blocking

  • Long running queries or transactions
  • Inappropriate transactions or transaction-isolation level
  • Losing track of transaction-nesting level
  • Not processing result quickly or completely

Undetected distributed deadlock

1) Blocking Caused by long-running queries

Once we have identified the blocking session, the row associated with that session or request in sys.dm_exec_sessions or sys.dm_exec_requests will show the column values as shown below:

Columns

Value

Status

Runnable, The requests is running and temporarily scheduled out.

Wait_type

If the request is blocked, this column returns the type of wait.

Additionally, we can monitor the blocking session through SQL Profiler by using the following profiler events:

  • SQL : StmtCompleted (SQL : BatchCpmpleted)
  • SP:StmtCompleted (RPC:Completed)

The duration column in these events will have a relatively high value.

Other supporting information includes high values for sys.dm_exec_requests columns, including the cpu_time, writes, logical_reads and granted_query_memory columns. A high value for any of these columns is an indication that the request has been utilizing a large amount of resources to process queries. 

To resolve blocking caused by Long-running queries, we can:

  • Improve Performance of the long-running queries.
  • Run the queries on other SQL Instance or run them at a different time to minimize their impact on concurrency.
  • Consider to implement row versioning. 

2) Blocking caused by inappropriate transactions or transaction-isolation level

Once we have identified the blocking session and request, the rows associated with them in sys.dm_exec_sessions or sys.dm_exec_requests will show the below column values:

Columns

Value

Status

Runnable, The requests is running and temporarily scheduled out.

Wait_type

If the request is blocked, this column returns the type of wait.

Open_transaction_count

This column specifies the number of transactions that open for this request. If this greater than 0, it indicates that the process is in transaction.

To resolve Blocking caused by inappropriate transactions or transaction-isolation level

  • Evaluate if you truly need high isolation level.
  • Evaluate individual query that holds large number of locks. You may be able to tune the query by adding indexes to reduce the number of locks required.
Also note that incorrectly managing transactions that accumulate large number of locks (even with read committed isolation level, not just SERIALIZABLE or REPEATABLE READS) can cause exact same behavior.

3) Blocking Caused by losing track of transaction-nesting level

Once we identify the blocking session and request, the rows associated with them in sys.dm_exec_sessions or sys.dm_exec_requests will show the below column values:

Columns

Value

Status

Sleeping, There is no work to be done.

Wait_type

NULL.

Open_transaction_count

Number of transactions that are open for this request will be 0.

Additionally, you can Monitor the blocking session and request through the SQL server Profiler. Monitor by using the following profiler events:

  • Attention event
  • Exception event
  • Or both

An orphaned transaction is usually caused by query cancellation or timeout without a rollback. Timeout and most errors do not roll back any active transactions. It is the responsibility of the application to explicitly request a rollback in the events of an error or timeout.

4) Blocking caused by undetected distributed deadlock

Once we have identified the blocking session and request, the rows associated with them in sys.dm_exec_sessions or sys.dm_exec_requests will show the below column values:

Columns

Value

Status

Sleeping, There is no work to be done.

Wait_type

NULL.

Open_transaction_count

This column specifies the number of transactions that are open for this request. If this is greater than 0, it indicates that the process is in transaction.

Undetected deadlocks are difficult to find and troubleshoot. Application developers must be aware of them and must code applications to handle such cases appropriately.

5) Blocking caused by slow or incomplete result processing.

Once we have identified the blocking SPID, the row associated with that SPID in sys.sysprocesses will show the below column values:

Columns

Value

Status

Runnable, The request is running and temporarily scheduled out.

Wait_type

ASYNC_NETWORK_IO (Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server).

Open_transaction_count

This column specifies the number of transactions that are open for this request. If this is greater than 0, it indicates that the process is in transaction.


  • The runnable status shows that the query is still running
  • The wait_type = ASYNC_NETWORK_IO indicates that the query is waiting on the network resource.
  • open_transaction_count >=0 indicates that the process may or may not be in transaction.

Compile Blocking

            Blocking Process:

                        Typical:

     sys.dm_exec_requests : wait_resource = ‘[COMPILE]’

                              Sys.dm_tran_locks: resource_type = ‘OBJECT’, resource_subtype = ‘COMPILE’

                        Likely

                            SP: Recompile Event

                        Possible

                           High CPU Utilization

Resolution:

  1. Check to see whether two part name (schema-qualified) is used on stored procedure.
  2. Troubleshoot stored procedure recompile.

Check for dbo and then schema is owner not specified.

Compile blocking appears as blocked requests waiting on COMPILE resource in sys.dm_exec_requests.wait_resource and sys.dm_tran_locks. In addition, there may be a large number of SQL profiler SP: Recompile events in the trace. Compile blocking will usually cause rolling blocking.

In SQL server, only one copy of a stored procedure plan is generally cached at a time and the stored procedure compilation attempts are serialized. If a commonly used stored procedure must be frequently recompiled, it is possible for this stored procedure to cause blocking as one request waits on another to finish compilation.

In this situation we can do one of the following:

  • Reduce recompile frequency

Owner-qualify stored procedures. Compile locks can be held for longer than is necessary if the name of the procedure is not fully qualified with database and owner name, and the user is not the owner of procedure.

Conclusion:
There are multiple types of blocking in SQL which have been shared above and with the help of mentioned steps one can prevent blocking in SQL. This will ensure that the query can be completed on time and performance improves. For any queries on the topic click below. You can also leave a comment.

Ask Manish