THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tamarick Hill

July, the 31 Days of SQL Server DMO’s – Day 4 (sys.dm_exec_sql_text)

 

The sys.dm_exec_sql_text Dynamic Management Object is the first Function that we will review. This function is used to return sql text from a batch by passing in the sql_handle. If we recall one of the DMV’s that we have previously reviewed (sys.dm_exec_requests), this DMV provided a sql_handle column for each of the requests that were executing on the server. If we take that sql_handle and pass it into the sys.dm_exec_sql_text function, we are able to extract the actual T-SQL statements that are being executed.

To illustrate how this function works, I will first need to capture sql_handle. **NOTE: I have a SQL Statement running in a loop in a separate window against the AdventureWorks2012 Database.

SELECT * FROM sys.dm_exec_requests
WHERE session_id > 50 and session_id <> @@SPID

image

Now that I have a sql_handle to work with, I can either call the sys.dm_exec_sql_text function directly and pass in the sql_handle, or I can run a query against my sys.dm_exec_requests DMV and use a CROSS APPLY with the sys.dm_exec_sql_text function.

SELECT * FROM sys.dm_exec_sql_text(0x0200000059598407105BDB167364C21EC3C2568FC10FF80A0000000000000000000000000000000000000000)

image

As we can see if the text column, the actual SQL text that I had running in a separate window is displayed here. Additionally you see columns for the Database ID and Object ID. These columns return NULL values whenever you are running ad hoc or perpared SQL Statements, which explains why they are NULL now. The ‘number’ columns refers to numbered stored procedures and the encrypted column represents whether or not the SQL text is encrypted or not.

Optionally we could have queried the sys.dm_exec_requests DMV and used a CROSS APPLY as you see below.

SELECT er.session_id, er.status, er.command, db_name(er.database_id) [Database], st.text
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE er.session_id > 50 and session_id <> @@SPID

image

This function is very useful when you need to extract the actual text that is being executed on a server. Many DBA’s tend to use DBCC INPUTBUFFER() but for extremely long SQL statements, the text output can be truncated when using DBCC INPUTBUFFER(). This Dynamic Management Function will never truncate your text.

For more information on this DMF, please see the below Books Online link:

http://msdn.microsoft.com/en-us/library/ms181929.aspx

Published Thursday, July 04, 2013 11:16 AM by Tamarick Hill

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement