Extended Events

  • Dear All

    I am trying to find out why some procedures are getting recomiled. For this when i run following select,

    SELECT deqs.last_execution_time AS [Time], dest.text AS [Query],dest.dbid

    FROM sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

    ORDER BY deqs.last_execution_time DESC

    it gives out put as many lines of

    "Create procedure...."

    To find out the reason for "create procedure ...", I tried with extended event. I am capturing SQLStatementREcompile event. But when start the session and check the live data, it does not show same numbers of lines as shown by above select.

    Am i missing some thing?

    Regards

  • - not every execution produces a recompile. ( that's the goal with cached plans )

    I haven't implemented the xevents version to monitor it all, but I'll have a go if I find some time later today. :Whistling:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I agree with you. but the query given shows create procedure....So what is it means?

  • that's the text of the compiled object. So if that is (part of) a stored procedure it will show the sproc ddl ( unless it is encrypted ).

    Using the statement offset ( , deqs.[statement_start_offset] ) , you can figure out which command in the sproc it is all about.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks.

    To chekc it, i kept extended event capturing on and executed the procedure. The sql returned 5 rows as "create procedure...". (from your explanation it must be the statamentd from the procedure. ) but extended event did not capture any thing. Why?

    Regards

  • Can you share the DDL for the Xevent session you created? Perhaps you aren't capturing the data you need to capture.

  • Hi

    script for extended event is as follows

    CREATE EVENT SESSION [xx] ON SERVER

    ADD EVENT sqlserver.sql_statement_recompile(SET collect_object_name=(1),collect_statement=(1)

    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))

    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

    GO

    Regards

Viewing 7 posts - 1 through 6 (of 6 total)

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