none
Get the Execution Plan of an SPID

    Question

  • Hello,

    Asked this in the Database Engine Forum also.

    I know the below query would get the SQL query that an SPID is running. It works correctly on SQL 2005 but returns a blank value in SQL 2000. Read in a few forums that this will be solved by enabling trace 2861. But is there a disadvantage doing it on a production system?

    DECLARE @Handle binary(20)
    SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 152
    SELECT * FROM ::fn_get_sql(@Handle)

    I'm trying to figure out if there is a way to get the execution plan of the query a particular SPID is running. Tried by adding the SET STATISTICS_ALL ON. But I believe its not the right way.

    Also if anyone can suggest to modify this query to dynamically accept a SPID value and display the text and execution plan? This has to be compatable with SQL 2000.

    Wednesday, July 20, 2011 1:39 PM

Answers

  • If you can run that query without errors, then you are not running SQL Server 2000, since CROSS APPLY was introduced in SQL Server 2005.

    If you run your query in SSMS in Grid mode, then you can simply click on the ShowPlanXML tag to open the XML query plan and it will open as graphical query plan in SSMS.

     

     


    Gert-Jan
    Friday, July 22, 2011 7:18 PM

All replies

  • Hello,

    I know the below query would get the SQL query that an SPID is running. It works correctly on SQL 2005 but returns a blank value in SQL 2000. Read in a few forums that this will be solved by enabling trace 2861. But is there a disadvantage doing it on a production system?

    DECLARE @Handle binary(20)
    SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 152
    SELECT * FROM ::fn_get_sql(@Handle)

    I'm trying to figure out if there is a way to get the execution plan of the query a particular SPID is running. Tried by adding the SET STATISTICS_ALL ON. But I believe its not the right way.

    Also if anyone can suggest to modify this query to dynamically accept a SPID value and display the text and execution plan? This has to be compatable with SQL 2000.

    Wednesday, July 20, 2011 1:29 PM
  • Hello,

    Do you want to get the plan of the query you are actually executing or of someone else spid?

    If it's for your query, there is an option in Query Analyser.

    If it's for some spid; that works only in SQL Server 2005 and higher with the following:

    SELECT EQP.query_plan, *
    FROM sys.dm_exec_requests AS ER
       CROSS APPLY sys.dm_exec_query_plan(ER.plan_handle) AS EQP
    WHERE ER.session_id = @@spid
    


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Wednesday, July 20, 2011 1:59 PM
  • Thanks Olaf. No not for my SPID but any SPID. Don't want to use the query analyser. Is there not an option on SQL 2000. I've seen this in Sybase Databases the Execution plan comes out for a SPID. Was wondering that must be available in SQL Server too.

    Wednesday, July 20, 2011 2:28 PM
  • One way would be to start up SQL Profiler, capture the Performance/Show Plan Text event (or any of the other 2 Show Plan events), and filter on the relevant SPID.

     


    Gert-Jan
    Wednesday, July 20, 2011 7:14 PM
  • Thanks Gret. I'm trying to get that through a query because the requirement is that when any member keys in an SPID he must get the SQL Text and its execution plan. Hope I'm clear in my explaining what I'm looking for?
    Wednesday, July 20, 2011 11:23 PM
  • I don't know if it is an option, but if you are not creating temporary tables (or functionality to the same effect), you could run SET SHOWPLAN_TEXT ON and the query to get the query plans, and then execute the actual query. That would get you the estimated query plan(s) (not necessarily the executed query plans).

    I am not aware of any programmatic feature in SQL Server 2000 to get the actual query plan, since the views to support this all seemed to have been introduced in SQL Server 2005.

     

     


    Gert-Jan
    Thursday, July 21, 2011 11:01 AM
  • When I execute this code

    SELECT EQP.query_plan, *
    FROM sys.dm_exec_requests AS ER
      CROSS APPLY sys.dm_exec_query_plan(ER.plan_handle) AS EQP
    WHERE ER.session_id = 60
    The execuetion plan is in an XML format. Can it be in a text or any other form that would be readable.
    Friday, July 22, 2011 2:43 AM
  • If you can run that query without errors, then you are not running SQL Server 2000, since CROSS APPLY was introduced in SQL Server 2005.

    If you run your query in SSMS in Grid mode, then you can simply click on the ShowPlanXML tag to open the XML query plan and it will open as graphical query plan in SSMS.

     

     


    Gert-Jan
    Friday, July 22, 2011 7:18 PM