January 27, 2015 at 8:33 am
I do have the following Extended Event session on my Dev box;
CREATE EVENT SESSION [sp_showplan] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlserver.plan_handle)
WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[equal_i_sql_unicode_string]([object_name],N'MyStoreProcedure')))
ADD TARGET package0.event_file(SET filename=N'E:\DBA_Audit\SP_Exec.xel',metadatafile=N'E:\DBA_Audit\SP_Exec.xem')
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
The idea is being able to capture the execution plan when the program invokes the store procedure, regardless of the database.
This works on my Dev box. When I manually trigger "MyStoreProcedure" from database A , the event is saved. The same thing happens when I do that from database B. Ok ... so far, so good.
So I went to the live production environment and setup my Extended Events session. But it's saving nothing. I was able to check that the store procedure was executed on several databases but my extended events session never grabbed the plan.
What could be the reason for this? Memory starvation maybe? Is there something I am doing wrong or that I should tweak so I can make it work?
January 27, 2015 at 11:44 am
Silly, but obvious question- is the Extended Event session running? Sometimes when they are created, they are disabled by default. Are you able to view live data and execute the proc similar to what you did in development?
January 27, 2015 at 12:36 pm
Wow! :doze: ... I can't believe people keep asking me about that and if the session has been started. It happened to me on StackExchange too.
Yes! The session is running. And I can see the file created on the local drive.
The session works without any issues on my Dev box. It collects the data I need, which on this particular case, is the execution plan. It does not on Pro.
January 29, 2015 at 12:14 pm
The event session that you sent over didn't generate any data for me. I created a simple procedure named MyStoreProcedure so as to not modify the definition and didn't get any results.
If I removed the object_type clause and only had the object_name clause left in there, it seemed to work.
I couldn't figure out why the second clause wasn't working, but if changed to this, it seems to be working:
AND [package0].[is_any_on_uint64]([object_type],(8272))
The is_any_on_uint64 set to PROC converted it to the 8272 number when I scripted it out.
Doesn't really answer your question, but it's another way to attempt getting the same data.
Hope that helps,
Steve
January 29, 2015 at 1:02 pm
S. Kusen (1/29/2015)
The event session that you sent over didn't generate any data for me. I created a simple procedure named MyStoreProcedure so as to not modify the definition and didn't get any results.If I removed the object_type clause and only had the object_name clause left in there, it seemed to work.
I couldn't figure out why the second clause wasn't working, but if changed to this, it seems to be working:
AND [package0].[is_any_on_uint64]([object_type],(8272))
The is_any_on_uint64 set to PROC converted it to the 8272 number when I scripted it out.
Doesn't really answer your question, but it's another way to attempt getting the same data.
Hope that helps,
Steve
Thanks
That's interesting.
TBH, I didn't generate the tsql. I just created the event on management studio and then I scripted it out. So... maybe a bug?
Not at work now so I will check tonight.
January 29, 2015 at 1:48 pm
I'm curious if it works or not for you. I couldn't get it to work with the [equal_uint64] filter so i gave [is_any_on_uint64] a try.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply