SQL Server Query Tuning and Optimization


طول دوره زمان برگزاری تاریخ شروع دوره شهریه استاد ثبت نام
5 جلسه 15 ساعت شنبه از ساعت 17:30 الی 20:30
شنبه ۲۶ فروردین ۱۳۹۶ 258,000 تومان علی تجویدی

سرفصل ها و محتوای دوره SQL Server Query Tuning and Optimization  

سرفصل های دوره:

Introduction

  • Introduction
  • Query Tuning

Execution Plan Basics

  • Introduction
  • Displaying Graphical Execution Plans
  • Graphical Execution Plan Icons
  • Various Execution Plan Types
  • Clustered Index
  • Non-clustered Index
  • Key Lookup
  • Index Seek vs Index Scan
  • Types of Joins
  • Summary

Tables and Index Data Structures Architecture

  • Table and Index Organization
  • Heap Structures
  • Clustered Index Structures
  • Nonclustered Index Structures

Indexes

  • Understanding Indexes
  • Understanding Indexes
  • Designing Indexes
  • Designing Indexed Views
  • Implementing Indexes
  • Optimizing Indexes
  • Essential Indexing Techniques

Statistics 

Statistics Used by the Query Optimizer

Using Statistics to  Improve Query Performance

  • What are Query Optimization Statistics?
  • Using the Database-Wide Statistics Options
  • Determining When to Create Statistics
  • Determining When to Update Statistics
  • Designing Queries that Use Statistics Effectively

Query Design for Performance

  • Introduction
  • What makes a SQL statement sargable?
  • Tips to improve SQL Server query design and performance
  • EXISTS vs IN vs JOIN
  • Common Table Expression (CTE)
  • Order of Table and Query Hints Plan
  • Guide and Parameter Sniffing
  • Dynamic SQL and Performance
  • User Defined Functions, Views and Performance
  • Summary

Performance Tuning Tools

  • Introduction
  • SSMS Performance Dashboard
  • Error Logs
  • SQL Server Best Practices Analyzer
  • Database Tuning Advisor
  • Perfmon Counters
  • Performance Analysis of Logs (PAL) Tool
  • SQL Space Map
  • SQL Server Profiler
  • SQL Server Extended Events
  • SQL Server Wait Stats
    • SQL Server Wait Stats – CXPACKET
    •  Wait Stats - Identify Offending Query
  • Dynamic Management Views (DMV)
  • SQLDiag
  • Summary

Tips and Tricks

  • Introduction
  • Demo: Columnstore Index
  • Demo: Functions and Computed Columns
  • Demo: Multi Statement Table Value Functions
  • Demo: Stored Procedure and Dynamic Query
  • Demo: Stored Procedure and Transaction
  • Demo: Stored Procedure and Compilation
  • Demo: Performance Tips and Tricks

Check List: Performance Tuning

  • Checklist