SQL Server Performance Tuning (Jump Start)

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 OutlineModule 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.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan