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 firstname.lastname@example.org. Agenda.
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
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 email@example.com
Agenda • New Tools and Features Overview • XEvents (Extended Events) • Introduction • Practical Uses
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
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!
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
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
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
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
XEvents Predicates • Filters are server-side • Occurences of events can be restricted • pred_source object types • counter and partitioned counter
Practical Uses: Page Splits • Page Splits Explained
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.