Microsoft SQL Server Performance Tuning

Overview

This course is designed to give the right amount of internal knowledge and wealth of practical tuning and optimization techniques that you can put into production. The 5 day class offers a comprehensive coverage of SQL Server architecture, indexing and statistics strategies, optimize transaction log operations, tempdb and data file configuration, transactions and isolation levels, and locking and blocking. The course also teaches how to create baselines and benchmark SQL Server performance, how to analyze workload and figure out where performance problems are, and how to fix them. The course has a special focus on SQL Server I/O, CPU usage, memory usage, query plans, statement execution, parameter sniffing and procedural code, deadlocking, plan cache, wait and latch statistics, Extended Events, DMVs and PerfMon

Duration

The course is run 5 days full time.

Technical Skill

The primary audience for this course is individuals who administer and maintain SQL Server databases and are responsible for optimal performance of SQL Server instances that they manage. These individuals also write queries against data and need to ensure optimal execution performance of the workloads.

In addition to their professional experience, students who attend this training should already have the following technical knowledge:

• Basic knowledge of the Microsoft Windows operating system and its core functionality.
• Working knowledge of database administration and maintenance
• Working knowledge of Transact-SQL.

Private Training

This course is offered only privately. The course can be run onsite or on our site. The course can be run from our office in Cape Town or Johannesburg. The course price is R12 599 onsite and R17 500 on our premises. There is no set date to run the course, we schedule the course on dates that suits your team. A minimum of 4 delegates is required to run the course.

Course Curriculum

Performance Tuning Overview

The Performance-Tuning Process
Performance vs. Price
Performance Baseline
Where to Focus Efforts
SQL Server Performance Killers

SQL Perfomance Analysis

Performance Monitor Tool
Dynamic Management Views
Hardware Resource Bottlenecks
Memory Bottleneck Analysis
Memory Bottleneck Resolutions
Disk Bottleneck Analysis
Disk Bottleneck Resolutions
Processor Bottleneck Analysis
Processor Bottleneck Resolutions
Network Bottleneck Analysis
Network Bottleneck Resolutions
SQL Server Overall Performance
Creating a Baseline
System Behavior Analysis Against Baseline

SQL Query Performance Analysis

Extended Events Wizard
Extended Events Automation
Extended Events Recommendations
Other Query Performance Metrics Methods
Costly Queries Execution Plans

Index Analysis

What Is an Index?
Index Design Recommendations
Clustered Indexes
Nonclustered Indexes Clustered vs. Nonclustered Indexes Advanced Indexing Techniques
ColumnStore Indexes
Special Index Types
Additional Characteristics of Indexes

Database Tuning Advisor

Database Engine Tuning Advisor Mechanisms
Database Engine Tuning Advisor Examples
Database Engine Tuning Advisor Limitations

Bookmark Lookup Analysis

Purpose of Bookmark Lookups
Drawbacks of Bookmark Lookups
Analyzing the Cause of a Bookmark Lookup
Resolving Bookmark Lookups

Statistics Analysis

The Role of Statistics in Query Optimization
Statistics on an Indexed Column
Statistics on a Nonindexed Column
Analyzing Statistics
Statistics Maintenance
Analyzing the Effectiveness of Statistics

Index Fragmentation Analysis

Causes of Fragmentation
Fragmentation Overhead
Analyzing the Amount of Fragmentation
Fragmentation Resolutions
Significance of the Fill Factor
Automatic Maintenance

Execution Plan Cache Analysis

Execution Plan Generation
Execution Plan Caching
Components of the Execution Plan
Aging of the Execution Plan
Analyzing the Execution Plan Cache
Execution Plan Reuse
Query Plan Hash and Query Hash
Execution Plan Cache Recommendations

Query Recompilation

Benefits and Drawbacks of Recompilation
Identifying the Statement Causing Recompilation
Analyzing Causes of Recompilation
Avoiding Recompilations

Query Design Analysis

Query Design Recommendations
Operating on Small Result Sets
Using Indexes Effectively
Avoiding Optimizer Hints
Using Domain and Referential Integrity
Avoiding Resource-Intensive Queries
Reducing the Number of Network Round-Trips
Reducing the Transaction Cost

Blocking Analysis

Blocking Fundamentals
Understanding Blocking
Locks
Isolation Levels Effect of Indexes on Locking
Capturing Blocking Information
Blocking Resolutions
Recommendations to Reduce Blocking
Automation to Detect and Collect Blocking Information

Deadlock Analysis

Deadlock Fundamentals
Using Error Handling to Catch a Deadlock
Deadlock Analysis
Avoiding Deadlocks

Cursor Cost Analysis

Cursor Fundamentals
Cursor Cost Comparison
Default Result Set
Analyzing SQL Server Overhead with Cursors
Cursor Recommendations

Database Performance Stress Testing

Database Stressing with JMeter
Replaying SQL Scripts with JMeter
Performance Testing Overview
Capturing Data with the Server Side Trace Distributed Replay for Database Testing
Summary and SQL Server Optimization Checklist