Performance Tuning Training Classes
 
 

Performance Tuning (Query Tuning)

This impeccable Query Tuning course is exclusively designed for SQL Developers, MSBI Developers and Database Consultants. Course includes Mid Level to Advanced Query Tuning Techniques including Memory Optimized Tables, Temporal Objects, LIVE Query Stats, Partitions, Stats, DTA, Index Plans, Execution Plans, DDM, SQL Buffer Space, In-Memory, Lookup Options, Query Store, Dynamic Objects, DOP, Query Priority Settings, more...!


Performance Tuning - LIVE Online Training Course

Completely Practical and Real-time. Total of 8 hours sessions.

Pre-requisites: Participant needs to complete our SQL Server T-SQL or MSBI (SSIS/SSAS/SSAS) courses.

Training Type: 1. LIVE Online Training
  2. Inhouse Classroom Training

 

Available Schedules : 6 30 AM / 11 AM / 6:30 PM (IST). Register Today
Trainer: Mr Sai Phanindra T (11+ Yrs Exp)


* 1 Mock Interview *1 Real-time Project

* 24x7 LIVE Server Access * 2.1 TB Database

* Power Shell Integration * MS Azure Integration


Dur: 8 hrs       Course Fee: INR 3500 (USD 50)
 
Level 1 Tuning Options - Dur: 4 Hours
Level 2 Tuning Options - Dur: 4 Hours

Hour 1: QUERY TUNING - CTE, JOIN OPTIONS, STATS

  • LIVE Query Statistics and Table Statistics
  • Sub Queries Versus Joins. Use of CTEs & Memory
  • Using CTEs for Memory Based Query Pre-Fetch
  • Avoiding Self Joins, Options. Real-world Scenarios
  • Query Tuning and Resource Optimization Options
  • HASH JOIN - Examples and Precautions
  • MERGE JOIN - Examples and Precautions
  • LOOP JOIN - Examples and Precautions
  • JOIN Options - Performance Comparison
  • Real-world Scenarios @ ERP (LIVE) Database

Hour 5: MEMORY TABLES, LIVE EXECUTION PLANS

  • Memory Optimized Tables, Temporal Data Access
  • In-Memory Tables Creation and Index Options
  • Memory Tables Versus Temp Tables/Table Variables
  • LIVE Execution Statistics, Hash Plans, Performance
  • Natively Compiled Stored Procedures, Performance
  • Creating System Versioned Temporal Tables
  • Querying and Modifications to Temporal Tables
  • Bulk Inserts, OPENROWSET with Temporal Tables
  • Tuning Bulk Inserts - Resource Allocations
  • Real-world Scenarios @ ERP (LIVE) Database

Hour 2: INDEXED QUERIES, STATISTICS, MISSING INDEXES

  • Index Internals and Execution Plans
  • Index Fragmentation - Issues, Performance
  • SAMPLED and DETAILED Query Scans. FillFactor
  • Index Rebuilds, Query Tuning Options
  • Index Reorganization Process and Advantages
  • Page, Row Compressions with Indexes - Cautions
  • Filtered Indexes, Online Indexes, Indexes Views
  • GAM, SGAM Pages, Metadata Header Info
  • Filtered Indexes and Size Limitations
  • Analyzing Work Load Tables and Scans
  • Table Statistics & Query Tuning Options
  • Statistics Role in Query Tuning Process

Hour 6: DATABASE TUNING ADVISOR (DTA) TOOL, DOP

  • DTA: Usage, Sequential / Parallel Query Tuning
  • DTA Tool with Profiler, Trace Tables, Cache
  • Understanding Workload Files & Tables in Profiler
  • SQL Profiler Tuning and Tuning Templates
  • Database Tuning Advisor (DTA) - Usage
  • Understanding PDS Options with Indexes
  • Choosing Correct Option (PDS) for Tuning
  • Resource Governor - Resource Pools - Tuning
  • Classifier Functions, Cost Based Optimization
  • Query Priority, CPU / Memory / IO Limits
  • Windows Fibres, Priority Boost, DOP Options
  • Real-world Scenarios @ ERP (LIVE) Database

