SQL Server Performance Tuning (Workshop)

The estimated reading time for this post is 3 minutes


This class will give you a wealth of practical optimization techniques that you can put into production as soon as you return to work and we ensure you understand *why* and *how* a technique works rather than just what the technique is. This usually means we will explain how SQL Server works internally while discussing a specific topic. Our aim is that you can apply what we teach you to any situation and justify your solution to your peers, management, or clients (yes, we welcome SQL Server consultants in our classes).

This course will cover SQL Server 2005 through SQL Server 2014, and is essential for all SQL Server developers, DBAs, and architects. We will answer all your questions, to whatever depth you want to go.


What we offer?

  1. Our participants enjoy a true technical, “hands-on” experience, with virtual images on each participant’s personalized computer. Our training lab utilizes our simple yet effective format of Learn it, Do it, Prove it. Every class is comprised of PowerPoint-driven lectures and “hands-on” labs.
  2. We have created training classes and the ability to custom-create Quest training courses that meet your company’s specific needs.

Course Information:

Title:             FS-2060A Microsoft SQL Server Performance Tuning (Workshop)

Duration:    2 Days

Time:            09:00 am – 05:00 pm

Trainer:       Hamid J. Fard – Microsoft Certified Master: SQL Server 2008

 

This course is intended for:

  • SQL Server administrators who are responsible for the performance of their database servers and installation.
  • SQL Server developers who are responsible for developing SQL Server queries and stored procedures.

Prerequisites:

  • Experience with SQL Server.
  • Understanding of Database concept.
  • Experience with SQL Server administration.
  • Experience with Transact-SOL Programming.
  • Knowledge of SQL Server performance tuning concept.

Course Outline

Module 1 SQL Query Performance Tuning

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

 Module 2 System Performance Analysis

  • Performance Monitor Tool
  • Analyze Hardware Resource Bottleneck
  • Retrieve Performance Monitor Data by DMV
  • Resolve Hardware Resource Bottleneck
  • Analyze the Overall Performance of SQL Server

Module 3 SQL Query Performance Analysis

  • The Basics of the SQL Server Profiler Tool
  • How to Analyze Costly Queries by SQL Server Profiler Tool
  • How to Combine baseline Measurement with Data Collected by SQL Server Profiler
  • How to Track Query Performance by DMV
  • How to Analyze Effectiveness of Index and Join Strategies for the SQL Query
  • How to Measure the Cost of SQL Query Using SQL Utilities

Module 4 Index Analysis

  • What an Index Is
  • The Benefits and Overhead of an Index
  • General Recommendation for Index Design
  • Clustered and Non-Clustered Index Behavior and Comparison

Module 5 : Statistics Analysis

  • The role of Statistics in Query Optimization
  • The importance of Statistics on Columns with Indexes
  • The importance of Statistics on Non-Indexed Columns used in Join and Filter Criteria
  • Analysis a Single-Column and Multi-Column Statistics

Module 6 : Fragmentation Analysis

  • The causes of Index Fragmentation, Including an Analysis of Page Splits.
  • The overhead costs associated with fragmentation.
  • How to analyze the amount of fragmentation.
  • Techniques used to resolve fragmentation.

Module 7 : Execution Plan Cache Analysis

  • Execution Plan generation and caching.
  • The SQL Server components used to generate an execution plan.
  • Strategies to optimize the cost of execution plan generation.
  • Factors affecting parallel plan generation.
  • How to analyze execution plan caching.

Module 8 Stored Procedure Recompilation

  • The benefits and drawbacks of Recompilation.
  • How to identify the statements causing Recompilation.
  • How to analyze the causes of Recompilation.
  • ways to avoid Recompilation.

Module 9 Blocking Analysis

  • The fundamentals of blocking in SQL Server.
  • The ACID properties of a transactional database.
  • Database locks granularity, escalation, modes and compatibility.
  • ANSI Isolation Levels.

Module 10 Deadlock Analysis

  • Deadlock Fundamentals.
  • Error Handling to Catch a deadlock.
  • Ways to analyze the cause of a deadlock.

Module 11 : SQL Server Optimization Checklist

  • Database Design Checklist.
  • Query Design Checklist.
  • Configuration Setting Checklist.
  • Database Administration Checklist.
  • Database Backup Checklist.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz