{"id":529,"date":"2011-04-07T03:10:41","date_gmt":"2011-04-07T03:10:41","guid":{"rendered":"\/blogs\/jonathan\/post\/What-plan_handle-is-Extended-Events-sqlserverplan_handle-action-returning.aspx"},"modified":"2017-04-13T14:41:48","modified_gmt":"2017-04-13T18:41:48","slug":"what-plan_handle-is-extended-events-sqlserver-plan_handle-action-returning","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/what-plan_handle-is-extended-events-sqlserver-plan_handle-action-returning\/","title":{"rendered":"What plan_handle is Extended Events sqlserver.plan_handle action returning?"},"content":{"rendered":"

The topic for today’s post comes from a <\/font>forums question<\/font><\/a> and a subsequent <\/font>Connect feedback item<\/font><\/a> where someone noted that the plan_handle being returned by Extended Events using the sqlserver.plan_handle action was not available in the plan cache even when queried immediately following completion of the event that should have produced the plan cache entry.  To setup the appropriate context of this post, let’s first take a look at the original repro for this problem.<\/font><\/p>\n

\n
\n
-- Create the Event Session<\/font><\/span><\/pre>\n
IF<\/span> EXISTS<\/span>(SELECT<\/span> * <\/font><\/pre>\n
          FROM<\/span> sys.server_event_sessions <\/font><\/pre>\n
          WHERE<\/span> name='SQLStmtEvents'<\/span>)<\/font><\/pre>\n
    DROP<\/span> EVENT SESSION<\/span> SQLStmtEvents <\/font><\/pre>\n
    ON<\/span> SERVER;<\/font><\/pre>\n
GO<\/font><\/span><\/pre>\n
CREATE<\/span> EVENT SESSION<\/span> SQLStmtEvents<\/font><\/pre>\n
ON<\/span> SERVER<\/font><\/pre>\n
ADD<\/span> EVENT sqlserver.sql_statement_completed(<\/font><\/pre>\n
    ACTION<\/span> (sqlserver.client_app_name,<\/font><\/pre>\n
            sqlserver.plan_handle,<\/font><\/pre>\n
            sqlserver.sql_text,<\/font><\/pre>\n
            sqlserver.tsql_stack,<\/font><\/pre>\n
            package0.callstack,<\/font><\/pre>\n
            sqlserver.request_id)<\/font><\/pre>\n
--Change this to<\/span> match<\/span> the AdventureWorks, <\/font><\/pre>\n
--AdventureWorks2008 or<\/span> AdventureWorks2008 DB_ID()<\/font><\/pre>\n
WHERE<\/span> sqlserver.database_id=5 <\/font><\/pre>\n
)<\/font><\/pre>\n
ADD<\/span> TARGET package0.ring_buffer<\/font><\/pre>\n
WITH<\/span> (MAX_DISPATCH_LATENCY=5SECONDS, TRACK_CAUSALITY=ON<\/span>)<\/font><\/pre>\n
GO<\/font><\/span><\/pre>\n
 <\/font><\/pre>\n
-- Start the Event Session<\/font><\/span><\/pre>\n
ALTER<\/span> EVENT SESSION<\/span> SQLStmtEvents <\/font><\/pre>\n
ON<\/span> SERVER <\/font><\/pre>\n
STATE<\/span> = START<\/span>;<\/font><\/pre>\n
GO<\/font><\/span><\/pre>\n
 <\/font><\/pre>\n
-- Change database contexts and insert one row<\/font><\/span><\/pre>\n
USE<\/span> AdventureWorks2008;<\/font><\/pre>\n
GO<\/font><\/span><\/pre>\n
INSERT INTO<\/span> [dbo].[ErrorLog]([ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])<\/font><\/pre>\n
VALUES<\/span>(getdate(),SYSTEM_USER<\/span>,-1,-1,-1,'ErrorProcedure'<\/span>,-1,'An error occurred'<\/span>)<\/font><\/pre>\n
GO<\/font><\/span><\/pre>\n
 <\/font><\/pre>\n
-- Retrieve the Event Data from the Event Session Target<\/font><\/span><\/pre>\n
SELECT<\/font><\/span><\/pre>\n
    event_data.value<\/span>('(event\/@name)[1]'<\/span>, 'varchar(50)'<\/span>) AS<\/span> event_name,<\/font><\/pre>\n
    CAST<\/span>(event_data.value<\/span>('(event\/action[@name="plan_handle"]\/value)[1]'<\/span>, 'varchar(max)'<\/span>) AS<\/span> XML) as<\/span> plan_handle,<\/font><\/pre>\n
    event_data.value<\/span>('(event\/action[@name="sql_text"]\/value)[1]'<\/span>, 'varchar(max)'<\/span>) AS<\/span> sql_text<\/font><\/pre>\n
FROM<\/span>(    SELECT<\/span> evnt.query('.'<\/span>) AS<\/span> event_data<\/font><\/pre>\n
        FROM<\/span><\/font><\/pre>\n
        (    SELECT<\/span> CAST<\/span>(target_data AS<\/span> xml) AS<\/span> TargetData<\/font><\/pre>\n
            FROM<\/span> sys.dm_xe_sessions AS<\/span> s<\/font><\/pre>\n
            JOIN<\/span> sys.dm_xe_session_targets AS<\/span> t<\/font><\/pre>\n
                ON<\/span> s.address = t.event_session_address<\/font><\/pre>\n
            WHERE<\/span> s.name = 'SQLStmtEvents'<\/span><\/font><\/pre>\n
              AND<\/span> t.target_name = 'ring_buffer'<\/span><\/font><\/pre>\n
        ) AS<\/span> tab<\/font><\/pre>\n
        CROSS<\/span> APPLY TargetData.nodes ('RingBufferTarget\/event'<\/span>) AS<\/span> split(evnt) <\/font><\/pre>\n
     ) AS<\/span> evts(event_data)<\/font><\/pre>\n
     <\/font><\/pre>\n
-- Use the plan_handle from one of the Events to get the query plan<\/font><\/span><\/pre>\n
DECLARE<\/span> @plan_handle varbinary(64) = 0x06000800DD8D6D0840015585000000000000000000000000<\/font><\/pre>\n
SELECT<\/span> * <\/font><\/pre>\n
FROM<\/span> sys.dm_exec_query_plan(@plan_handle)<\/font><\/pre>\n<\/p><\/div>\n<\/blockquote>\n