Hour 3: PARTITIONS and DATA COMPRESSIONS

  • Big Data - Performance Considerations
  • Table Partitions & Query Tuning Options
  • Partition Functions and Partition Schemes
  • Partition Ranges, Values and Sort Orders
  • Partitioned Data, Long Running Queries
  • Partitioning Un-partitioned Structures
  • Aligned Partitioning and Performance
  • Fill Factor, Pad Index and Query Tuning
  • Statistics with Table & Index Partitions
  • Statistics for Query Conditions, Audits
  • Data Compression Types and Query Tuning
  • ROW & PAGE Compressions. Differences
  • Performance Issues - Data Compressions
  • Partition Numbers, Filtered Compressions
  • Managing Partitions and Tuning Options

Hour 7: MEMORY & RESOURCES - PERFORMANCE IMPACT

  • System Monitor Tool & Long Running Queries
  • Activity Monitor Tool For CPU & Disk IO
  • PERFMON Counters and PSSDIAG Tools
  • Tempdb Space Issues and Performance Impact
  • Log Space Issues and Performance Impact
  • Memory Pages & IO Resources : Query Performance
  • MEMORY LEAKS & PAGE WAITS: Query Performance
  • LATCH WAITS and Query Performance Impact
  • Tuning Data Loads : OLTP to Stage, DWH, OLAP
  • Long Running Queries and Dynamic Views : RCA
  • Query Handle and Plan Handle Audits, IO Options
  • Understanding Query Statistics - Tuning Options
  • New Query Tuning Features in SQL Server 2016
  • Database Reports and Server Dashboards
  • One Real-time Project Implementation

Hour 4: FULL TEXT INDEXES (FTS)

  • LIKE Operator - Limitations & Wild-cards
  • Full Text Search (FTS) Configuration
  • Full Text Search Service and Importance
  • Filter Daemon Launcher Server - Options
  • Database Catalogs (FTC) and Storage
  • Full Text (FT) Indexes for Query Tuning
  • Full Text Columns and Primary Index
  • Full Text Index and Searching Queries
  • Full Population, Incremental Population
  • CONTAINS() and FREETEXT() Functions
  • Token Search, Inflectional Forms Search
  • Manual Data Populations, Index Loads
  • Backups, Restores with FT Catalog Files
  • CHANGE_TRACKING Options, Limitations
  • Securing FT Catalogs and FT Indexes
  • Performance Tuning with FT Indexes

Hour 8: SQL Server 2016 - New Tuning Options

  • Temporal Tables & SYSTEM_VERSIONING
  • PERFMON Counters and PSSDIAG Tools
  • Secured Column Access - DDM Functions
  • Index Management Options - SQL 2016
  • Distributed Replay Controller Tool, SCOM
  • Data Migration Assistant (DMA) Tool
  • Using DMA Tool for DB Analysis, Upgrades
  • Server and Database Analysis, SSIS
  • PSSDIAG Tool - Performance Monitoring
  • SQL Browser Server - TCP IP, Trace Flags
  • CPU, Thread Management and Windows Fibres
  • Priority Boost Settings and Windows Kernel
  • Working with Machine Code @ SQL Server 2016
  • Non-TSQL queries in SSMS - Performance Benefits!!
  • Index Management Options - SQL Server 2016
  • Performance Tuning - Checklist Activities

Register Today

 

All Classes are Instructor-Led & LIVE. Completely Practical and Real-time with Study Material, Session Notes, Tasks and 24x7 LIVE Server.

 

Trainer : Mr. Sai Phanindra T, 11+ Yrs of Experience exclusively on SQL Server, SQL DBA, more..

Training Reviews

Register Now

 

Register Today:

 

 

Job-Oriented Real-time Training @ SQL School Training Institute


SQL Server & T-SQL
SQL Server DBA
MSBI (SSIS, SSAS, SSRS)
 

Register Today

 

SSIS (with ETL, DWH)
SSAS (with MDX, DAX)
SSRS & Power View
 

Register Today

 

Register Today For Free Demo