1 / 20

SQL Server 2012 New Performance Tuning Tools

SQL Server 2012 New Performance Tuning Tools. 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. Agenda.

mahogony
Download Presentation

SQL Server 2012 New Performance Tuning Tools

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

More Related