SQL Server Performance Tuning | Query Tuning training | SQLSchool Training Institute

SQL Server Performance Tuning | Query Tuning training | SQLSchool Training Institute

• 1.
  SQL SERVER PERFORMANCETUNING (QUERY TUNING COURSE) Applicable for Starters as well as for Working Professionals Complete Practical & Real-time Trainings A Unit of SequelGate Innovative Technologies Pvt. Ltd. > ISO Certified Training Institute > Microsoft Certified Partner Training Highlights  Complete Practical and Real-time Scenarios  Session wise Material and Practice Labs  Session wise Notes & Doubts Clarifications  Certification Material & Resume Preparation  24x7 LIVE Server Access with Real-time Databases  Interview Preparation and Guidance  Technical Support and Placements Assistance  One Real-time Project and FAQs with Answers  Mock Interview and Course Completion Certificate All Training Sessions are Completely Practical & Real-time Every session includes Study Material and Practice Material.
• 2.
  SQL SERVER (QUERY)PERFORMANCE TUNING All Our Training Sessions are COMPLETELY PRACTICAL & REALTIME with Hands-On Lab DAY 1: QUERY TUNING - CTE, JOIN OPTIONS, STATS Identifying Long Running Queries & Activity Monitor * Using Important Dynamic Management Objects (DMV, DMF) * Using DMVs, DMFs for Query Audits, Procedure Cache * Avoiding Self Joins - Real-world Scenarios * Avoiding Sub Queries with - Real-world Scenarios * Comparing Sub Queries & Joins - Performance Baselines * Using CTEs for Memory Based Query Pre-Fetch CTEs for DML and Data Retrieval Operations * CTEs with Cloning and Bulk Operations - Anchor Members, Recursive Members and Avoiding Sub Queries with CTEs * Query Tuning and Resource Optimization Options * HASH JOIN - Examples and Precautions. Usage * MERGE JOIN - Examples and Precautions. Usage * LOOP JOIN - Examples. Usage * OUTER APPLY, Hybrid and Multi - Level Joins * Comparing Execution Plans with Join Options * LIVE Execution Plans with SSMS 2016, Higher Versions * Comparing Query Costs with LIVE Execution Plans * Indexes @ Join Options - MERGE and LOOP Joins with Indexes - Usage DAY 2: PARTITIONS and PARTITIONED VIEW Big Data - Performance Considerations, Partitioning Views * Query Optimizer in Query Tuning, Table Partitions * Partition Functions and Partition Schemes * Partition Ranges, Values and Sort Orders * Partitioning Un-partitioned Tables using Indexes * Partition SPLIT and MERGE Operations - Issues * Aligned / Indexed Partitioning and Performance * Data Compression Types - ROW Level, PAGE Level Partition Numbers and Filtered Compression Concepts * Real-world Partitions with Filegroups * Managing Partitions and Query Tuning Options * Partitioned Views and Advantages - Common Myths * Partitioned Views with CHECK - Consistency * Manual Update of Column Statistics - GUI & Scripting * Role of Statistics in Query Tuning Process - Options * SET STATISTICS TIME ON and Stats Update Options * Automated Data Routing @ Partition Views * Execution Plans, Aligned / Indexed Partitions * Linked Server Connections and Remote Partitions * Using Linked Servers for Partitioned Views DAY 3: FULL TEXT SEARCH (FTS) - QUERY TUNING Full Text Search (FTS) Architecture and fdhost.exe Files * Protocols, Crawl Threads, FT Query Compilation, Execution * Stoplists & thesaurus. Filter Daemon Manager, Word breakers * MSFTESQL and MSFTEFD Threads. Population with FT Index * Stemmer, Gatherer, Index Writer and Semantic Search Objects * Full Text Search Service Activation and DB Settings. * Database Catalogs (FTC) and Storage Locations * Full Text (FT) Indexes Creation - GUI and T-SQL * Full Text Columns and Unique Key Index Preferences * Full Population and Incremental Population * Change Tracking Options with FT Indexes - Benefits * Important DMVs and DMFs with FTS Architecture * CONTAINS(), FREETEXT() Functions * Token Search, Inflectional Forms, Operators * Performance Tuning with Full Text Indexes * Using OPENROWSET with BLOB and BULKCOLUMN Data * FT Indexes on Binary Large Object Data * AND, OR, NOT OR, AND NOT, PHRASE, PREFIX, PROXIMITY. * INFLECTIONAL FORMS, MAXIMUM PROXIMITY FTS Queries * Monitoring FTS @ Dynamic Views & Functions * Fulltext Filters, Stopwords, Stemmers.
• 3.
  For registrations, pleasekindly contact us: We are available 24x7 @ +91 9666440801 / 040 64577244 [INDIA] We are available 24x7 @ +1 510.400.4845 [USA] Skype: SQL School Training Institute Email: contact@sqlschool.com DAY 4: INDEXES and B-TREE Index Internals and B Tree (Balanced Binary) Tree Structures * Root Nodes, Branch Nodes and Branch Pages Structures * GAM, SGAM Pages, Metadata Header Information * bitmap Value for Space Allocations - DBCC PAGE * bytemap Values and PFS - Page Free Space Allocation * Trace Flags for Page Allocation and Metadata * Index Fragmentation - Issues, Performance * SAMPLED and DETAILED Query Scans. FillFactor * Index Rebuilds (Online/Offline), Tuning Options * Index Reorganization Process and Advantages * Fragmentation Audit with DMVs, DMFs - Usage * Using Database Plans (DMPs) for Index Updates * Page, Row Compressions with Indexes - Cautions * Row Compressions with Read-Only Databases - Tuning * Filtered Indexes, Online Indexes, Indexes Views Filtered Indexes and Index Size Limitations * Table Statistics & Query Tuning Options * Handling Heaps, Clustered, and Nonclustered Indexes * Fill Factor, Pad Index and Query Tuning DAY 5: MEMORY OPTIMIZATION, LIVE EXEC PLANS Memory Optimized Tables, Optimized Filegroups * Memory Snapshot Settings and Real-world Usage * Working with Memory 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 * Controlling and Monitoring Execution Plans * Execution Plans: Estimated and Actual Plans * LIVE Execution Plans - Usage and Query Statistics * Algebrizer 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 6: DATABASE TUNING ADVISOR (DTA) TOOL, PERFMON DTA: Usage, Sequential / Parallel Query Tuning * DTA Tool with Profiler, Trace Tables, Cache * DTA with Audit Tables and Databases - MultiSelect * 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 with Indexes, Memory * Choosing PDS for Tuning, Memory Leaks * BLOB and BULK Operations with DTA Tool * DTA Tool - Limitations with Heaps, Transactions * Disk IO and Slow Query Issues - Perfmon * PERFMON Counters and PSSDIAG Tools * Index Management Options - SQL 2016 * Distributed Replay Controller Tool, SCOM * CPU, Thread Management and Windows Fibres * Priority Boost Settings and Windows Kernel * Performance Tuning - Checklist Activities * SEVEN STEP PROCESS for Query Tuning Operations * FIVE PHASE PROCESS for Query Tuning Operations ALL TRAINING SESSIONS ARE COMPLETELY PRACTICAL, REAL-TIME.