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 (http://blog.SQLAuthority.com)

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

  1. ” multiple way to achieve the same result” – WHA…
    Not the same

    SELECT * FROM SYS.dm_exec_sql_text(@Handle)

    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    GO
    CREATE FUNCTION sys.dm_exec_sql_text(@handle varbinary(64))
    RETURNS TABLE
    AS
    RETURN SELECT * FROM OPENROWSET(TABLE FNGETSQL, @handle)
    GO

    ———————

    SELECT * FROM sys.fn_get_sql(@Handle)

    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    GO
    /* Shiloh fn_get_sql returned text column as TEXT instead of NTEXT. */
    /* Use the same time in Yukon as well. */
    create function sys.fn_get_sql(@handle varbinary(64))
    returns table
    as
    return select
    dbid,
    objectid,
    number,
    encrypted,
    convert (TEXT, text) as text
    from
    sys.dm_exec_sql_text(@handle)
    GO

    Like

  2. Thanks for participating and the nice compliments, Pinal! As I mentioned to some other people, DMVs were the way I first learned about and began using APPLY. They are so helpful.

    Like

  3. Hi,

    Nice article.

    You can discover a lot more about improving SQL performance via DMVs in this forthcoming book http://www.manning.com/stirk (published at the start of May). It contains more than 100 scripts to identify problems, and offers a wide range of solutions.

    Chapter 1 can be downloaded for free and includes scripts for:

    A simple monitor
    Finding your slowest queries
    Find your missing indexes
    Identifying what SQL is running now
    Quickly find a cached plan

    Thanks
    Ian

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s