1 / 23

Extended Events

Extended Events. Dave Bland daveb8782@gmail.com @SQLDave29 www.davebland.com www.linkedin.com/in/ dave -bland-SQL-Server. What are they and how can I use them. Don’t forget to thank the team that organized today…they worked hard and did a great job!!. About Me . 15 years DBA Experience

rolon
Download Presentation

Extended Events

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Extended Events Dave Bland • daveb8782@gmail.com • @SQLDave29 • www.davebland.com • www.linkedin.com/in/ dave-bland-SQL-Server What are they and how can I use them

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

  3. About Me • 15 years DBA Experience • 10 Years Application\BI 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

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

  5. 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

  6. Why Use Extended Events? Found This – Query Timeout

  7. Cool Art!!!!

  8. Time Invested Querying and Analyzing Deciding what Implement

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

  10. 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

  11. Components of Extended Events • Sessions • Events • Predicates • Actions • Targets

  12. Session • Provided sessions • system_health • Turned on by default • Starts when the SQL Server service starts • AlwaysOn_health • Turned Off by default • If using AlwaysOn • 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

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

  14. Actions Create Demo Using SSMS

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

  16. Event Fields Create Demo Using SSMS

  17. Create Demo Targets Using SSMS • 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

  18. What is Missing?

  19. Viewing Captured Data • How to View • Transact SQL • View Target data • SSMS • Merge Target Data – Under File Menu in SSMS

  20. 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

  21. Azure SQL and Extended Events

  22. Examples • 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 • Query Timeout • Attention • Deadlock • xml_deadlock_report • Failed Login • error_reported • Severity 14 • Error_number = 18456 • State = > 1 • Page Splits(SQLSkills) • transaction_log • LOP_DELETE_SPLIT

  23. Thank you! Enjoy the rest of the day!!

More Related