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


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

PowerPoint Slideshow about 'SQL Server 2012 New Performance Tuning Tools' - mahogony


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
slide2

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
Agenda
  • New Tools and Features Overview
  • XEvents (Extended Events)
    • Introduction
    • Practical Uses
new tools and features
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
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
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 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
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
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
XEvents Predicates
  • Filters are server-side
  • Occurences of events can be restricted
    • pred_source object types
      • counter and partitioned counter
practical uses page splits
Practical Uses: Page Splits
  • Page Splits Explained
practical uses tracking session and statement level waits
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.