sys.dm_exec_sql_text – Day 32 – One DMV a Day

Hello Geeks and welcome to the Day 32 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday we have seen how to use sys.dm_exec_requests in combination with sys.dm_exec_connections to get the blocking details. Also we have seen the percent_complete column and estimated completion time in sys.dm_exec_requests. Today I am going to talk about another execution related DMV. It is rather a Dynamic Management Function. Sys.dm_exec_sql_text displays the current request running in the specific session.

Sys.dm_exec_sql_text replaces the DBCC INPUTBUFFER from SQL 2008. The problems with DBCC INPUTBUFFER is that it truncates the statements in the output if they are too long. Sys.dm_exec_sql_text does not truncate the statement. The other difference between these two is with respect to the output shown. To demonstrate this difference let us run the below steps.

I will create a simple procedure to see the output from sys.dm_exec_sql_text for procedures and adhoc statements. In a single session I will run an adhoc query followed by a procedure call.

--Create Procedure
USE AdventureWorks2012
GO
CREATE PROCEDURE usp_getPerson_prc
AS
BEGIN
	 SELECT TOP 10 * FROM person.person
	 WAITFOR DELAY '00:00:10'
END

Run the below query in a new session.

--Session ID 51
SELECT TOP 10 * FROM person.person
WAITFOR DELAY '00:00:10'
EXEC usp_getPerson_prc

Now open a new session and run the below query to see the output within first 10 seconds and within the next 10 seconds.

SELECT est.*
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) est
WHERE session_id = 52
GO
DBCC INPUTBUFFER(52)
GO

Within first 10 seconds

Within next 10 seconds

From the above output you can observe that sys.dm_exec_sql_text shows only the most recent request from the session. But the output from DBCC INPUTBUFFER doesn’t differentiate each request from a session. So you will observe the procedure statements in the later result.

Tomorrow I will be covering another execution related DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook