sql_statment_recompile

Question

sql_statment_recompile

Krishna1

Hall of Fame

Points: 3666
More actions
November 4, 2013 at 2:19 am

#282066

Dear All
Want to capture data related to sql_statment_recompile event. This captured information needs to be stored to the tables. Please guide hoe to go about.
Regards

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could you be a little more specific about what you want to collect, when and how?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Krishna1 Hall of Fame Points: 3666 More actions · Answer 2

Hi

We can monitor this event using the extended events. Want to store output of this event in a table . How do i do this?

Regards

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Use extended events to capture the data to a file.

Then, you can use fn_xe_file_target_read_file to read the file. It'll output as XML. Here's an example XML query that might be helpful:

WITH xEvents

AS (SELECT object_name AS xEventName,

CAST (event_data AS XML) AS xEventData

FROM sys.fn_xe_file_target_read_file('C:\PerformanceTuning\*.xel',

NULL, NULL, NULL)

)

SELECT xEventName,

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

'bigint') Duration,

xEventData.value('(/event/data[@name=''physical_reads'']/value)[1]',

'bigint') PhysicalReads,

xEventData.value('(/event/data[@name=''logical_reads'']/value)[1]',

'bigint') LogicalReads,

xEventData.value('(/event/data[@name=''cpu_time'']/value)[1]',

'bigint') CpuTime,

xEventData.value('(/event/data[@name=''batch_text'']/value)[1]',

'varchar(max)') BatchText,

xEventData.value('(/event/data[@name=''statement'']/value)[1]',

'varchar(max)') StatementText,

xEventData.value('(/event/data[@name=''query_plan_hash'']/value)[1]',

'binary(8)') QueryPlanHash

FROM xEvents

ORDER BY LogicalReads DESC ;

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning