PTO_SQL

Course

Performance Tuning and Optimizing SQL Server and SQL Databases 

 

Overview

Performance tuning focuses on identifying and resolving bottlenecks that may affect the performance applications.

Optimization involves analyzing and improving the performance of your code and resources to make it faster and more efficient

Audience Profile

This course was designed for SQL developers and database administrators (DBAs) who wish to enhance their SQL tuning skills and improve database performance but also for SQL designer application developers

 

Course Outline

  1. SQL Server and databases tuning
  2. Evaluate the system’s hardware resources and and their load level
  3. Identify critical hardware resources
  4. Evaluate system hardware resources allocated and used by SQL Server
  5. Identify the database(s) with the highest consumption of hardware resources
  6. Analyze the design of the main database / databases (filegroup / filegroups, filestream, memory-optimized filegroup)
  7. Identify tables, procedures, and functions that generate bottlenecks in a database or application
  8. Identify slow-stored procedures and functions
  9. Evaluate indexes and statistics
    • Evaluating indexes – look for missing indexes, index usage, index maintenance overhead
    • Evaluating statistics – check for missing statistics, evaluate statistics accuracy, consider the frequency of data changes
  1. Analyze the maintenance plan of indexes and statistics and decide for daily, weekly, periodic, emergency job
  2. Impact of rebuilding vs impact of reorganizing indexes
  1. SQL server and databases optimization
  2. Detect too many indexes
  3. Optimizing queries by configuring columnstore and full-text indexes
  4. In-depth understanding of the execution plan
  5. Fatal errors vs query time out
  6. Determining the need for partitioning tables
  7. Implementing the optimal level of transactions isolation level
  8. Implementing the optimal level of transactions isolation level for Select statements
  • SQL server and databases optimization tools
  1. SQL Server Query Optimizer vs SQL Database Engine Tuning Advisor
  2. SQL Server Profiler vs Performance Monitor
  3. SQL Server Profiler & Performance Monitor
  4. SQL Server Profiler vs Extended Events
  5. SQL Resource Governor
  6. Query dynamic views for Waits, Query cached plans, I/O latches, Buffer latches, and non-buffer latches, Locks, Similar plan cache for the same statements, etc.
  1. SQL server and databases maintenance
  2. Set Baseline for SQL Server metrics
  3. Set Baseline for SQL database / databases metrics
  4. Set the daily maintenance job
  5. Set the weekly maintenance job
  6. Set the monthly maintenance job
  7. Set the procedure for emergency maintenance plan