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