Query Tuning (Performance Tuning) with SQL Server
4.5 (2 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
8 students enrolled

Query Tuning (Performance Tuning) with SQL Server

100% Practical, In-depth Real-time Examples with Ease of Undrstanding
4.5 (2 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
8 students enrolled
Created by Sai Phanindra
Last updated 4/2020
English
Price: $79.99
30-Day Money-Back Guarantee
This course includes
  • 5 hours on-demand video
  • 11 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • In-depth Performance Tuning Concepts
  • Using SQL Server Tuning Tools
  • Using DTA, Profiler, Perfmon Tools
  • Execution Plan Analysis
  • Lock Management and Deadlocks
Requirements
  • Prior knowledge on SQL Server Basics
Description

This SQL Server Performance Tuning from SQL School includes In-depth Query Tuning and Troubleshooting concepts in SQL Server 2019, 2017, 2016 including Histograms, XEL Files, In-Memory Tables, Temporal Tables, IO Costs, CPU Costs, Opertor Costs, Execution Plan Analysis and DOP Settings with Resource Groups & Workload Groups. Memory Optimization Techniques, CPU Utilization and IO Monitoring Processes, Query Audits with Dynamic Management Views (DMVs), Dynamic Management Functions (DMFs), Procedure Cache are included in this course.

This SQL Server Performance Tuning also includes a Real-time CaseStudy that helps you to understand master Query Analytical skills with Workload Analysis, Peak - Hour metrics, Understanding various levels of Query Statistics, Index Management Techniques

This course includes total of NINE Technical, Practical Video Lectures.

VIDEO 1 : DEMO [INTRODUCTION]

Course Outline, Pre-Requisites, Software Installations, Database Configurations]


VIDEO 2 : INDEXES

Indexes: Architecture and Types Of Indexes Clustered Indexes and Non Clustered Indexes Included Index, ColumnStore Index, Filtered Indexes COVERING Indexes and UNIQUE Column Indexes LIVE Online Indexes in Real-time - Practical Use B Tree Structures and Index Access Management (IAM) Branch Level Pages and Leaf Level Pages in Indexes Tuning Join Queries and Query Level Conditions Index Selectivity and Tuning Options in SQL Queries


VIDEO 3 : QUERY AUDITS

Audit Long Running Queries using DMVs and DMFs Activity Monitor Tool and Query Statistics Reports Logical I/O, Physical I/O and Database I/O, Wait Time Recent Expensive Queries & Active Expensive Queries Plan Handle and Execution Time - Query Usage Audits Factors Impacting the Query Executions, Performance Resumable Indexes, Usage in SQL Server 2017, Higher ONLINE, RESUME, PAUSE, MAX_DURATION Options Query Store - Settings and Advantages. Options


VIDEO 4 : PARTITIONS

PARTITIONS Mechanism : Advantages, Performance Database Filegroups Usage with Partition Ranges Partition Functions and Partition Schemes - Usage Partitioning Un-partitioned Tables using Indexes Aligned / Indexed Partitions - Query Importance Partition SPLIT and Partition MERGE, NextUsed Partition Compression Techniques : ROW, PAGE Data Archival & SWITCH Partitions. Partitioned Views Query Store - Settings and Advantages. Options


VIDEO 5 : STATISTICS, MEMORY & TEMPORAL TABLES

In-Memory Tables and Memory Optimizations Memory Snapshots at Database Level Memory Settings for Tables with Primary Key MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT Temporal Tables and SYSTEM VERSIONING Temporal Tables For DML History Audits Statistics : Purpose, Auto / Manual Creation, Usage Index Statistics, Column Statistics for Tables, ReUse


VIDEO 6 : INDEX MANAGEMENT

Internal Fragmentation and External Fragmentation Fragmentation Audits : DMFs and Threshold Values Index Reorganization and Index Rebuild Options Database Maintenance Plans (DMP) For Index Reorg Compress Large Objects, Fragmentation Condition Index Page Count Condition, Index Usage Condition Fast, Sampled and Detailed Scan Types for Indexes Statistics Updates : Full Scan Options, DMP Jobs


VIDEO 7 : TUNING TOOLS

Tuning Tools : Creating Workload Files and Trace Files SQL Profiler Tool - Tuning Template and TSQL / SP Events DTA Tool - Profiler Trace Files: Tuning Recommendations DTA with Query Cache (Procedure Cache), .SQL File Inputs Execution Plans - Internals. Actual, LIVE Execution Plans Plan Types : Index Scan, Index Seek, Tables Scan, Spooling Query Costs : IO, CPU Cost, SubTree Cost, Operator Cost NUMA Nodes, Boost SQL Priority, Thread Count, IO Affinity


VIDEO 8 : LOCKS, ISOLATION LEVELS LOCKS

Mechanism, Types, Concurrency Control Lock Types: X, S, IS, IX, U, MD, Sch-M and Sch-S Lock Audits : SP_WHO2, SP_LOCK, sysprocesses Deadlock Simulation, Deadlock Prevention Scenarios Deadlock Audits and Lock Events in Profiler Tool Isolation Levels - ReadCommitted, Read Uncommitted Serializable, Snapshot, Repeatable Read Isolations Read Committed Snapshot Isolation Level in Real-time


VIDEO 9 : FULL TEXT SEARCH

Full Text Search (FTS) Architecture - Tuning Stop Words, Stemmer and Thesaurus For Queries Indexer Program and Query Processor Usage Database Catalogs (FTC) and FDHost.exe Full Text (FT) Indexes for Query Tuning Change Tracking, Automated Population CONTAINS() and FREETEXT() Functions Filter Daemon Host Exe and Tokenization Concepts


VIDEO 10 : PERFMON TOOLS & REAL-TIME ISSUES, SOLUTIONS

Perfmon Counters and Real-time Tracking Of Resources Processor, Disk, Memory, Transactions, Database Counters Using Perfmon for Big Query Audits. Free & Total Memory Longest Running Transactions & Transactions Per Second Database Log Space - Issues, Solutions. Log Rebuilds TempDB Space Issues - Reasons, Cautions and Solutions Memory Issues - Hit Ratio, Buffer Cache, Parameter Sniffing Performance Tuning - Final Checklist and Precautions

All Training sessions are completely practical, Real-time.

Study Material and Practice Databases, Notes included for each video lecture.


Who this course is for:
  • Anyone who works with SQL Server
  • Developers / Admins / BI Engineers / Data Analyts
Course content
Expand all 10 lectures 05:05:54
+ Section 1
5 lectures 02:13:11

This is an Introduction Video about SQL Server Performance Tuning.

To get started with this course, kindly install SQL Server 2017 or higher from :  https://sqlschool.com/downloads/ 

Preview 08:01

INDEXES

Indexes: Architecture and Types Of Indexes

Clustered Indexes and Non Clustered Indexes

Included Index, ColumnStore Index, Filtered Indexes

COVERING Indexes and UNIQUE Column Indexes

LIVE Online Indexes in Real-time - Practical Use

B Tree Structures and Index Access Management (IAM)

Branch Level Pages and Leaf Level Pages in Indexes

Tuning Join Queries and Query Level Conditions

Index Selectivity and Tuning Options in SQL Queries

Indexes
31:18

QUERY AUDITS

Audit Long Running Queries using DMVs and DMFs

Activity Monitor Tool and Query Statistics Reports

Logical I/O, Physical I/O and Database I/O, Wait Time

Recent Expensive Queries & Active Expensive Queries

Plan Handle and Execution Time - Query Usage Audits

Factors Impacting the Query Executions, Performance

Resumable Indexes, Usage in SQL Server 2017, Higher

ONLINE, RESUME, PAUSE, MAX_DURATION Options

Query Store - Settings and Advantages. Options

Query Audits
33:20

PARTITIONS

PARTITIONS Mechanism : Advantages, Performance

Database Filegroups Usage with Partition Ranges

Partition Functions and Partition Schemes - Usage

Partitioning Un-partitioned Tables using Indexes

Aligned / Indexed Partitions - Query Importance

Partition SPLIT and Partition MERGE, NextUsed

Partition Compression Techniques : ROW, PAGE

Data Archival & SWITCH Partitions. Partitioned Views

Query Store - Settings and Advantages. Options

Partitions
34:40

STATISTICS, MEMORY & TEMPORAL TABLES

In-Memory Tables and Memory Optimizations

Memory Snapshots at Database Level

Memory Settings for Tables with Primary Key

MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT

Temporal Tables and SYSTEM VERSIONING

Temporal Tables For DML History Audits

Statistics : Purpose, Auto / Manual Creation, Usage

Index Statistics, Column Statistics for Tables, ReUse

STATISTICS, MEMORY & TEMPORAL TABLES
25:52
+ Section 2
5 lectures 02:52:43

INDEX MANAGEMENT

Internal Fragmentation and External Fragmentation

Fragmentation Audits : DMFs and Threshold Values

Index Reorganization and Index Rebuild Options

Database Maintenance Plans (DMP) For Index Reorg

Compress Large Objects, Fragmentation Condition

Index Page Count Condition, Index Usage Condition

Fast, Sampled and Detailed Scan Types for Indexes

Statistics Updates : Full Scan Options, DMP Jobs

INDEX MANAGEMENT
30:46

TUNING TOOLS

Tuning Tools : Creating Workload Files and Trace Files

SQL Profiler Tool - Tuning Template and TSQL / SP Events

DTA Tool - Profiler Trace Files: Tuning Recommendations

DTA with Query Cache (Procedure Cache), .SQL File Inputs

Execution Plans - Internals. Actual, LIVE Execution Plans

Plan Types : Index Scan, Index Seek, Tables Scan, Spooling

Query Costs : IO, CPU Cost, SubTree Cost, Operator Cost

NUMA Nodes, Boost SQL Priority, Thread Count, IO Affinity

TUNING TOOLS
37:47

LOCKS, ISOLATION LEVELS

LOCKS : Mechanism, Types, Concurrency Control

Lock Types: X, S, IS, IX, U, MD, Sch-M and Sch-S

Lock Audits : SP_WHO2, SP_LOCK, sysprocesses

Deadlock Simulation, Deadlock Prevention Scenarios

Deadlock Audits and Lock Events in Profiler Tool

Isolation Levels - ReadCommitted, Read Uncommitted

Serializable, Snapshot, Repeatable Read Isolations

Read Committed Snapshot Isolation Level in Real-time

LOCKS, ISOLATION LEVELS
44:06

FULL TEXT SEARCH

Full Text Search (FTS) Architecture - Tuning

Stop Words, Stemmer and Thesaurus For Queries

Indexer Program and Query Processor Usage

Database Catalogs (FTC) and FDHost.exe

Full Text (FT) Indexes for Query Tuning

Change Tracking, Automated Population

CONTAINS() and FREETEXT() Functions

Filter Daemon Host Exe and Tokenization Concepts

FULL TEXT SEARCH
22:42

PERFMON TOOLS & REAL-TIME ISSUES, SOLUTIONS

Perfmon Counters and Real-time Tracking Of Resources

Processor, Disk, Memory, Transactions, Database Counters

Using Perfmon for Big Query Audits. Free & Total Memory

Longest Running Transactions & Transactions Per Second

Database Log Space - Issues, Solutions. Log Rebuilds

TempDB Space Issues - Reasons, Cautions and Solutions

Memory Issues - Hit Ratio, Buffer Cache, Parameter Sniffing

Performance Tuning - Final Checklist and Precautions


Preview 37:22