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

SQL Server & Query Performance Training Schedules

 
Timings (IST) Free Demo Start Date Register
6:30 AM to 8 AM Sep 4th Sep 4th Register
10:00 AM to 11:30 AM Sep 15th Sep 15th Register
6:30 PM to 8 PM Aug 30th Aug 30th Register

None of the above schedules work for you? opt for On-demand, Self Paced Video Training Course

Trainer : Mr Sai Phanindra T (11+ Yrs Exp)

Dur : 6 Days (1 Hr per day)

Course Fee : INR 4000/- (USD 60)


COURSE HIGHLIGHTS
Daily Tasks Real-time Examples
LIVE Execution Plans Perf. Baselines
70-761 Certification 70-762 Certification
 

SQL Server Performance Tuning (Query Tuning):

Level 1 Tuning Options

Level 2 Tuning Options

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

  • Identifying Long Running Queries & Activity Monitor
  • Using Important Dynamic Management Objects (DMV, DMF)
  • Avoiding Self Joins - Real-world Scenarios
  • Avoding Sub Queries and Conditions - Real-world Scenarios
  • Comparing Sub Queries & Joins - Performance Baselines
  • Using CTEs for Memory Based Query Pre-Fetch
  • Query Tuning and Resource Optimization Options
  • STATISTICS - Purpose and Types. Query Tuning Options
  • Column Statistics - Creation and Usage. Advantages
  • Index Statistics - Auto Creation with Indexes, Usage
  • Manual Update of Column Statistics - GUI & Scripting
  • Role of Statistics in Query Tuning Process - Options
  • STATISTICS with Indexes and Query Conditions. Updates
  • LIVE Query Statistics (SQL Server 2016), Table Statistics
  • HASH JOIN - Examples and Precautions. Usage
  • MERGE JOIN - Examples and Precautions. Usage
  • LOOP JOIN - Examples and Precautions. Usage
  • OUTER APPLY, Hybrid and Multi - Level Joins
  • Indexes on Join Options - MERGE and LOOP Joins. Usage
  • Real-world Scenarios @ ERP (LIVE) Database

DAY 4: INDEXES and B TREE MAPS

  • Index Internals and Execution Plans
  • Understanding Execution Plans, Statistics, Cost
  • Index Fragmentation - Issues, Performance
  • SAMPLED and DETAILED Query Scans. FillFactor
  • Index Rebuilds (Online/Offline), 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 Index Size Limitations
  • Table Statistics & Query Tuning Options
  • Handling Heaps, Clustered, and Nonclustered Indexes
  • Fill Factor, Pad Index and Query Tuning
  • Memory Pages & IO Resources : Query Performance
  • MEMORY LEAKS & PAGE WAITS: Query Performance
  • LATCH WAITS and Query Performance Impact
  • LIVE Query Statistics - Monitoring Options, Metrics
  • LIVE Query Statistics - Tracing, and Baselining
  • Working with Extended Events & Performance Impact

DAY 2: PARTITIONS

  • Big Data - Performance Considerations
  • Table Partitions and Query Tuning Options
  • Partition Functions and Partition Schemes
  • Partition Ranges, Values and Sort Orders
  • Partitioning Un-partitioned Tables using Indexes
  • Partition SPLIT and MERGE Operations
  • Aligned / Indexed Partitioning and Performance
  • Data Compression Types - ROW Level, PAGE Level
  • Partition Numbers and Filtered Compression Concepts
  • Real-world Partitions with Filegroups, Advantages
  • Managing Partitions and Query Tuning Options
  • Partitioned Views and Advantages
  • Partitioned Views with CHECK Options
  • UNION and UNION ALL Performance : Partition Views
  • Automated Data Routing using Partition Views
  • Execution Plans with Aligned Partitions
  • Linked Server Connections and Remote Partitioned
  • Partition Views with Synonyms, Linked Servers
  • Using Linked Servers for Partitioned Views

