SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Show running queries

By Richard Swinbank,

This query provides information about queries currently in execution, useful typically when SQL Server is processing batches of long-running queries. It augments the information available from the SQL activity monitor with the text and line number of the current statement in each batch, includes information about tempdb utilisation, and provides a link to the query execution plan (if available).

Some additional notes:

  • UserObjMB* typically relates to storage explicitly requested by the user (e.g. in temporary tables). The asterisk is intended to indicate that this is not always reliable - deferred deallocation in tempdb can make this appear to be higher than is the case.
  • InternalObjMB relates to storage used by the database engine in evaluating the batch (e.g. hash buckets, temporary sort runs etc)
  • The link to the query plan may be null if the plan is no longer in the cache or if the plan is too complex. In the latter case a text plan may still be available and can be obtained using the commented-out OUTER APPLY sys.dm_exec_text_query_plan.
  • The purpose of the NOT LIKE '%9ow34ytghehl3q94wg%' clause is to exclude *this* query from the output!

Total article views: 5023 | Views in the last 30 days: 4
 
Related Articles
ARTICLE

Querying the INFORMATION_SCHEMA

Using a macro to query the INFORMATION_SCHEMA

FORUM

Problem while executing Query batch

Problem while executing Query batch using c# code

BLOG

SQL Server 2005 Diagnostic Information Queries

Here is an updated version of my diagnostic information queries for SQL Server 2005. You can get the...

BLOG

SQL Server 2005 Diagnostic Information Queries (November 2010)

Here is the November 2010 version of my diagnostic information queries for SQL Server 2005. Many of ...

BLOG

Updated SQL Server 2008 Diagnostic Information Queries

Here is the October 2009 version of my SQL Server 2008 Diagnostic Information Queries. They are very...

Tags
 
Contribute