Sql Server 2008 extended event

Nov 26, 2010 at 10:11 AM

I want to capture the bad execution plan(actual execution plan) i.e long running query using extended event. when i use dmv's  i am able to view the execution plan . when  i use extended event i am not able to view the execution plan.

SELECT

 

Q.query_plan,T.text

FROM

 

 sys.dm_exec_procedure_stats cross apply sys.dm_exec_sql_text(sql_handle) T

cross

 

 apply sys.dm_exec_query_plan(plan_handle) Q

I want to capture  execution plan & save this plan in target file for future analysis so that i created a extended event.



IF

EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRunningQuery'

)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

DROP

 

EVENT SESSION LongRunningQuery ON

SERVER

GO

-- Create Event

CREATE

 

EVENT SESSION

LongRunningQuery

ON

 

SERVER

-- Add event to capture event

ADD

 

EVENT sqlserver.

sql_statement_completed

(

-- Add action - event property

ACTION

 

(sqlserver.sql_text, sqlserver.tsql_stack,

sqlserver

.database_id,

sqlserver

.plan_handle,

sqlserver

.session_id,

sqlserver

.sql_text

)

-- Predicate - time 3 Minute

WHERE

(

 

duration > (300000000

)))

-- Add target for capturing the data - XML File

ADD

 

TARGET package0.asynchronous_file_target

(

SET

 

filename='C:\Demo\Traces\LongRunningQuery.xet', metadatafile='C:\Demo\Traces\LongRunningQuery.xem'

)

WITH

 

(max_dispatch_latency = 1 seconds

)

GO

-- Enable Event

ALTER

 

EVENT SESSION LongRunningQuery ON

SERVER

STATE

 

=

START

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SELECT

 

* FROM

(

 

SELECT

data

.value

(

 

'(/event[@name=''sql_statement_completed'']/@timestamp)[1]', 'DATETIME') AS [Time]

,

data

.value

(

 

'(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)]

,

 

CONVERT (FLOAT, data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT')) /

1000000

 

AS [Duration (s)]

,

data

.value

(

 

'(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement]

,

 

SUBSTRING (data.value ('(/event/action[@name=''plan_handle'']/value)[1]', 'VARCHAR(100)'), 15, 50

)

 

AS

[PlanHandle]

FROM

 

 

 

(SELECT CONVERT (XML, event_data) AS data FROM sys.

fn_xe_file_target_read_file

 

 

('C:\Demo\Traces\LongRunningQuery*.xet', 'C:\Demo\Traces\LongRunningQuery*.xem', null,

null)

)

 

entries

)

 

as

t

GO

 

2010-11-26 08:59:40.953 16812 254.436523 exec xxx  0x06000100DF7ED504B8803707000000000000000000000000
 

 

Select

* from sys.dm_exec_query_plan(0x06000100DF7ED504B8803707000000000000000000000000)

 

The

plan handle is differs for same stored procedures . when i get plan_handle from sys.dm_exec_query_stats/ sys.dm_exec_procedure_stats it seems to be simillar. But when see the plan handle of extended events it differs. Please provide me a solution for this

.

 

 







 

 

Coordinator
Nov 26, 2010 at 2:22 PM

This discussions on this site are for topics related to the Addin and not Extended Events in general.  You should post your question to the SQL Database Engine forum on MSDN Forums:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/threads