September 18, 2013 at 11:02 pm
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
September 19, 2013 at 12:32 am
- 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
September 19, 2013 at 1:29 am
I agree with you. but the query given shows create procedure....So what is it means?
September 19, 2013 at 2:02 am
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
September 19, 2013 at 3:23 am
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
September 19, 2013 at 11:43 am
Can you share the DDL for the Xevent session you created? Perhaps you aren't capturing the data you need to capture.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2013 at 11:14 pm
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