Performance Tuning (Query Tuning) Video Training at SQL School Training Institute

[Sql_School_Training]

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

 

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

No Of Videos: 6 (1 Hr per video)

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):

Brochure Download 

 

CHAPTER 1 : QUERY AUDITS, QUERY TUNING - JOIN OPTIONS, CTES  Identifying Long Running Queries & Activity Monitor  Using Important Dynamic Management Objects (DMV, DMF)  Using Query Statistics and Cache Plans / Execution Plans  Using CROSSAPPLY and Other Operators with Dynamic Objects  Avoiding Self Joins - Real-world Scenarios  Avoiding Sub Queries and Conditions - Real-world Scenarios  Comparing Sub Queries & Joins - Performance Baselines Stored Procedures for Parameterized CTE (Sub) Queries  Using CTE for Table Data Operations - DML & Retrieval  CTE for DML and DDL Operations in Stored Procedures  Using Recursive CTEs and Self Joins with Stored Procedures  Precautions for Recursive CTEs - Performance Impact  Query Tuning Operations with CTEs. Query Store Options  CTE Advantages and Limitations - Precompilations ANCHOR Members and RECURSIVE Members. Termination  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 @ Joins, Join Options

CHAPTER 2 : PARTITIONS and STATISTICS  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  Partitioned Views and Concolation Options  Table Archival Process and Partition Split/Merge  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

CHAPTER 3 : FULL TEXT SEARCH (FTS) & FT Indexes  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, Operators  Data Populations and FILESTREAM with FTS  Performance Tuning with Full Text Indexes CONTAINSTABLE and FREETEXTTABLE with FTS  Real-world Performance Considerations with FTS

CHAPTER 4 : INDEX INTERNALS AND DTA TOOL  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 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, Recent Queries  DTA Tool for Multi-Database Connections  Understanding PDS Options with Indexes

CHAPTER 5 : MEMORY 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

CHAPTER 6 : PERFMON COUNTERS, MEMORY OPTIONS @ TUNING  PERFMON Counters and MSDTC Service  Memory Pages & IO Resources : Query Performance  MEMORY LEAKS & PAGE WAITS: Query Performance  LATCH WAITS and Query Performance Impact CPU, Thread Management and Windows Fibres  Working with Machine Code @ SQL Server 2016  Resource Governor - Resource Pools - Tuning  Resource Workload Groups - Creation, Settings LOW, HIGH, MEDIUM Priority Queries - Resources  Classifier Functions, Cost Based Optimization  Query Priority, CPU / Memory / IO Limits  Windows Fibres, Priority Boost, DOP Options  Processor Settings and Counters. Thresholds  Cached Plans and Memory Store for Stored Procedures  Performance Tuning - Checklist Activities

 

Register for free demo @ http://sqlschool.com/SQL-Performance-Tuning.html

Contact us: +91 9666 44 0801 [India] | 001 510 400 4845 [USA}