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?
- 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.
- We have created training classes and the ability to custom-create Quest training courses that meet your company’s specific needs.
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.
- 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
: 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
: 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
: 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
: 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.
: 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.
: 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.
: 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.
: Deadlock Analysis
- Deadlock Fundamentals.
- Error Handling to Catch a deadlock.
- Ways to analyze the cause of a deadlock.