Using extended events for troubleshooting sql server

Using extended events for troubleshooting sql server

• 1.
  CHAPTER Using Extended Events forTroubleshooting SQL Server Antonios Chatzipavlis SQLschool.gr Founder, Principal Consultant SQL Server Evangelist, MVP on SQL Server June 4, 2015
• 2.
  I have beenstarted with computers. I started my professional carrier in computers industry. I have been started to work with SQL Server version 6.0 I earned my first certification at Microsoft as Microsoft Certified Solution Developer (3rd in Greece) and started my carrier as Microsoft Certified Trainer (MCT) with more than 20.000 hours of training until now! I became for first time Microsoft MVP on SQL Server I created the SQL School Greece (www.sqlschool.gr) I became MCT Regional Lead by Microsoft Learning Program. I was certified as MCSE : Data Platform, MCSE: Business Intelligence Antonios Chatzipavlis Database Architect SQL Server Evangelist MCT, MCSE, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCA, ITIL-F 1982 1988 1996 1998 2010 2012 2013 CHAPTER
• 3.
  Follow us insocial media Twitter @antoniosch / @sqlschool Facebook fb/sqlschoolgr YouTube yt/user/achatzipavlis LinkedIn SQL School Greece group Pinterest pi/SQLschool/
• 4.
  help@sqlschool.gr
• 5.
  Extended Events wasintroduced in SQL Server 2008 as a new method of collecting diagnostic data from SQL Server
• 6.
  • It’s theFUTURE • SQL Trace is a deprecated feature in SQL Server 2012 • This makes understanding XE crucial to supporting SQL Server in the future • Less overhead • Lightweight to minimize impact • Provides minimum schema of data that is specific to the event being fired • Events are filtered early in the firing lifecycle based on the predicates • Flexibility and Power • Allows complex configurations for event collection that simplify problem identification. • Many events in more recent releases Why Extended Events?
• 7.
  • Events MappingQuery • Column to Action Mapping Query Switch from SQL Trace to XEvents select xe.xe_event_name,st.name from sys.trace_xe_event_map as xe inner join sys.trace_events as st on xe.trace_event_id = st.trace_event_id; select xe.xe_action_name, tc.name from sys.trace_xe_action_map as xe inner join sys.trace_columns as tc on xe.trace_column_id = tc.trace_column_id;
• 8.
  • Sessions • Area functional boundary for configuration of events • Events • Correspond to well-know points of code • Predicates • Boolean expressions that define the conditions required for an event to actually fire • Actions • Actions only execute after predicate evaluation determines the event will fire • Targets • Targets are event consumers Extended Events Architecture
• 9.
  Event Life Cycle Eventpoint encountered in code Is Event Enabled in a session Code Continues Buffer Data for Asynchronus Targets Send to Synchronous Targets Immediately Execute Actions and Collect data (if applicable) Are there configurable columns Collect non-configurable column data Passes Filter Criteria (Predicate) Collect Configurable Column data No No Yes Yes Yes
• 10.
  • Event counter •Counts all specified events that occur during an Extended Events session. • Use to obtain information about workload characteristics without adding the overhead of full event collection. • This is a synchronous target. • Event file • Use to write event session output from complete memory buffers to disk. • This is an asynchronous target. Targets - 1
• 11.
  • Event pairing •Many kinds of events occur in pairs, such as lock acquires and lock releases. • Use to determine when a specified paired event does not occur in a matched set. • This is an asynchronous target. • Event Tracing for Windows (ETW) • Use to correlate SQL Server events with Windows operating system or application event data. • This is a synchronous target. Targets - 2
• 12.
  • Histogram • Useto count the number of times that a specified event occurs, based on a specified event column or action. • This is an asynchronous target. • Ring buffer • Use to hold the event data in memory on a first-in first-out (FIFO) basis, or on a per-event FIFO basis. • This is an asynchronous target. Targets - 3
• 13.
  DEMO CHAPTER Use case scenariosof Extended Events
• 14.
  SQL Server 2016
• 15.
  • Live QueryStats (LQS) • Query Store SQL Server 2016
• 17.
  Thank you
• 18.
  SELECT KNOWLEDGE FROM SQL SERVER http://www.sqlschool.gr Copyright ©2015 SQL School Greece