SQL Performance Tuning and Optimizing
=====
—–
Course Id : DATA-SQLT
Duration : 32 Hours
Overview
—–
* SQL is a relational database management system
* To have a proper SQL Server management solution in order to keep their server performances intact DB Admins is needed
* In this course you will learn how to manage and maintain SQL Server databases with the knowledge and skills to performance tune and optimize databases
Pre-Requisites
——-
* Participants should have the basic knowledge of SQL
* Knowledge of the Microsoft Windows operating system
* Working knowledge of database administration and maintenance is preferred
Training Objectives
——
* Understand architectural overview of SQL Server and its various components
* Understand the SQL Server execution model, waits and queues
* Understand core I/O concepts, Storage Area Networks and performance testing
* Understand architectural concepts and data files for user databases and TempDB
* Learn Concurrency, Transactions, Isolation Levels and Locking
* Understand concepts of the Optimizer and how to identify and fix query plan issues
* Understand troubleshooting scenarios, Plan Cache, troubleshooting strategy and usage scenarios for Extended Events
* Understand data collection strategy and techniques to analyze collected data
Course Structure
—–
* We provide more focus on hands-on in our technical courses (typically 80% hands-on/20% theory)
* Students get the capability to apply the material they learn to real-world problems
Materials Provided
—–
* PDF of slides and hands-on exercises
* Access to instance with lab environment
Software Requirements
—–
Any of the following
* Any current internet browser
* vnc client
* rdp client
Hardware Requirements
—–
* Processor: 1.2 GHz
* RAM: 512 MB
* Disk space: 1 GB
* Network Connection with low latency (<250ms) to Internet
Course Outline
## Daywise Course Outline
—–
## Day 1
—–
* Unit 1 : SQL Server Architecture, Scheduling, and Waits
* Unit 2 : SQL Server I/O
* Unit 3 : Database Structures
## Day 2
—–
* Unit 4 : SQL Server Memory
* Unit 5 : Concurrency and Transactions
* Unit 6 : Statistics and Index Internals
## Day 3
—–
* Unit 7 : Query Execution and Query Plan Analysis
* Unit 8 : Plan Caching and Recompilation
* Unit 9 : Extended Events
## Day 4
—–
* Unit 10 : Monitoring, Tracing, and Baselining
* Unit 11 : Troubleshooting Common Performance Issues
## Detailed Course Outline
—–
Unit 1 : SQL Server Architecture, Scheduling, and Waits
—–
* SQL Server Components and SQL OS
* Windows Scheduling vs SQL Scheduling
* Waits and Queues
Unit 2 : SQL Server I/O
—–
* Core Concepts
* Storage Solutions
* I/O Setup and Testing
Unit 3 : Database Structures
—–
* Database Structure Internals
* Data File Internals
* TempDB Internals
Unit 4 : SQL Server Memory
—–
* Windows Memory
* SQL Server Memory
* In-Memory OLTP
Unit 5 : Concurrency and Transactions
—–
* Concurrency and Transactions
* Locking Internals
Unit 6 : Statistics and Index Internals
—–
* Statistics Internals and Cardinality Estimation
* Index Internals
* Columnstore Indexes
Unit 7 : Query Execution and Query Plan Analysis
—–
* Query execution and optimizer internals
* Analyzing query plans
Unit 8 : Plan Caching and Recompilation
—–
* Plan cache internals
* Troubleshooting plan cache issues
* Query store
Unit 9 : Extended Events
—–
* Extended events core concepts
* Implementing extended events
Unit 10 : Monitoring, Tracing, and Baselining
—–
* Monitoring and tracing
* Baselining and benchmarking
Unit 11 : Troubleshooting Common Performance Issues
—–
* Troubleshoot CPU performance
* Troubleshoot memory performance
* Troubleshoot I/O performance
* Troubleshoot Concurrency performance
* Troubleshoot TempDB performance