sql server 2012 new performance tuning tools n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2012 New Performance Tuning Tools PowerPoint Presentation
Download Presentation
SQL Server 2012 New Performance Tuning Tools

Loading in 2 Seconds...

play fullscreen
1 / 20

SQL Server 2012 New Performance Tuning Tools - PowerPoint PPT Presentation


  • 312 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


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