Scribd Logo
Upload

Welcome to Scribd!

  • 11 views

    Sys DM Exec Text Query Plan

    Uploaded by

    kishore2285
    Sys.dm_exec_text_query_plan accepts statement start and end offsets to get the execution plan for a specific statement running within a stored procedure or batch. The document demonstrates using sys.dm_exec_text_query_plan to retrieve the individual execution plans for two statements within a stored procedure while it is running.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Sys DM Exec Text Query Plan

    Uploaded by

    kishore2285
    11 views2 pages
    Sys.dm_exec_text_query_plan accepts statement start and end offsets to get the execution plan for a specific statement running within a stored procedure or batch. The document demonstrates using sys.dm_exec_text_query_plan to retrieve the individual execution plans for two statements within a stored procedure while it is running.

    Original Description:

    vvv

    Original Title

    Sys Dm Exec Text Query Plan

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Sys.dm_exec_text_query_plan accepts statement start and end offsets to get the execution plan for a specific statement running within a stored procedure or batch. The document demonstrates using sys.dm_exec_text_query_plan to retrieve the individual execution plans for two statements within a stored procedure while it is running.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    11 views2 pages

    Sys DM Exec Text Query Plan

    Uploaded by

    kishore2285
    Sys.dm_exec_text_query_plan accepts statement start and end offsets to get the execution plan for a specific statement running within a stored procedure or batch. The document demonstrates using sys.dm_exec_text_query_plan to retrieve the individual execution plans for two statements within a stored procedure while it is running.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 2

    Unlikesys.dm_exec_query_plansys.

    dm_exec_text_query_planacceptsstatement_start_offsetand
    statement_end_offsettogettheplanoftheexactstatementrunning.TheotherbenefitfromthisDMVistouse
    thisoutputinUSEPLANqueryhintorusingPLANGUIDE.
    Todemonstratemoreonsys.dm_exec_text_query_planIwillmodifytheprocedureIusedinlasttwoblogs.Iwill
    beaddinganotherstatementtotheprocedure.
    1
    2
    3
    4
    5
    6
    7
    8

    USEAdventureWorks2012
    GO
    CREATEPROCEDUREusp_getPerson_prc
    AS
    BEGIN

    SELECT*FROMperson.person

    SELECT*FROMperson.Address
    END

    Nowexecutetheabovestoredprocedure.Whiletheabovestoredprocedureisrunningandifyouarefast
    enoughyoucanrunthebelowquerytwiceduringexecution.Youwillfindthebelowoutputfortwodifferent
    statementsduringtheprocedureexecution.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12

    SELECTer.session_id,

    SUBSTRING(est.text,statement_start_offset/2+

    (((CASEWHENer.statement_end_offset=

    THENdatalength(est.text)

    ELSEer.statement_end_offsetEND)

    AScurrent_stmnt,

    textASbatch,
    CAST(etqp.query_planASXML)ASstmnt_plan
    FROMsys.dm_exec_requestser
    CROSSAPPLYsys.dm_exec_sql_text(er.sql_handle)est
    CROSSAPPLYsys.dm_exec_text_query_plan(er.plan_handle
    WHEREsession_id=54Changethesession_idasneeded

    Firststatement:

    Secondstatement:

    Theexecutionplansforindividualstatementsinabatchcanbecollectedlikethisusing
    sys.dm_exec_text_query_plan.Sostartusingittoday.

    You might also like