SQL Power Tools - Enhance Your Effectiveness with Extended Events

1. DBI410 SQL Power Tools - Enhance Your Effectiveness with Extended Events boB ‘The Tool Man’ Taylor MCA, MCM, MCSD.Net, MCT Microsoft Corporation

2. Blogs.msdn.com /bobtaylor bob.Taylor@microsoft.com boB ‘The Tool Man’ Taylor @SQL_MCA_MCSM Tweeting? Use #TechEd and #DBI410 hash tags

3. Why are you here today? A. I am a SQL Server DBA B. I am a SQL Server Developer C. I am an application developer D. The title just sounded cool E. All the above

4. Which tools do you use on a regular basis? A. SQL Server Trace B. SQL Server Profiler C. SQLDiag D. RML Utilities E. SQL Nexus F. All of the above What do they have in common? They all use SQL Server Trace

5. They’ve taken away your tools! • SQL Profiler • SQL Trace • SQL Diag • RML Utilities • SQL Nexus Now what do you do?

6. Don’t Despair • Good News – New tools are available! • Well… not totally new • Complete engine coverage • Full Tool Support • Bad News – There is a learning curve • Not as steep as it used to be • Good News – You will learn these new tools today!

7. Extended Events Refresher • Real-time data capture • No performance penalty • Based on Event Tracing for Windows (ETW) • Full programmability support

8. Extended EventsObject Model Events Types • Packages • Events and Actions • Filters and Predicates • Sessions • Targets Package(s) Targets Actions Maps Predicates Channels Module

9. Packages in SQL Server 2012

10. System Sessions • system_health • AlwaysOn_health

11. What does that give me? • Errors reported • Non-yielding schedulers • Deadlocks • Internal and external wait info • sp_server_diagnostics output • Persisted to file or in memory

12. Viewing a real-time snapshot of system_health • SELECTCAST(xet.target_dataasxml) • FROMsys.dm_xe_session_targetsxet • JOINsys.dm_xe_sessionsxe • ON (xe.address=xet.event_session_address) • WHERExe.name='system_health'

13. demo Extended Events Profiler boB ‘The Tool Man’ Taylor MCA, MCM, MCSD.Net, MCT Microsoft Corporation

14. What other things can you look at? • sqlserver package • Page splits – see Jonathan Kehayias blog • auto_stats • begin / end transaction • Over 500 different items! • sqlos package • wait_info • wait_info_external • spinlock_backoffs • Sixty different items

15. But what about your other tools? • RML Utilities created by SQL Server escalation engineers • Those same engineers drove the UI in Management Studio • The Top 10 by CPU or Duration are there out of the box!

16. demo RML Type Reporting boB ‘The Tool Man’ Taylor MCA, MCM, MCSD.Net, MCT Microsoft Corporation

17. What about all my Profiler traces definitions? • Almost every Event and column available can be converted into Extended Event equivalents • 1. Create a new session • 2. Look up every trace event and column and convert to events and actions – add to your new session • 3. Determine if any filters that exists and add those as predicates • 4. Add target(s)

18. CREATEEVENTSESSION[SampledQueries]ONSERVER ADDEVENTsqlserver.error_reported( ACTION(sqlserver.client_app_name,sqlserver.database_id, sqlserver.query_hash,sqlserver.session_id) WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5)))AND([package0].[greater_than_uint64]([sqlserver].[database_id],(4))))AND([package0].[equal_boolean]([sqlserver].[is_system],(0))))), ADDEVENTsqlserver.sql_batch_completed( ACTION(sqlserver.client_app_name,sqlserver.database_id, sqlserver.query_hash,sqlserver.session_id) WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5)))AND([package0].[greater_than_uint64]([sqlserver].[database_id],(4))))AND([package0].[equal_boolean]([sqlserver].[is_system],(0))))) ADDTARGETpackage0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO

19. Am I serious? • That is a lot of work • It is if you attempt to do this manually • What we need is a tool! • boB ‘The Tool Man’ Taylor is happy to announce…

20. announcing SQLPIE on CodePlex http://SQLPie.CodePlex.com

21. demo Profiler Into Events (PIE) boB ‘The Tool Man’ Taylor MCA, MCM, MCSD.Net, MCT Microsoft Corporation

22. Conclusion • They have taken away your old tools! • You don’t care! • The new tools are better anyway! • Call to action • Start using Extended Events today!

23. Time for Questions!

24. Track Resources Hands-On Labs @sqlserver @ms_teched SQL Server 2012 Eval Copy Get Certified! mva Microsoft Virtual Academy

25. Resources Learning TechNet • Connect. Share. Discuss. • Microsoft Certification & Training Resources http://northamerica.msteched.com www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet http://microsoft.com/msdn

26. Required Slide Complete an evaluation on CommNet and enter to win!

27. MS Tag Scan the Tag to evaluate this session now on myTechEd Mobile

28. © 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.