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

SQL SERVER - Query to Recent Query on Server with Execution Plan Function to Get SQL TSQL2sDay 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.

Solarwinds

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

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

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

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.

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

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.

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

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)

Solarwinds
Previous Post
SQL SERVER – expressor Studio Includes Powerful Scripting Capabilities
Next Post
SQL SERVER – FIX – ERROR : Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)

Related Posts

5 Comments. Leave new

  • ” 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Brilliant! I didn’t know that you can do this. You rock!

    Reply
  • how to add codition to check textqualifier in Openrowset statement with format file?

    Reply

Leave a Reply

Menu