mardi 27 janvier 2015

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


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?





Aucun commentaire:

Enregistrer un commentaire