Pragmatic SQL Server Performance Tuning (Online)

Performance tuning a SQL Server is as much an art as it is a science. Many times a server will give conflicting information about the state that it is in. This 4-day course is designed to provide SQL DBA’s with the tools and knowledge that is required to keep their SQL Servers running efficiently and reliably. Additionally, you will experience how to perform advanced performance tuning and optimization in your everyday DBA work. This course will cover topics such as Dynamic Management Views (DMV’s), Execution Plans and the Plan Cache, SQL Server Hardware and Software Best Practices, Index Tuning, Query Tuning and using tools such as Profiler, Extended Events and SQLIO.

The final module in this class (Making it real) is designed to provide information that will assist the DBA in performing performance tuning and optimization in their specific environment. We will work together to create a plan that will assist the DBA to monitor servers, alert on issues and resolve slowdowns.

IMPORTANT: When you purchase a seat in this class you also receive 2 hours of our Performance Tuning Virtual Mentor service for assistance with YOUR project work. This remote service lets you work ONE on ONE with our Performance Tuning mentors to accelerate YOUR project and further improve YOUR skills. Your Virtual Mentor hours can be used during business hours anytime during the 12 months following your class. To set up a session, download this simple questionnaire, fill it out, and send it as an attachment to mentor@pragmaticworks.com. This unique Pragmatic Works offering insures that your training will translate into real world success for YOUR projects.

All virtual training students are provided a previously recorded version of the class that are available for 7 days after the end of the class.

Outline

Day 1 - Introduction to SQL Server Performance Tuning:

  1. Introduction: “Houston, do you have a problem?”
    • Resource bottlenecks
    • Identifying issues
    • Prioritizing issues
    • Continuous process
  2. SQL Server Query Basics
    • How does a query process?
    • Query Optimizer
    • Plan Cache
    • Execution plans
  3. Baselines and Statistics
    • Value in knowing what’s ”good”
    • Performance metrics
    • SQL Server Statistics (DMO’s)
    • SQL Server Load Testing
    • Scripts & third party tools
    • Hands-on lab: Perform SQL Server load testing using PowerShell

Day 2 - Configuring a SQL Server for Optimal Performance:

  1. Hardware Environment and Configuration
    • CPU
    • Memory
    • Storage Platform
  2. Software Environment and Configuration
    • Windows Operating System
    • SQL Server Best Practices

Day 3 - SQL Server Performance Tuning Methodology:

  1. Waits and Queues Methodology
    • Basics of methodology
    • Cashier example
    • Value of methodology
    • How to monitor Wait and Queues
    • Hands-on lab: Querying DMV’s to monitor waits and queues
  2. Index Tuning
    • Index Types:
      • Clustered Indexes
      • Heaps
      • Non-Clustered Indexes
      • ColumnStore Indexes
    • Index Maintenance
    • Missing indexes
      • Database Engine Tuning Advisor
    • Duplicate Indexes
    • Hands-on lab: Perform various index tuning exercises (2 - 3)
  3. Query Tuning
    • Watching selectivity
    • Order of operations
    • Blocking
    • Deadlocks
    • Locking
    • Consistency
    • Complex solutions
    • Best Practices
    • Hands-on lab: Perform various query tuning exercises (3 - 4)

Day 4 - SQL Server Monitoring and Tools:

  1. Monitoring, Alerting and Tools
    • Perfmon
    • SQL Trace
    • Profiler
    • Extended Events
    • sp_ServerDiagnostic
    • sp_WhoIsActive
    • sp_Blitz
    • sp_BlitzIndex
    • SQLIO
    • Alerting
    • Hands-on lab: Create an XEvent session to track wait resources
  2. BI performance monitoring
    • SSIS
    • SSRS
    • SSAS
  3. Making it real
    • Phase I: Taking over a SQL Server environment
    • Phase II: Identifying your “problem children”
    • Phase III: Achieving an acceptable level of stability & reliability

Prerequisites

The target of this class is SQL Server DBA’s, developers, architects or IT professionals who have average to good T-SQL skills already. This class assumes that you have a good understanding of SQL Server fundamentals. For example, you should know and use the SQL Server Management Studio (SSMS) interface and associated toolbox items before attending this class.

SystemRequirements

  • Microsoft Windows XP Professional, Microsoft Windows Server 2003, Microsoft Vista, Windows 7 or Windows 8
  • 1 GHz or faster CPU
  • 1 GB of available RAM (recommended 2 GB)
  • 20 GB of free hard drive space
  • SQL Server Developer, Standard or Enterprise Edition 2008/2008R2/2012/2014
    • SQL Server Database Engine
    • SQL Server Management Studio
    • Follow this installation guide to ensure all items are installed correctly
  • AdventureWorks2008R2 or AdventureWorks2012 databases installed on the instance.
  • AdventureWorksDW2008R2 or AdventureWorksDW2012 databases installed on the instance.

Some of our clients