SQL SERVER – Get Query Plan Along with Query Text and Execution Count

Quite often, we need to know how many any particular objects have been executed on our server and what their execution plan is. I use the following handy script, which I use when I need to know the details regarding how many times any query has ran on my server along with its execution plan. You can add an additional WHERE condition if you want to learn about any specific object.

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'

In result set, you will find the last column QueryPlan with XML text in it. Click on it, and it will open a new tab displaying the graphical execution plan.

You can also save the newly opened tab in XML format with the extension .sqlplan. When this new file is opened in SQL Server Management Studio, it will automatically display the graphical representation of the XML Plan.

It is a quite simple script, but it works all the time.

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

About these ads

18 thoughts on “SQL SERVER – Get Query Plan Along with Query Text and Execution Count

  1. Hi Pinal

    When I executed the query you mentioned above on SQL Server 2005, it throws an error saying…

    “Msg 174, Level 15, State 1, Line 2
    The object_name function requires 1 argument(s).”

    Does it work on SQL Server 2008?

    Thanks
    Sy

  2. Hi Pinal,

    When I executed this is the error msg i got for a particular object.

    Msg 6335, Level 16, State 101, Line 1
    XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

    Pls advise.

    Thanks,
    Gautham

  3. @Gautham: In SQL Server XML data type accepts only up to 128 levels of nodes nesting. Probably there is a plan that is too complicated to be stores in XML data type :-)

    @Sy: What build of SQL Server 2005 do you have? It works for me on 9.00.4207.00. Also, check if your database is in compatibility level 90.

  4. Well in SQL SSMS if you goto activity monitor and go to “Recent expensive queries” pane, right click and you will get the “Show execution plan” for a selected query..you will get the same result.

      • Me too, I am seeing the XML plan in SQL 2008 R2, but the graphical plan in SQL 2005. I want to get 2008 R2 to return the graphical version but cannot find anything that shows how to do it. Please help!

        Thank you
        Gary

  5. Pingback: SQL SERVER – Plan Cache and Data Cache in Memory Journey to SQL Authority with Pinal Dave

    • the execution count here is cumulative since the sql services started…..if so then can we have a way to cumulate execution count/per hr or so…..!!
      Please advise…..

      Thank You,
      Vaibhav

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  7. Hi Pinal,

    I am getting the following error in my error log.
    43003 2014-02-03 16:01:43.590 spid87 The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large numbe
    r of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

    I have to know the query which is giving the above error. Could you please help me to find the query which is producing this error. I am facing this error from last few days but I am unable to find the query..

    Thanks and Regards,
    Raja Kumar Chikkala.

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