1. SQL Server 2012 New Performance Tuning Tools

2. Introduction • Who am I? • Menzo Steinhorst Senior Premier Field Engineer SQLRAP, WS+ SQL Server Performance Tuning, WS+ SQL Server 2008 Admin, WS SSIS 2008 for DBA’s Focus area: Performance Tuning SQL Server menzos@microsoft.com

3. Agenda • New Tools and Features Overview • XEvents (Extended Events) • Introduction • Practical Uses

4. New Tools and Features • Performance Data Warehouse • Faster Spatial Performance • Paging for Result Sets • Partitioning • Faster Full Text Search • Column Store Index • Distributed Replay • XEvents

5. What is XEvents? • A general event-handling system • There since SQL Server 2008 • Foundation of auditing • Used for problem diagnosis and info gathering • Engine lives in sqlserver.exe • Consumers can be in-proc or out of proc • ETW consumer is provided • Enables correlation between SQL Server and Windows trace information. • But....classic provider. Not very useful!

6. Extended Events in SQL Server 2012 • SQL Trace (Profiler) is on the deprecated list! • See Mike Wachal blog: “Migrating from SQL Trace to Extended Events” • Extended Events are supported in SSMS • Extended Events included in SMO • Extended Events object model • Extended Events in PowerShell provider

7. SQL Server Diagnostics • SQL Server pre-2008 supports: • Performance monitor counters • Counters are cumulative • Little/no buil in filtering • SQL Profiler • Granular events • Column filters • Automated via server-side trace queues • SQL DMVs and DMFs • Detailed • No persisted info • Event Tracing for Windows (ETW) • Exposed profiler events

8. Demo XPerf

9. SQL Server 2012: Extended Events come of age! • Extended Events • Less overhead than server-side trace queues • Single event cost is ~2us on a 2GHz processor • Event and consumer agnostic • Any event can be processed by any consumer • New events can be added, immediatly useable • Allow actions to be taken when an event fires • Additional information can be included • Rich predicate system for filtering

10. Extended Events Explained • Event sessions specify • Events • The actual trigger • The payload of the event • Actions • The extra data that is collected • Targets • Where you send the data

11. Demo XEvent GUI in SQL 2012

12. XEvents Targets

13. Demo Targets

14. XEvents Predicates • Filters are server-side • Occurences of events can be restricted • pred_source object types • counter and partitioned counter

15. Demo Predicates

16. Practical Uses: Troubleshooting Blocking • Demo

17. Practical Uses: Page Splits • Page Splits Explained

18. Practical Uses: Tracking Session and Statement Level Waits • 2 Events: • wait_info • For SQLOS controlled resource • wait_info_external • Occurs when a SQLOS Task switches to preemptive mode.

19. Q & A