Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extended Events Expand / Collapse
Author
Message
Posted Wednesday, September 18, 2013 11:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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
Post #1496212
Posted Thursday, September 19, 2013 12:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
- 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.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #1496226
Posted Thursday, September 19, 2013 1:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
I agree with you. but the query given shows create procedure....So what is it means?
Post #1496242
Posted Thursday, September 19, 2013 2:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #1496251
Posted Thursday, September 19, 2013 3:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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
Post #1496278
Posted Thursday, September 19, 2013 11:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Can you share the DDL for the Xevent session you created? Perhaps you aren't capturing the data you need to capture.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1496524
Posted Saturday, September 21, 2013 11:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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
Post #1497185
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse