1 / 18

SQL Server 2012 Extended Events

SQL Server 2012 Extended Events. Agenda. SQL Server 2012 Extended Events About me New & improved Extended Events Event Tracing for Windows XEvents basics & concepts DEMO XEvents Tools. Gareth Swanepoel From South Africa originally Been in IT for 22+ years

reuel
Download Presentation

SQL Server 2012 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. SQL Server 2012 Extended Events

  2. Agenda SQL Server 2012 Extended Events About me New & improved Extended Events Event Tracing for Windows XEvents basics & concepts DEMO XEvents Tools

  3. Gareth Swanepoel • From South Africa originally • Been in IT for 22+ years • Was first a sysadmin, then a DBA • Sr. DBA Consultant with Pragmatic Works • Author - SQL 2012 Pro Admin • SQL 2014 Extended Events • eMail : GarethS@pragmaticworks.com • Twitter : @GarethSwan • Website : www.MyGareth.com • Sales : Sales@pragmaticworks.com

  4. New & improved XEvents Extended Events First introduced in SQL Server 2008 What are “events” ? What are “Extended Events” ?

  5. Event Tracing for Windows Concepts & Definitions Events Providers Consumers Controllers

  6. Extended Events

  7. Components

  8. Packages • package0 • sqlos • sqlserver • XeDkPkg • SecAudit • ucs • sqlclr • filestream

  9. Events • Wait Info • Error Reported • Lock Acquired • Statement Completed • Spinlock • FileTables • Lock deadlock • Auto stats

  10. Actions • Session ID • User Name • Reads • Writes • CPU • Call Stack • Client Process ID • Mini dump • Duration • SQL Text • SQL Stack • Plan Handle

  11. Predicates (Filters) • WHERE • Equal • Greater Than • Less Than • Not Equal • LIKE • FILTERS • AND • OR

  12. Targets • Pair Matching • Ring Buffer • Event Counter • File Target • Histogram • Event Stream

  13. XEvents Sessions

  14. XEvents - DEMO

  15. System Health Event Included by Default in SQL 2008 and up Starts When the Database Engine Starts Can Be Stopped via a Trace Flag (DON’T STOP IT) Collects info on Errors where Severity > 20, Memory Related Errors, Deadlocks, Call Stack & SQL Text for Latches > 15 Seconds, Locks > 30 Seconds, Preemptive Waits, CLR Virtual Alloc Failures, Connectivity Errors using Ring Buffer ..... and more.

  16. XEvents Tools T-SQL Tools DDL Statements (CREATE, ALTER, DROP) Catalog views (sys.server_event_sessions, etc) DMV’s (sys.dm_xe_objects, etc) System tables (trace_xe_event_map, trace_xe_action_map) Graphical Tools XEvents Session Wizard & Session UI New XEvents for SQL Server 2012 3 New XEvents (page_allocated, page_freed, allocation_failure) 2 Modified XEvents (Ring buffer events)

  17. SQL Server 2012 XEvents in Books Online: http://msdn.microsoft.com/en-us/library/bb630282(v=sql.110).aspx XEvents Activity Monitor Template bug workaround: http://sqlskills.com/blogs/jonathan/post/Workaround-for-Bug-in-Activity-Tracking-Event-Session-Template-in-2012-RC0.aspx XEvents References

  18. eMail : GarethS@pragmaticworks.com Twitter : @GarethSwan Website : www.MyGareth.com Sales : Sales@pragmaticworks.com Products Services Foundation BI productstoconvertto a Microsoft BI platform and simplifydevelopmentontheplatform. Speed development through training and rapid development services from Pragmatic Works. Helping those who do not have the means to get into information technology and to achieve their dreams. Thanks

More Related