MS 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