EXTENDED EVENTS WHAT ARE THEY AND HOW CAN

EXTENDED EVENTS WHAT ARE THEY AND HOW CAN I USE THEM Dave Bland • • daveb 8782@gmail. com @SQLDave 29 www. davebland. com www. linkedin. com/in/ dave-bland-SQL-Server

Don’t forget to thank the team that organized today…they worked hard and did a great job!!

ABOUT ME • 15 years DBA Experience • 10 Years ApplicationBI development • 19 years of teaching SQL Server • 19 years as Microsoft Certified Trainer • 5 years SQL Server Instructor at Harper College, Palatine, IL • Supervised the Shared Services DBA team for Stericycle for 4 years • Currently Data Architect at Stericycle • 13 Certifications, mostly Microsoft and Comptia

AGENDA • What are Extended Events • History of Extended Events • What can I use Extended Events for? • How to query Extended Events data

WHAT ARE EXTENDED EVENTS “Extended Events is a light weight performance monitoring system that uses very few performance resources. ” -- Microsoft. com • History • Introduced with SQL Server 2008 • Intended to replace Profiler • Used to capture information of what is happening inside the Database Engine • No GUI in 2008, but SSMS has one starting with SQL Server 2012 • Can view 2008 events with SSMS 2012 with Add-In by Jonathan Kehayias

WHY USE EXTENDED EVENTS? Found This – Query Timeout

COOL ART!!!!

TIME INVESTED Deciding what Implement Querying and Analyzing

COMMON EVENTS • Deadlocks* • Query Timeouts* • Information about waits • Poor performing queries • Column Store events • Page Splits* • Cardinality Mis-Estimates* www. sqlservercentral. com • CPU and Memory Issues • Login information* • Database file activity • Execution Plan warnings* • Capture execution plans*

HOW TO GET INFORMATION ABOUT EE USING DATA MANAGEMENT VIEWS • SELECT * FROM sys. dm_xe_sessions • SELECT * FROM sys. dm_xe_session_targets • SELECT * FROM sys. dm_xe_session_events

COMPONENTS OF EXTENDED EVENTS Sessions Predicates Events Actions Targets

SESSION • Provided sessions • system_health • Turned on by default • Starts when the SQL Server service starts • Always. On_health • Turned Off by default • If using Always. On • telemetry_xevents • SQL Server 2016 • Column Store • Stretch Tables • Collection of events, targets, actions and predicates • Determine: • What is collected • When it is collected • Where it is stored

EVENTS Create Demo Using SSMS Image from: http: //www. insidesql. org/blogs/andreaswolter/2013/11/extended-events-vs-sql-trace-comparison-top-features

ACTIONS Create Demo Using SSMS

PREDICATE • Narrows down the collection only the data that is needed • Also referred as a Filter Create Demo Using SSMS

EVENT FIELDS Create Demo Using SSMS

TARGETS • Destination for the data • Can have more than one target • Found on the “Data Storage” tab • Types • Ring_buffer • Event_File • Event_counter • Pair_matching Create Demo Using SSMS

WHAT IS MISSING?

• How to View • Transact SQL VIEWING CAPTURED DATA • View Target data • SSMS • Merge Target Data – Under File Menu in SSMS

USING TSQL TO READ DATA FROM A FILE TARGET • Use with caution • Use the sys. fn_xe_file_target_read_file function • Have. XEL extension • Steps • Dump XML into a temporary table • Parse XML • Do what you need with the data

AZURE SQL AND EXTENDED EVENTS

• Query Timeout EXAMPLES • Attention • Deadlock • xml_deadlock_report • Failed Login • error_reported • Severity 14 • Error_number = 18456 • State = > 1 • Page Splits(SQLSkills) • transaction_log • LOP_DELETE_SPLIT • Execution Plan Warnings • • missing_column_statistics missing_join_predicate plan_affecting_convert unmatched_filtered_indexes • Execution Plan Capture • • query_pre_execution_showplan query_post_execution_showplan • Cardinality Mis-estimates • large_cardinality_misestimate • Long Running Queries • sql_statement_completed • Duration – In Microseconds

Thank you! Enjoy the rest of the day!!