For my test I get session_id = 53

Now I can utilize the sys.dm_exec_connections Dynamic Management View, in conjunction with thesys.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.

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:

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.