About the book

This is a sample chapter of the book SQL Server DMVs in Action. It has been published with the exclusive permission of Manning.

Written by: Ian W. stirk
Pages: 375
Publisher: Manning
ISBN: 9781935182733

Introduction

Many SQL queries run fine in isolation; however, in the real world, SQL queries need to interact with other running queries. The queries compete for resources, often leading to temporary blocking. This is to be expected. However, if you notice queries running slowly, it is possible to identify the queries involved and perhaps reschedule them so they don't run concurrently or decode the resource for which the queries are competing, possibly removing the contention. For example, if an index is the cause of contention, it is possible to create another index that could remove this contention, allowing both queries to progress.

The script we use to identify blocked queries is shown in listing 1.

Listing 1: What is blocked?

In the listing, you can see there are four Dynamic Management Views (DMVs) and one Dynamic Management Function (DMF) involved in identifying what queries are being blocked. A brief description of each is shown in table 1.

Table 1: DMVs/DMFs for blocked queries

The joining of the DMVs and DMF provides enough information to determine which SQL queries are causing the blocking and which queries are being blocked across all databases on the server. The DMVs sys.dm_exec_connections, sys.dm_exec_requests and sys.dm_os_waiting_tasks are joined on their common key column session_id (blocking_session_id in the case of sys.dm_exec_requests).

These DMVs have a RIGHT OUTER JOIN to the DMV sys.dm_exec_sessions. That is because the blocking queries are typically running and will be present in the sys.dm_dm_os_waiting_tasks DMV. The blocking query's most_recent_sql_handle is passed to the DMF sys.dm_exec_sql_text to retrieve the text of the blocking SQL query. Similarly, the blocked query's sql_handle is passed to DMF sys.dm_exec_sql_text to retrieve the text of the blocked SQL query.

An example of the type of output for this query is given in figure 1.

Figure 1: Output showing what SQL queries are blocking and being blocked

In the output, you can see the SessionId 101 is blocking SessionId 75. The users causing the blocking and being blocked are both identified. Similarly, the SQL queries involved in the blocking are identified. You can also see that SessionId 103 is also being blocked by SessionId 101, and SessionId 85 is being blocked by SessionId 103.

Summary

When SQL queries interact, they compete for resources, which leads to temporary blocking. We discussed a script for identifying blocked queries. Queries can be rescheduled so they don't run concurrently. Also, the resource for which the queries are competing can be decoded, possibly removing the contention.

About Manning Publications

Manning Publication publishes computer books for professionals--programmers, system administrators, designers, architects, managers and others. Our focus is on computing titles at professional levels. We care about the quality of our books. We work with our authors to coax out of them the best writi... This author has published 33 articles on DotNetSlackers. View other articles or the complete profile here.