Published: 10 May 2010
By: Manning Publications

This article is taken from the book SQL Server DMVs in Action. The author discusses a routine that identifies the SQL queries that are currently running, which is useful for debugging.

Contents [hide]

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

Get 30% discount

DotNetSlacker readers can get 30% off the full print book or ebook at www.manning.com using the promo code dns30 at checkout.

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

DMV/Table

Description

sys.dm_exec_requests

Contains details about each request executing on SQL Server

sys.dm_exec_sessions

Contains details about each authenticated session on SQL Server

sys.dm_exec_query_plan

DMF that returns the cached plan in XML format, identified by a given plan handle

sys.dm_exec_sql_text

DMF that returns the sql text identified by a given sql_handle

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

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.

Get 30% discount

DotNetSlacker readers can get 30% off the full print book or ebook at www.manning.com using the promo code dns30 at checkout.

<<  Previous Article Continue reading and see our next or previous articles Next Article >>

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.

Other articles in this category


Lucene.NET vs SQL Server Full-text – Generating a million records and a full-text index
In this article we will take a look at how SQL Server performs with one million records in a table. ...
Creating Pivot Table and Pivot Chart with data from SQL Server 2008
This article shows how you may visualize data on your SQL Server 2008 with Pivot tables and Pivot Ch...
What's blocking my running SQL?
This article is taken from the book SQL Server DMVs in Action. The author discusses a routine that i...
Easy Insert, Update and Retrieve Values for Microsoft SQL Database with C#, Visual Studio
This article will provide you with the small amount of code required to insert, update and retrieve ...
SQL Azure to Developers: Part 1
In this part we will focus on overview of SQL Azure along with a first look on SQL Azure Management ...

You might also be interested in the following related blog posts


Migrating to Postgresql with my friend NHibernate read more
Eloquera Database - the web-oriented client/server object database for .NET read more
Checking for EF to TSQL Query Compilation Changes in VS2010 Beta1 read more
OleDb Parameters to access FoxPro Data from .NET read more
LINQ to Entities, Entity SQL, Parameterized Store Queries and SQL Injection read more
SQL SERVER - Find Currently Running Query - T-SQL read more
SQL SERVER - sqlcmd vs osql - Basic Comparison read more
date validations read more
11 DotNetNuke Video Tutorials - Professional DotNetNuke Administration using SQL (Part 2) read more
Repairing SQL 2005 Business Intelligence Studio after uninstalling VS2005 read more
Top
 
 
 

Please login to rate or to leave a comment.