SQL SERVER – Query to Recent Query on Server with Execution Plan Function to Get SQL

This blog post is written in response to the T-SQL Tuesday hosted by Matt Velic. He has picked very interesting topic which is related to APPLY clause of the T-SQL. When I read the subject, I really liked the subject. This is very new subject and it is quite a interesting choice by Matt.

I tried to explain in simpler words regarding APPLY but it is not that easy to explain. Instead Here is the quick theory from BOL: The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output.

When I talk about APPLY, I always remember query which I use to get latest execution plan from the cache. If the query is not in cache it will not work.

SELECT deqs.last_execution_time AS [Time],
dest.TEXT AS [Query],
decp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
ORDER BY deqs.last_execution_time DESC


Now let us check the query results. The query returns the query text and query execution plan in XML from the memory cache.

Let go further and check the execution plan of the query.

Click on execution plan on the top, you will see the same image enlarged.

If you notice carefully in the execution plan of the query – there are four Table Valued Functions are used. One of the function is which is visible in the execution plan is FNGETSQL.

If you mouse over that function you will figure out that this function gets the SQL Text. Interestingly enough there is another function which does the same with the same number. Let us modify the above query and re-run it.

SELECT deqs.last_execution_time AS [Time],
dest.TEXT AS [Query],
decp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.fn_get_sql(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
ORDER BY deqs.last_execution_time DESC

It will give you the same result and if you compare the execution plan it will be almost same with the same query cost.

I really appreciate SQL as there multiple way to achieve the same result but underlying the technology is the same. This ensures and proves that SQL Server is quality product.

Reference: Pinal Dave (https://blog.sqlauthority.com)