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-2050A Microsoft SQL Server Performance Tuning
Duration: 5 Days
Time: 09:00 am – 05:00 pm
Trainer: Hamid J. Fard – Microsoft Certified Master: SQL Server 2008
Intended for SQL Server administrators who are responsible for the performance of their database servers and installation and alsoSQL 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.
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
: 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
- Recommendation for Clustered and Non-Clustered Index
- Advanced Indexing Techniques
- Special Index Types
: Database Engine Tuning Advisor
- How the Database Engine Tuning Advisor Works
- How to use Database Engine Tuning Advisor
- The limitations of Database Engine Tuning Advisor
: Bookmark Lookup Analysis
- The Purpose of Bookmark Lookups
- Drawbacks of Using Bookmark Lookups
- Analysis of the Cause of Bookmark Lookups
- Techniques to Resolve Bookmark Lookups
: 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
- The Computation of Selectivity of a Column for Indexing
- Statistics Maintenance
- Effective Evaluation of Statistics used in a Query Execution
Module 8 : 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.
- The significance of the FILLFACTOR in helping to control fragmentation.
- How to automate the fragmentation analysis process.
: 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.
- Query plan hash and query hash as mechanisms for identifying queries to tune.
- Ways to improve the re-usability of 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.
: Query Design Analysis
- Aspects of Query design that affect performance.
- How query design use indexes effectively.
- The role of optimizer hints on query performance.
- The role of database constraints on query performance.
- Query designs that are less resource intensive.
- Query designs that use the procedure cache effectively.
- Query design that reduce network overhead.
- Techniques to reduce the Transaction cost of a query.
: 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.
- The effect of indexes on locking.
- Resolutions and Recommendations to avoid blocking.
- Techniques to automate blocking detection and information collection processes.
: Deadlock Analysis
- Deadlock Fundamentals.
- Error Handling to Catch a deadlock.
- Ways to analyze the cause of a deadlock.
- Techniques to resolve a deadlock.
: Cursor Cost Analysis
- The fundamentals of cursors.
- A cost analysis of different characteristics of cursors.
- The benefits and drawbacks of a default result set over cursors.
- Recommendations to minimize the cost overhead of cursors.
: Database Workload Optimization
- The characteristics of a database workload.
- The steps involved in database workload optimization.
- How to identify costly queries in the workload.
- How to measure the baseline resource use and performance of costly queries.
- How to apply techniques to optimize costly queries.
- How to analyze of the query optimization on the overall workload.
: Transaction Log and Tempdb
- What is Transaction Log.
- How transaction log works.
- How to configure the transaction log.
- How to truncate large transaction log.
- What is Tempdb.
- Benefits of Tempdb database to optimize queries.
- How to configure the Tempdb database.
- Monitoring the Tempdb database by GUI and DMV.
: Database File Placement
- What is I/O Subsystem.
- How to improve the I/O subsystem.
- What is RAID System.
- What is Virtualization.
- How to do Virtualization.
- Data and Log file placement.
- Data and Log file division.
: SQL Server and Processor
- How the processor and L2 cache work.
- What is thread and fiber.
- What is NUMA and how it works.
- What is Hyper-Threading technology.
- How to set CPU affinity mask and parallelism.
- How to assign a port number to a CPU.
: SQL Server Optimization Checklist
- Database Design Checklist.
- Query Design Checklist.
- Configuration Setting Checklist.
- Database Administration Checklist.
- Database Backup Checklist.