SQL Server 2012 Optimization, Performance Tuning and Troubleshooting


Learn the internal architecture of SQL Server and the typical environments it operates in. Diagnose problems and optimize the performance of your SQL Server environment.


Duration: 40 hours

Level: 300


  • Configure database physical storage and filegroups
  • Manage database compression
  • Troubleshoot common virtualization problems
  • Troubleshoot and optimize locking and concurrency issues
  • Optimize and maintain statistics


  • Read, understand and troubleshoot complex execution plans
  • Detect and optimize common poorly performing coding practices
  • Optimize data loading
  • Maintain databases for optimal performance


”Very knowledgeable and good energy. Kept the class moving and engaged”

WHEN and WHERE is this course running?

What does this course cover?

The Optimization and Performance tuning course was developed in-house by SolidQ. During this five-day intensive course, you will learn the internal architecture of SQL Server and the typical environments it operates in. This will enable you to better diagnose problems and improve the performance of your SQL Server environment. You will also dive through the various bottlenecks of SQL Server, learn how to optimize them, and troubleshoot common problems.


Who is this course designed for?

  • SQL Server administrators who are responsible for monitoring, troubleshooting and optimizing database servers and installations
  • SQL Server developers who are responsible for developing efficient SQL Server queries and stored procedures


Pre-requisites: What do you need to know?

  • Experience with SQL Server
  • Understanding of database concepts
  • Experience with SQL Server administration
  • Experience with Transact-SQL programming
  • Knowledge of SQL Server Performance Tuning concepts

All our courses can be offered as a private delivery and tailored for your team's specific needs

Course outline

Module 01: SQL Server Architecture
  • SQL Server Architecture
Module 02: SQL Server CPU
  • Architecture of the CPU Subsystem
    • SMP and NUMA
    • Schedulers and Workers
    • Enhancements in SQL Server 2012
  • SQL Server Configurations
    • Affinity Mask
    • Parallelism
  • Monitoring SQL Server CPU
    • Monitoring and Thresholds
    • Tuning Methodology
Module 03: Introduction of SQL Server Memory Architecture
  • Memory Architecture and Memory Pools
  • SQL Server Configurations Affecting Memory Usage
    • Min server memory and max server memory
    • Dynamic Memory
    • AWE
  • Performance Monitoring and Tuning SQL Server Memory
    • Monitoring and Thresholds
    • Tuning Methodology
Module 04: SQL Server Files and Filegroups
  • Files and their Usage
  • Filegroups and their Usage
  • Monitoring File Activity and Size

Lab 04: Configuring and Verifying a Database File Structure

Module 05: Introduction to Disk Subsystem Terminology and Architecture
  • Disk Subsystem Terminology and Architecture
  • Disk Subsystem RAID Levels
  • Virtualization of SQL Server

Lab 05: Planning a SQL Server Storage Environment

Module 06: Physical Architecture of Databases and Storage Internals
  • Database Files
  • Data Compression

Lab 06: Working with Compression

Module 07: Transaction Log and tempdb
  • Working with the SQL Server Transaction Log
  • Transaction Log Architecture
  • Transaction Log Configuration
  • Working with SQL Server tempdb
    • Configuring tempdb
    • Monitoring tempdb

Lab 07: Troubleshooting tempdb Contention

Module 08: Monitoring and Tuning the Performance of the SQL Server Disk Subsystem
  • Pre-installation Testing
    • SQLIO
    • Iometer
  • Monitoring a Running System
    • Wait Statistics
    • File I/O Statistics
  • Monitoring and Thresholds

Lab 08: I/O Benchmarking

Module 09: Introduction to SQL Server performance tuning tools
  • Windows Performance Monitor
  • SQL Trace and SQL Server Profiler
  • Extended Events
  • DMVs and DMFs
  • Database Engine Tuning Advisor
  • SQLDiag
  • Data Collector
  • Third-party Tools

Lab 09 A: Synchronizing SQL Server Profiler and Windows Performance Monitor Data

Lab 09 B: Working with “Deep” Events

Module 10: SQL Server concurrency
  • Concurrency and Transactions
  • Isolation Levels
  • Blocking
  • Deadlocks

Lab 10 A: Working with the SNAPSHOT Isolation Level

Lab 10 B: Deadlock Monitoring

Module 11: SQL Server Locking
  • Locks
  • Latches
  • Monitoring Locks

Lab 11: Monitoring Locks

Module 12: SQL Server Data Types
  • Native Data Types
  • SQLCLR Data Types

Lab 12 A: Understanding the Impact of Improper Data Type Usage

Lab 12 B: Investigating a SQLCLR Data Type

Module 13: Statistics
  • The Need for Statistics
  • SQL Server Statistics Objects
  • Creating Statistics
  • Maintaining Statistics
  • Best Practices

Lab 13: Creating and Maintaining Statistics

Module 14: SQL Server Indexing and Index Maintenance
  • Heaps
  • Indexing Basics
    • Special Considerations
  • Special Indexes
    • Columnstore
    • XML
    • Spatial
    • FTS
    • Hierarchyid
  • Fragmentation
    • Correcting Fragmentation Issues

Lab 14: Understanding Fragmentation

Module 15: Execution Plans
  • Query Processing
  • Viewing Execution Plans
  • Plan Operators
  • Affecting Execution Plans
  • Caching and Parameterized Plans
  • What to Look For

Lab 15: Reading and Affecting Execution Plans

Module 16: Poorly Performing Coding Practices
  • Search Arguments
  • Parameter Sniffing
  • User-defined Functions
  • Business Logic in Queries
  • Iterative Data Access
  • Plan Guides
  • Query and Table Hints
  • UDFs
  • Computed Columns
Module 18: SQL Server Partitioning
  • Table and Index Partitioning
    • Introduction
    • Benefits
  • Components and Concepts
  • Partition Management
    • MERGE
    • SPLIT
    • SWITCH
  • Storage Alignment
  • Metadata

Lab 18: Table and Index Partitioning


Module 19: Optimizing Data Loading
  • Minimally Logged Operations
  • Bulk Load Mechanisms
  • Common Scenarios
  • Optimizing Bulk Load Performance
Module 20: Database Maintenance
  • Fragmentation
  • Consistency

WHEN and WHERE is this course running next?

This course may be scheduled in more than one region. Please check availability in your country.