Learn more about SQL Server tools

   
   

















































Latest from MSSQLTips

Find Tables, Columns or Stored Procedures used in a SQL Server Reporting Services Report

Dynamically Refresh SQL Server Reporting Services Report Date Parameters

SQL Server 2016 T-SQL Syntax to Query Temporal Tables

Troubleshoot SQL Azure Bacpac Import Failures

On-Demand Webcast - Monitoring SQL Server with Mobile Devices

Configure the SQL Server Integration Services For Loop Container

On-Demand Webcast - Performance Monitoring with Uptime

On-Demand Webcast - Physical and Virtual Performance Monitoring with SQL Diagnostic Manager

On-Demand Webcast - Encrypting Data with SQL Server

On-Demand Webcast - Performance monitoring with Spotlight on SQL Server

Whitepaper - Resolving the Database Performance Blame Game

3 Tips for Managing Large Numbers of SQL Server Jobs

Accelerate SQL Server with Flash Storage














Identify last statement run for a specific SQL Server session

MSSQLTips author Tim Ford By:   |   Read Comments (2)   |   Related Tips: More > Dynamic Management Views and Functions



>>> >> > Vote for your favorite MSSQLTips.com Authors < << <<<


Problem
I was reading a recent blog post from Pinal Dave, SQL Server MVP, regarding returning information on the latest query executed for a given session.  He offered up a couple options to return the last query statement executed, settling upon querying the sys.sysprocesses system compatibility view, but another way that this can be done is through the Dynamic Management Views and Functions.  The process for doing so is quite straight-forward and works in all versions of Microsoft SQL Server since DMOs (dynamic management objects) were integrated into SQL Server. 

Solution
Before proceeding we should take a second to explain what a session is.  In Microsoft SQL Server, a session is synonymous with a user process.  Previous to SQL 2005 sessions were referred to - and identified solely - as SPIDs (short for session id).  A SPID uniquely identifies a session and a SPID is unique across the SQL Server instance.  In an attempt to conform SQL Server object identifiers to be more user-friendly and to standardize a naming convention across all system objects, sessions are now identified across the DMO and system catalog views as session_id.  You'll see similar changes between previous versions of SQL Server and current versions where all object identifiers are concerned.

You can use the @@spid() system function to return the session_id of the current session as follows:

SELECT @@SPID

For my test I get session_id = 52.

So, now that we've identified what session_id uniquely identifies the session I'm using during this demonstration, I'll do a simple query against the Northwind database.

SELECT C.[CompanyName] 
FROM [Northwind].dbo.[Customers] C 
WHERE C.[City] 'Berlin' 
ORDER BY [C].[CompanyName]

At this point I'll now open up a separate query window in SQL Server Management Studio.  If I now execute the first query above you'll see that this registers as a new session on the SQL Server instance:

SELECT @@SPID

For my test I get session_id = 53

Now I can utilize the sys.dm_exec_connections Dynamic Management View, in conjunction with the sys.dm_exec_sql_text Dynamic Management Function to return the last query statement executed against the SQL Server instance on a selected session.  In all truth, you can return the last query executed on all sessions, but for the sake of this discussion we're limiting the results based upon the session_id (52) we've identified above.  I'll present the query, then we can examine in detail what it provides for us.

SELECT DEST.TEXT 
FROM 
sys.[dm_exec_connections] SDEC
 
CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]AS DEST
WHERE SDEC.[most_recent_session_id] 52 

The output for this query shows the statement that was run for session_id 52.

So what just happened?  Simply-put, we returned the results from the sys.dm_exec_connections DMV, limiting the results by the session_id (52) we identified above.  We, submitted the value contained in the most_recent_sql_handle column of this DMV to the sys.dm_exec_sql_text Dynamic Management Function.  That function then returned as text, the value of the sql_handle we passed to it. 

So what is a sql_handle?  Think of a sql_handle as a unique identifier for a query that is unique across the entire SQL Server instance.  Just as a session_id uniquely identifies a session, so does a sql_handle identify a query.  The actual value of the sql_handle column is very cryptic.  The value for the most_recent_sql_handle in this example is shown below:

SELECT SDEC.[most_recent_sql_handle]DEST.[text] 
FROM sys.[dm_exec_connections] SDEC
 
CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]AS DEST
WHERE SDEC.[most_recent_session_id] 52 

Here we can see the value of the sql_handle and the text translation.

The handle itself does not really do much for us without the function call that rationalizes it into the original query text.  As you can see though, this very simple query does provide us with yet another option for returning information on what users are (or have been) doing on the SQL Server instances we support.

Next Steps

  • The Dynamic Management Objects have so much to offer the DBA.  Check out other tips on DMOs from MSSQLTips.com.
  • Read more tips by the author here.
  • Still interested in information on sysprocesses, whether as a system table (pre-SQL 2005) or system view?  Here are some tips that meet your needs.


Last Update: 7/22/2009


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools


Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, May 29, 2012 - 7:41:19 AM - Robert Read The Tip

Now try with an EXEC call into a sp with parameters, You will NOT get the batch sql, but the SP source - at least under 2k8r2sp1.


Thursday, July 30, 2009 - 2:18:05 AM - rubik Read The Tip

very good

 dbcc inputbuffer (@@spid)




 
Sponsor Information







 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.