VIDEO 1: PERFORMANCE TUNING Demo, Indexes
- Need for Performance Tuning (Query Tuning)
- SQL Server Query Tuning Operations - Plan
- SQL Server Versions and Editions - Course Plan
- Query Parse, Compilation and Query Executions
- Query Optimizer [QO] Component Overview
- TDS Packets - Verification and SSMS Options
- Query Performance Tuning Factors in SQL Server
- Query Memory Settings and Server Properties
- Day Wise Course Plan - Performance Tuning Course
- Indexes: Architecture and Types Of Indexes
- Clustered Indexes and Non Clustered Indexes
- Included Index, ColumnStore Index, Filtered Indexes
- COVERING Indexes and UNIQUE Column Indexes
- LIVE Online Indexes in Real-time - Practical Use
- B Tree Structures and Index Access Management (IAM)
- Branch Level Pages and Leaf Level Pages in Indexes
- Tuning Join Queries and Query Level Conditions
|
VIDEO 4: QUERY TUNING - INDEX MANAGEMENT, LOCKS
- Index Management Concepts for Performance Tuning
- Internal Fragmentation and External Fragmentation
- Fragmentation Audits : DMFs and Threshold Values
- Index Reorganization and Index Rebuild Options
- Database Maintenance Plans (DMP) For Index Reorg
- Compress Large Objects, Fragmentation Condition
- Index Page Count Condition, Index Usage Condition
- Fast, Sampled and Detailed Scan Types for Indexes
- Statistics Updates : Full Scan Options, DMP Jobs
- LOCKS : Mechanism, Types, Concurrency Control
- Lock Types: X, S, IS, IX, U, MD, Sch-M and Sch-S
- Lock Audits : SP_WHO2, SP_LOCK, sysprocesses
- Deadlock Simulation, Deadlock Prevention Scenarios
- Deadlock Audits and Lock Events in Profiler Tool
- Isolation Levels - ReadCommitted, Read Uncommitted
- Serializable, Snapshot, Repeatable Read Isolations
- Read Committed Snapshot Isolation Level in Real-time
|
VIDEO 2: QUERY TUNING - QUERY AUDITS, PARTITIONS
- Audit Long Running Queries using DMVs and DMFs
- Activity Monitor Tool and Query Statistics Reports
- Logical I/O, Physical I/O and Database I/O, Wait Time
- Recent Expensive Queries & Active Expensive Queries
- Plan Handle and Execution Time - Query Usage Audits
- Factors Impacting the Query Executions, Performance
- Resumable Indexes, Usage in SQL Server 2017 & 2019
- ONLINE, RESUME, PAUSE, MAX_DURATION Options
- Query Store - Settings and Advantages. Options
- PARTITIONS Mechanism : Advantages, Performance
- Database Filegroups Usage with Partition Ranges
- Partition Functions and Partition Schemes - Usage
- Partitioning Un-partitioned Tables using Indexes
- Aligned / Indexed Partitions - Query Importance
- Partition SPLIT and Partition MERGE, NextUsed
- Partition Compression Techniques : ROW, PAGE
- Data Archival & SWITCH Partitions. Partitioned Views
|
VIDEO 5: QUERY TUNING 4 - TUNING TOOLS, PERFMON
- Tuning Tools : Creating Workload Files and Trace Files
- SQL Profiler Tool - Tuning Template and TSQL / SP Events
- DTA Tool with Profiler Trace Files: Tuning Recommendations
- DTA with Query Cache (Procedure Cache) & .SQL File Inputs
- Execution Plans - Internals. Actual, LIVE Execution Plans
- Understanding the Query Execution Plans, Cost Analysis
- Plan Types : Index Scan, Index Seek, Tables Scan, Spooling
- Query Costs : IO, CPU Cost, SubTree Cost, Operator Cost
- NUMA Nodes, Boost SQL Priority, Thread Count, IO Affinity
- Perfmon Counters and Real-time Tracking Of Resources
- Processor, Disk, Memory, Transactions, Database Counters
- Using Perfmon for Big Query Audits. Free & Total Memory
- Longest Running Transactions & Transactions Per Second
- Database Log Space - Issues, Solutions. Log Rebuilds
- TempDB Space Issues - Reasons, Cautions and Solutions
- Memory Issues - Reasons, Buffer Cache and Solutions
- Performance Tuning - Final Checklist and Precautions
|
VIDEO 3: QUERY TUNING 3 - FULL TEXT SEARCH, TABLE TYPES
- Full Text Search (FTS) Mechanism - Architecture, Tuning
- Stop Words, Stemmer and Thesaurus For FullText Queries
- Indexer Program, Query Processor & FT Query Compilation
- Database Catalogs (FTC) and FDHost.exe. Daemon Threads
- Full Text (FT) Indexes for Query Tuning with Tokenization
- Crawler Threads and User Tables. Filter Daemon Host Exe
- CONTAINS() Queries and FREETEXT() Queries with SELECT
- In-Memory Tables : Creation and Practical Usage for Tuning
- Memory Snapshots and Database Level and Table Level
- FileStream Files and Memory Snapshot Filegroups for MOT
- MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT Settings
- Manual Table Stats Updates with FullScan, NoRecompute
- Temporal Tables : Real-time Usage and History Tracking
- Statistics : Purpose, Creation Scenarios and Usage
- Index Statistics and Column Statistics for Tables, ReUse
|
Real-time Case Study on Performance Tuning
This Case Study includes 24x7 LIVE Access to our remote SQL Server Instance
hosting Big Data Sources in a Virtual Machine hosted in Azure Cloud.
The participant would be provided with specific tables and data access. Need to implement
SQL Server Query Tuning [Performance Tuning] on the below concepts:
- 1. Query Audits
- 2. Table Partitions
- 3. Index Partitions
- 4. Index Management
- 5. Partitions Management
- 6. Stistics Management
- 7. Execution Plan Inernals
- 8. Perfmon Tools & Query Resources
- 9. Spooling, Threads and Node Priority
|
