Extend Your Knowledge with Extended Events Janis Griffin

Extend Your Knowledge with Extended Events! Janis Griffin Senior DBA 1

Who Am I • Senior DBA for Confio Software – Janis. Griffin@confio. com – @Do. Bout. Anything • Current – 24+ Years DBA – Primarily SQL Server, Sybase, Oracle • Former – Database Design & Implementation • Specialize in Performance Tuning • Review Database Performance for Customers and Prospects • Common Thread – How do I tune it? 2

Agenda • • • Extended Events Introduction Terms & Useful DMVs How to Create in 2012 Viewing & Reporting Event Info Examples – Deadlock Monitoring – Query Performance – Aggregate Like SQLs 3

Extended Events Intro (EE) • Lightweight event-handling mechanism – Captures event information like SQL Profiler / SQL Trace – More information plus you can now configure • When events are triggered, they can be sent to a target for further analysis • Introduced in SQL Server 2008 – Very complex to code and read (parse xml) • Much Improved in 2012 with many more Events – SSMS has Extended Event Interface • Deprecation Announcement – SQL Profiler and SQL Trace won’t be in Versions > 2012 4

Extended Events Intro (EE) • 2008 - DDL statements that create / modify Extended Events sessions – CREATE EVENT SESSION • Creates an extended event session object • Identifies Source of the events, Targets, and Parameters – ALTER EVENT SESSION • Starts/stops an event session or changes an event session configuration – DROP EVENT SESSION • Drops an event session • DMVs / Catalog views show session data & metadata – Use TSQL statements to get information on every extended events session that is created 5

Catalog Views for EE Metadata Views for event session creation • sys. server_event_sessions – Lists all event session definitions • sys. server_event_session_actions – Returns a row for each action on each event of an event session • sys. server_event_session_events – Returns a row for each event in an event session • sys. server_event_session_fields – Returns a row for each customizable column explicitly set on events and targets • sys. server_event_session_targets – Returns a row for each event target for an event session 6

DMVs for EE Session data created when event session is started Note: Views don’t have session data until a session starts • sys. dm_os_dispatcher_pools - Returns information about session dispatcher pools • • • sys. dm_xe_objects - Returns a row for each object exposed by an event package sys. dm_xe_object_columns - Returns the schema information for all the objects sys. dm_xe_packages - Lists all the packages registered with extended events engine • • • sys. dm_xe_sessions - Returns information about an active extended events session sys. dm_xe_session_targets - Returns information about session targets sys. dm_xe_session_events - Returns information about session events sys. dm_xe_session_event_actions - Returns information about event session actions sys. dm_xe_session_object_columns - Shows the configuration values for objects bound to a session • sys. dm_xe_map_values - Provides a mapping of internal keys to human-readable text 7

DMVs for EE Objects SELECT p. name AS package_name, o. name AS event_name, o. description FROM sys. dm_xe_packages AS p JOIN sys. dm_xe_objects AS o ON p. guid = o. package_guid WHERE (p. capabilities IS NULL OR p. capabilities & 1 = 0) AND (o. capabilities IS NULL OR o. capabilities & 1 = 0) AND o. object_type = 'event' ORDER by o. name 8

DMVs for EE Targets SELECT p. name AS package_name, o. name AS target_name, o. description FROM sys. dm_xe_packages AS p JOIN sys. dm_xe_objects AS o ON p. guid = o. package_guid WHERE (p. capabilities IS NULL OR p. capabilities & 1 = 0) AND (o. capabilities IS NULL OR o. capabilities & 1 = 0) AND o. object_type = ‘target' ORDER by o. name 9

DMVs for EE Actions SELECT p. name AS package_name, o. name AS action_name, o. description FROM sys. dm_xe_packages AS p JOIN sys. dm_xe_objects AS o ON p. guid = o. package_guid WHERE (p. capabilities IS NULL OR p. capabilities & 1 = 0) AND (o. capabilities IS NULL OR o. capabilities & 1 = 0) AND o. object_type = ‘action' ORDER by o. name 10

2012 Extended Events 11

2012 Extended Events 12

Creating Deadlock EE 13

Deadlock Event 14

Deadlock EE Actions 15

Deadlock EE Filters 16

Deadlock EE Target 17

Managing/ Viewing Sessions 18

Viewing Targets 19

Viewing Targets 20

Demonstration Examples • Deadlock Monitoring • Query Performance • Aggregate Like SQLs 21

Demo Steps • Deadlocking • Walk through the Session properties – General – Events, Actions, Filters – Targets • Start Monitor • Run dlock 1 / dlock 2 to get deadlock • Show different target examples 22

Demo Steps • Query Performance • • • Show How To Script Start Monitor Run load – Adventure. Works Show Extended Event Options Save Viewing properties for reuse – File / Open • Group Session / Duration - Filter Waits 23

Demo Steps • Aggregate Like SQLs • Group By Query Plan Hash • Sum by Duration • Sort Descending • Select * from sys. dm_exec_query_plan(@handle); 24

Summary • Extended Events are light weight – Quickly / continuously gather Performance Data – 2012 - Easy to capture, store and view data • Via Sessions, Events, Actions, Filters, & Targets – Can be used to troubleshoot issues • Replaces Sql Server Profile for Trace Capture – Deprecated in next release – Still need to use for Trace Capture of Analysis Services • Replaces Sql Trace – Stored procedures, functions and catalog views 25

About Confio • Wait-Based Performance Tools • Ignite 8 / Ignite. VM – Ignite for SQL Server, Oracle, DB 2, Sybase • Helps show which SQL to tune • Based in Colorado, worldwide customers • Free trial at www. confio. com • http: //www. ignitefree. com – Free Current View 26