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

Inspecting the currently running SQL queries can be very useful for debugging issues on your database servers. Often, running a script when a problem is occurring - for example, a query is running slowly - will illuminate the underlying cause of the problem and give you a greater insight into your processing.

The script we use to identify the queries run over a given time period is shown in listing 1.

Listing 1: What queries are running slow

In the listing, you can see there are two Dynamic Management Views (DMVs) and two Dynamic Management Functions (DMFs) involved in identifying the queries that are currently running. A brief description of each is shown in table 1.

Table 1: DMVs/DMFs for currently running queries

The joining of the DMVs and DMFs provides enough information to determine which SQL queries are currently running across all databases on the server. The DMVs sys.dm_exec_requests and sys.dm_exec_sessions are joined on their common key column session_id. The request's sql_handle is passed to the DMF sys.dm_exec_sql_text to retrieve the text of the SQL query. Similarly, the request's plan_handle is passed to the DMF sys.dm_exec_query_plan to retrieve the cached plan of the query. We use a common pattern to extract the individual query from the parent query.

The query is only concerned with requests that belong to users, as opposed to system requests. Therefore, we include requests where the column is_user_request is equal to 1. We also filter out the currently running script from the results.

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

Figure 1: Output showing what SQL queries are currently running

The output can prove useful in determining the cause of any conflicts. Additionally, it can be useful in ensuring if a given point in a SQL batch has passed. Running this script periodically, perhaps into a semi-permanent table, will allow you to make decisions about your SQL batch and possibly avoid concurrency problems. It is possible to amend the script to include only those SQL queries that are running on the database you're interested in.

Summary

We discussed a DMV-only version of a routine that identifies the SQL queries that are currently running. This should prove useful in debugging blocking and concurrency issues on production systems.

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.