Extended Events for Tracking Code Improvements (video)
1 Comment
Ever wanted to prove that your code or index changes had a positive impact? In this 30 minute video, Kendra shows you how to use Extended Events to measure a production workload– and how you can aggregate the results to prove your changes made a difference.
Want to skip straight to the demos? Here’s a quick timeline:
- Demo 1: How I generate a fake workload – 2:45
- Demo 2: Taking an initial benchmark with Extended Events – 4:45
- Demo 3: Aggregating the results of the Extended Events Trace -10:52
- Demo 4: Taking a second benchmark with Extended Events and aggregating the results – 18:05
http://youtu.be/I9cpowghzs8
Want a sample of the type of Extended Events trace run in the demo? Here’s a sample script. This isn’t any rocket science on my part: I generated the script with the Extended Events wizard and then told it to script it out. (Try it yourself!)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
--****************** --These scripts were generated from SQL Server 2012 --Management Studio Extended Events Wizard --Customization is primarily just formatting for readability --****************** CREATE EVENT SESSION [Production Perf Sample-Before] ON SERVER ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(sqlserver.client_app_name,sqlserver.database_id, sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id) WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5))) AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0))) AND ([sqlserver].[query_hash]<>(0)) )), ADD EVENT sqlserver.sql_statement_completed( ACTION(sqlserver.client_app_name,sqlserver.database_id, sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id) WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5))) AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0))) AND ([sqlserver].[query_hash]<>(0)) )) ADD TARGET package0.event_file (SET filename=N'c:\Xevents\Traces\Production Perf Sample-Before') WITH ( EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF ) GO --ALTER EVENT SESSION [Production Perf Sample-Before] ON SERVER STATE=START; --GO --ALTER EVENT SESSION [Production Perf Sample-Before] ON SERVER STATE=STOP; --GO |
