vendredi 19 décembre 2014

Visualizing SQL Server Extended Events data


Lately I've been exploring using Extended Events in SQL Server to help me benchmark and optimize various queries. So far, to view the event data I have been using the "Watch Live Data" feature in SSMS.


The problem that I am having is that it appears that the Live Events feature uses an internal buffer, which means that sometimes I need to execute a query several times to get its information to display in the window. I therefore have a two-part question to ask:



  1. Is there a way to get around this delay in getting the events to display in the live feed? (I'm doing this on a local database so performance is not an issue)

  2. Is the live feed the best way to visualize Extended Events data? Is there another tool either in SSMS or not that is better adapted to my use case?




UPDATE


As requested, here is the session:



CREATE EVENT SESSION [Simple Query Benchmarking] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
ACTION(sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_uint64]([duration],(1000))))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO




Aucun commentaire:

Enregistrer un commentaire