Blog

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

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!)

--******************
--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
↑ Back to top
  1. Pingback: (SFTW) SQL Server Links 28/02/14 • John Sansom

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php