Key Features

  • Five-day training (40 hours)
  • Expert level
  • Highly exclusive content & challenges
  • 100% hands-on & demo-based
  • Based on real-world scenarios
  • Highly configured systems will be provided
  • Dedicated high speed internet will be provided
  • Available In-person only
  • Module 1:  SQL Server Architecture, Scheduling and Waits

    • SQL Server Components and SQL OS
    • Windows Scheduling vs SQL
    • Waits and Queues
    • Hackathon Activities: Troubleshoot scheduler pressure, Identify top waits and culprit queries, Fix
      queries causing maximum CPU usage, Troubleshoot overall CPU performance

    Module 2:   SQL Server I/O

    • Core Concepts
    • IO Setup and Testing
    • SQL Server IO Patterns
    • Hackathon Activities: Troubleshoot IO pressure, Identify culprit queries causing maximum IO, Fix
      queries causing maximum IO, Troubleshoot overall IO performance

    Module 3:  Database Structures

    • Database Structure Internals
    • Data File Internals
    • TempDB Internals
    • Hackathon Activities: Troubleshoot Temdb usage, Identify queries causing maximum Tempdb usage, Fix
      queries causing maximum Temdb usage, Troubleshoot overall TempDB performance

    Module 4:  SQL Server Memory

    • Windows Memory
    • SQL Server Memory
    • Hackathon Activities: Troubleshoot SQL Server Memory, Identify queries causing maximum buffer pool
      usage, Identify memory clerks with max memory consumption, Identify & tune queries with high memory
      consumption or queries causing Out Of Memory exceptions, Troubleshoot overall Memory performance

    Module 5:  Concurrency and Transactions

    • Concurrency and Transactions
    • Locking Internals
    • Hackathon Activities: Identify block chain, Troubleshoot different types of deadlocks, Re-write
      transactional code to minimize deadlocks, Identify Snapshot Isolation Temdb space consumption,
      Troubleshoot overall concurrency performance

    Module 6:  Statistics and Index Internals

    • Statistics Internals and Cardinality Estimation
    • Index Internals
    • Hackathon Activities: Identify statistics used by a query, Identify statistics metadata, Investigate
      query plan to identify poor cardinality estimation, Identify missing and unused indexes, Fix poor
      performing queries by implementing the right indexing strategies, Troubleshoot overall indexing
      performance

    Module 7:  Extended Events

    • Extended Events core concepts
    • Implementing Extended Events
    • Hackathon Activities: Implement Extended Events to capture queries causing page splits, Implement
      Extended Events to capture workloads causing maximum IO, CPU, etc., Use extended events to capture wait stats for a particular workload

    Module 8:  Monitoring, Tracing and Baselining

    • Monitoring and Tracing
    • Baselining and Benchmarking
    • Hackathon Activities: Setup baseline database and capture performance data, Use the captured
      performance data to troubleshoot common performance issues

    Module 9:  Query Execution and Query Plan Analysis

    • Query Execution and Optimizer Internals
    • Optimizing T-SQL code / Rewriting including Query Hints
    • Analyzing Query Plans
    • Hackathon Activities: Analyze problems in query plans, re-write queries for better performance and/or
      tune indexes, dive deep into operator details

    Module 10:   Plan Caching and Recompilation

    • Plan Cache Internals
    • Troubleshooting Plan Cache Issues
    • Hackathon Activities: Identify, troubleshoot & fix plan cache bloating scenarios, Identify,
      troubleshoot & fix excess compilations and re-compilations

    Module 11:  Design and Implement an Azure SQL Database

    • Index Fragmentation
    • Indexing Techniques
    • Indexing Strategy
    • Tuning Queries
    • Hackathon Activities: Identify & diagnose real-world indexing issues, Identify & troubleshoot query
      workspace memory issues
    Hands-On

    Highly configured machines will be provided to the registered participants for labs and hackathon activities

    Please feel free to contact us if you need any further information
    Email : classes@sqlmaestros.com
    Skype : classes@sqlmaestros.com
    Contact: +91-9606482133 / +91-9503124667