DAY 5:MEMORY OPTIMIZATION, LIVE EXEC PLANS

  • Memory Optimized Tables, Optimized Filegroups
  • Memory Snapshot Settings and Real-world Usage
  • Working with Mmeory Tables and HDR Files
  • Comparing Memory Tables with CTEs and Variables
  • Memory Tables impact on VM (Virtual Memory)
  • Using Memory Optimized Tables for Query Tuning
  • In-Memory Tables Creation and Index Options
  • Temporal Tables and SYSTEM_VERSIONING
  • Temporal Tables For DML Audits, Performance Impact
  • Working with TimeStamp and SysStartTime Columns
  • AS OF and FROM TO Keywords Usage
  • GENERATED ALWAYS AS ROW START
  • GENERATED ALWAYS AS ROW END
  • Execution Plans: Estimated and Actual Plans
  • LIVE Execution Plans - Usage and Qurey Statistics
  • Algebraizer Component and Parser, Query Optimizer
  • CPU Cost, IO Cost, Operator Cost, Row Count
  • Comparing Execution Plans: XML and SQLPLAN
  • Querying Plan Handles, Procedure Tuning Techniques

DAY 3: FULL TEXT SEARCH (FTS)

  • Full Text Search (FTS) Configuration Options
  • Full Text Search Service Activation - DB Level
  • Filter Daemon Launcher Service - Purpose, Settings
  • Database Catalogs (FTC) and Storage Locations
  • Full Text (FT) Indexes for Query Tuning
  • Full Text Columns and Primary Key Index
  • Full Text Index For Searching Queries. Issues
  • Full Population and Incremental Population
  • CONTAINS() and FREETEXT() Functions
  • Token Search, Inflectional Forms, Opertors
  • Data Populations and FILESTREAM with FTS
  • Performance Tuning with Full Text Indexes
  • LIKE Operator - Limitations. Using Wild-cards
  • Collecting and Analyzing Data Using Extended Events
  • Implementing Performance Baseline Methodologies
  • Optimize the file configuration of your databases
  • Use DMVs and gather DB Performance Metrics
  • Memory Tables Versus Temp Tables/Table Variables
  • LIVE Execution Statistics, Hash Plans, Performance
  • Natively Compiled Stored Procedures, Performance
  • Tuning Bulk Inserts - Recovery Models & Logging
  • Real-world Scenarios @ ERP (LIVE) Database

DAY 6: DATABASE TUNING ADVISOR (DTA) TOOL PERFMON COUNTERS

  • 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
  • DTA Tool for Procedure Cache, Reent Queries
  • DTA Tool for Multi-Database Connections
  • Understanding PDS Options with Indexes
  • Choosing Correct Option (PDS) for Tuning
  • Resource Governor - Resource Pools - Tuning
  • Resource Workload Groups - Creation, Settings
  • LOW, HIGH, MEDIUM Priority Quries - Resources
  • Classifier Functions, Cost Based Optimization
  • Query Priority, CPU / Memory / IO Limits
  • Windows Fibres, Priority Boost, DOP Options
  • Processor Settings and Counters. Thresholds
  • Recommended Thread Counts and Fibres. Settings
  • CHANGE_TRACKING Options, Limitations
  • BLOB and BULK Operations with DTA Tool
  • DTA Tool - Limitations with Heaps, Transactions
  • OPENROWSET Queries and Correlated Queries
  • Real-world Scenarios @ ERP (LIVE) Database
  •  
  • PERFMON Counters and PSSDIAG Tools
  • Dynamic Data Masking (DDM) - Performance
  • Secured Column Access - DDM Functions
  • Impersonation Options with Data Masking
  • Index Management Options - SQL 2016
  • Distributed Replay Controller Tool, SCOM
  • 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
  • New Tuning Options - SQL Server 2016
  • Performance Tuning - Checklist Activities
 
All Classes are Instructor-Led & LIVE. Completely Practical and Real-time with Study Material, Session Notes, Tasks and 24x7 LIVE Server.
 
 
 

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer : Mr. Sai Phanindra T

 
 
 
New batch for SQL Server / T-SQL Course starts every 10 days. New batch for SQL DBA Course starts every 20 days. New batch for MSBI Course starts every 15 days.
 
SQL Server, SQL DBA, MSBI - Trainings, Placements & Job Support. 24x7 Online Lab. Register Today