By actively participating in this course, you will learn about the following:
- Describe the high level architectural overview of SQL Server and its various components.
- Describe the SQL Server execution model, waits and queues.
- Describe core I/O concepts, Storage Area Networks and performance testing.
- Describe architectural concepts and best practices related to data files for user databases and TempDB.
- Describe architectural concepts and best practices related to Concurrency, Transactions, Isolation Levels and Locking.
- Describe architectural concepts of the Optimizer and how to identify and fix query plan issues.
- Describe architectural concepts, troubleshooting scenarios and best practices related to Plan Cache.
- Describe architectural concepts, troubleshooting strategy and usage scenarios for Extended Events.
- Explain data collection strategy and techniques to analyze collected data.
- Understand techniques to identify and diagnose bottlenecks to improve overall performance.
Before attending this course, students must have:
- Experience of building and managing database, data warehouse, and business intelligence (BI) solutions with SQL Server 2014.
- Familiarity with the Windows Server 2012 R2 operating system and networking.
- Familiarity with Microsoft Excel and Microsoft SharePoint Server 2013.
Module 1: SQL Server Architecture, Scheduling, and Waits
- SQL Server Components and SQL OS
- Windows Scheduling vs SQL Scheduling
- Waits and Queues
- Lab : SQL Server Architecture, Scheduling, and Waits
Module 2: SQL Server I/O
- Core Concepts
- Storage Solutions
- I/O Setup and Testing
- Lab : Testing Storage Performance
Module 3: Database Structures
- Database Structure Internals
- Data File Internals
- TempDB Internals
- Lab : Database Structures
Module 4: SQL Server Memory
- Windows Memory
- SQL Server Memory
- In-Memory OLTP
- Lab : SQL Server Memory
Module 5: Concurrency and Transactions
- Concurrency and Transactions
- Locking Internals
- Lab : Concurrency and Transactions
Module 6: Statistics and Index Internals
- Statistics Internals and Cardinality Estimation
- Index Internals
- Columnstore Indexes
- Lab : Statistics and index Internals
Module 7: Query Execution and Query Plan Analysis
- Query execution and optimizer internals
- Analyzing query plans
- Lab : Query execution and query plan analysis
Module 8: Plan Caching and Recompilation
- Plan cache internals
- Troubleshooting plan cache issues
- Query store
- Lab : Plan caching and recompilation
Module 9: Extended Events
- Extended events core concepts
- Implementing extended events
- Lab : Extended events
Module 10: Monitoring, Tracing, and Baselining
- Monitoring and tracing
- Baselining and benchmarking
- Lab : Monitoring, Tracing and Baselining
This training course provided by Skilltec is accredited through Global Knowledge Training Ltd. Global Knowledge Training Ltd are the authorised learning partner; all trademarks and partner statuses are provided through them.