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 Tuning Course

Timings (IST) Free Demo Start Date
6:30 AM to 8 AM Aug 26th Aug 26th Register
10:00 AM to 11:30 AM Aug 1st Aug 1st Register
6:30 PM to 8 PM Aug 20th Aug 22nd Register
 

Modes Of Training : LIVE Online Training, Inhouse Classroom Training, Video Training. Register Today

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 Perofrmance Baselines
Memory Optimized Tables Execution Plans - indepth
 

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: PERFORMANCE MONITORING, LIVE EXECUTION PLANS

  • Memory Optimized Tables, Optimized Filegroups
  • Memory Snapshot Settings and Real-world Usage
  • Temporal Tables and SYSTEM_VERSIONING
  • Temporal Tables For DML Audits, Performance Impact
  • In-Memory Tables Creation and Index Options
  • Working with Extended Events & Performance Impact
  • LIVE Query Statistics - Monitoring Options, Metrics
  • LIVE Query Statistics - Tracing, and Baselining
  • 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
  • Creating System Versioned Temporal Tables
  • Querying and Modifications to Temporal Tables
  • Bulk Inserts, OPENROWSET with Temporal Tables
  • Tuning Bulk Inserts - Recovery Models & Logging
  • Real-world Scenarios @ ERP (LIVE) Database

DAY 2: PARTITIONS and FULL TEXT SEARCH

  • 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
  • Aligned / Indexed Partitioning and Performance
  • Data Compression Types - ROW Level, PAGE Level
  • Partition Numbers and Filtered Compression Concepts
  • Managing Partitions and Query Tuning Options
  • LIKE Operator - Limitations. Using Wild-cards
  • 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

DAY 5: 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
  • 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

DAY 3: INDEXED QUERIES, MISSING INDEXES

  • 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

DAY 6: PERFMON COUNTERS, DDM

  • 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
  • Data Migration Assistant (DMA) Tool
  • 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
  • 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