vendredi 30 janvier 2015

This Extended Events session works on Dev but not on Pro. Why? [on hold]


I do have the following Extended Event session on my Dev and Pro boxes:



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


Its main purpose is capture the execution plan for a given store procedure (any database).


I tested it on Dev and it works. I called the store procedure from two different databases and I was able to capture the execution plan.


Then I went to the live box but it does nothing. And yes, the store procedure was called there too.


Any reason why is not collecting the information on PRO? Anything that I should look at?


EDIT 1:


The session has ben created. I started it and it can generate the file. But the session runs and saves nothing. There are no permission issues at server level it's just that the session saves nothing.


EDIT 2:


Yes, I know this Event can be expensive. With doing nothing I mean, it captures no plan. I successfully created the EV session at SQL instance level, of course.


EDIT 3:


I'm Jose, the OP. Not sure why my q. was put on hold. To me, my question is clear. Above T-SQL for the Extended Event session captures Execution Plan of store procedures on my Dev SQL instance. When the same Extended Event session is created and is running on Pro, it does not capture actual execution plan. I want to know why! A bug maybe??? Maybe the T-SQL script that was generated from Dev via GUI added a line of code that breaks the Extended Event session on PRO? I have not created the Extended Event session on PRO via GUI. That was how it created it on Dev. I just script it out.





Aucun commentaire:

Enregistrer un commentaire