SQL Server Query Tuning (Performance Tuning)

This SQL Server Query Tuning (Performance Tuning) course 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. Register Today for Free Demo

 

SQL Server Query Tuning (Performance Tuning)

  PLAN A PLAN B
Description Query Tuning SQL Server, T-SQL,
Query Tuning
Applicable For Experienced Dev Starters, Experienced
Course Duration 1 Week 4.5 Weeks
SQL Server, DB Architecture Check-Symbol-for-No Check-Symbol-for-Yes
Query Architecture, Client Stats Check-Symbol-for-No Check-Symbol-for-Yes
Basic to Adv. Query Design, Joins Check-Symbol-for-No Check-Symbol-for-Yes
Basic to Adv. Stored Procedures Check-Symbol-for-No Check-Symbol-for-Yes
New Features: SQL 2017, 2019 Check-Symbol-for-Yes Check-Symbol-for-Yes
Real-time Project with Solution Check-Symbol-for-No Check-Symbol-for-Yes
Query Tuning & Execution Plans Check-Symbol-for-Yes Check-Symbol-for-Yes
Partitions, Locks and Isolations Check-Symbol-for-Yes Check-Symbol-for-Yes
Temporal Tables and Memory Tables Check-Symbol-for-Yes Check-Symbol-for-Yes
Query Statistics, LIVE & Client Statistics Check-Symbol-for-Yes Check-Symbol-for-Yes
Execution Plan Analsyts, Query Costs Check-Symbol-for-Yes Check-Symbol-for-Yes
Tuning Tools : DTA, Profiler, Perfmon Check-Symbol-for-Yes Check-Symbol-for-Yes
XEL Files, Extended Events, Logs Check-Symbol-for-Yes Check-Symbol-for-Yes
Index Management Check-Symbol-for-Yes Check-Symbol-for-Yes
Statistics Management Check-Symbol-for-Yes Check-Symbol-for-Yes
Resource Optimization Check-Symbol-for-Yes Check-Symbol-for-Yes
DOP and Resource Governor Check-Symbol-for-Yes Check-Symbol-for-Yes
MCSA 70-761 Certification Croos-symbol-for-Yes Croos-symbol-for-Yes
Total Course Fee INR 4000/-
INR 5000
INR 8000/-
INR 9000

Trainer : Mr. Sai Phanindra

S. No Timings (IST) Start Date  
Performance Tuning Schedules [Plan A]
1 6:30 AM - 7:30 AM Started Register
2 10:15 AM - 11:15 AM Started Register
3 7:30 PM - 8:30 PM August 12th Register
T-SQL + Performance Tuning Schedules [Plan B]
1 6:30 AM - 7:30 AM Sept 15th Register
2 9 AM - 10 AM Oct 21st Register
3 10:15 AM - 11:15 AM Sept 25th Register
4 6 PM - 7 PM Sept 23rd Register
5 7:30 PM - 8:30 PM Oct 11th Register

If above schedules does not work, opt for Query Tuning Video Training

 

Azure SQL Database - TRAINING HIGHLIGHTS :

✔ In-depth Tuning ✔ In-Memory Data
✔ Schema Migrations ✔ DB Documenation
✔ Memory Tables ✔ Temporal Tables
✔ Execution Plans ✔ XEL Graphs, DTA
✔ Query Costs ✔ Statistics Management

Azure SQL Database (DEV) Training Course Contents:

PART 1 Of 2: SQL Server Basics, Queries, Stored Procedures and Database Development

Module I: SQL Basics, SQL Server Concepts

(For Plan B)

Module II: T-SQL Queries

(For Plan B)

Module III: T-SQL Programming, Banking Project

(For Plan B)

DAY 1: INTRODUCTION, INSTALLATION

  • Data, Databases and RDBMS Software
  • Database Types : OLTP,DWH,OLAP,HTAP
  • Microsoft SQL Server Advantages, Use
  • DB Engine, BI, Data Science Components
  • SQL : Purpose, Real-time Usage Options
  • SQL Versus Microsoft T-SQL [MSSQL]
  • Microsoft SQL Server - Career Options
  • Real-time Projects & Job Responsibilities
  • SQL Server @ Cloud: Azure, AWS, G Cloud
  • Versions and Editions of SQL Server
  • SQL Server and SSMS Installation Plan
  • SQL Server Pre-requisites : S/W, H/W
  • System Configuration Checker (SCC) Tool
  • SQL Server 2019 : Installation [Overview]

DAY 7: JOINS, T-SQL QUERIES - Level 1

  • JOINS - Table Comparisons Queries
  • INNER JOIN - Examples, WHERE, ON
  • OUTER JOIN - Examples, WHERE, ON
  • Left Outer Joins with Example Queries
  • Right Outer Joins with Example Queries
  • FULL Outer Joins - Real-time Scenarios
  • MERGE, LOOP, HASH Join Options
  • Big Table Versus Small Table Joins
  • Join Types Versus Join Options in T-SQL
  • CROSS JOIN Versus CROSS APPLY
  • Using Joins for DB Metadata Audits
  • Joining more than 2 Tables in T-SQL
  • Joining Tables with Query Conditions
  • Joining Unrelated Tables, Join Options

DAY 14: STORED PROCEDURES - Level 2

  • Table Valued Parameters (TVP) - Usage
  • SQL Injection Attacks - Type Precautions
  • READONLY Parameters - Stored Procedures
  • OUTPUT Parameters - Stored Procedures
  • User Defined Data Types, Real-time Use
  • Dynamic Data Insertions with Stored Procs
  • Table Cloning, Data Inserts @ Table Variables
  • CTE : Common Table Expressions
  • Real-time Scenarios with CTEs - Usage
  • ROW_NUMBER() with CTE Queries
  • Using CTEs for Avoiding Self Joins
  • Using CTEs for Avoiding Sub Queries
  • Recursive CTEs and ANCHOR Element
  • Termination Checks in Recursive CTEs

Day 2: INSTALLATIONS [DETAILED]

  • SQL Server 2016 Installation, Guidance
  • SQL Server 2017 Installation, Guidance
  • Instance Types: Default, Named Instances
  • SQL Server Features and Importance
  • SQL Server Database Engine For OLTP
  • File Stream and Collation Properties
  • SQL Server Authentication Modes
  • Windows Login & SQL Server Login Types
  • System Databases: Master, Model, MSDB
  • TempDB and Resource Database Locations
  • "sa" System Account, File Stream Property
  • SQL Server Management Studio. SSMS
  • Test Connection to Local Servers
  • Test Connection to Remote Servers

DAY 8: JOINS, T-SQL QUERIES - Level 2

  • GROUP BY Queries and Aggregations
  • GROUP BY Queries with Having Clause
  • Group By Queries - Query Design Rules
  • ROLLUP( ) & CUBE( ) Summary Values
  • GROUPING() Function for Row Status
  • Replacing Nulls: ISNULL, COALESCE
  • Joining Tables with Group By, Having
  • Sub Queries and Alternatives to Joins
  • Using Joins with Group By Queries
  • Using Joins with Nested Sub Queries
  • Sub Queries with Joins and Group By
  • Using UNION and UNION ALL in Queries
  • Nested Sub Queries with Group By, Joins
  • Comparing WHERE, HAVING Conditions

DAY 15: STORED PROCEDURES - Level 3

  • Views on Tables - SCHEMABINDING
  • ENCRYPTION and CHECK OPTION
  • Cascaded Views, Encrypted Views
  • Updatable Views, Joins with Triggers
  • Error Handling in T-SQL: TRY & CATCH
  • Error Handling, THROW in Procedures
  • Stored Procedures - WITH RESULT SETS
  • Cursors - Benefits, Cursors in SProcs
  • ForwardOnly, Scroll & Local Cursors
  • Static, Dynamic & Global Cursors
  • Keyset Cursors and @@FetchStatus
  • Nesting of Stored Procedures - Dynamic
  • Data Formatting and WHILE Loops
  • Using Temporary Tables for Formatting

Day 3: DATABASE & SQL BASICS - Level 1

  • SQL : Purpose and Real-time Usage
  • DDL, DML, SELECT, DCL and TCL
  • SSMS Tool : Connections and Usage
  • SQL Versus T-SQL : Basic Difference
  • Server Connections, Session Creations
  • Creating Databases and DB Connections
  • Creating Tables. Int, Char Data Types
  • Single Row Inserts, Multi Row Inserts
  • INSERT and INSERT INTO Statements
  • SELECT Statement for Table Retrieval
  • WHERE Conditions with =, OR, IN
  • AND, OR, NOT, IN, NOT IN Conditions
  • LIVE QUERY STATISTICS in SSMS
  • Table Scan Properties in SQL Server

DAY 9: JOINS, T-SQL QUERIES - Level 3

  • Cast, Convert, DateAdd, DateDiff Functions
  • Date & Time Styles, Data Formatting
  • Using Date and Time Formats in Queries
  • String Functions: SUBSTRING,REPLICATE
  • CHARINDEX, PATINDEX, LEFT, RIGHT
  • LEN, STUFF, LTRIM, RTRIM, REVERSE
  • DIFFERENCE, SOUNDEX, STRING_SPLIT
  • WHEN MATCHED and NOT MATCHED
  • Incremental Loads with MERGE Statement
  • IIF(), CASE with WHEN and ELSE, END
  • FETCH - OFFSET, NEXT ROWS, Order By
  • Using PIVOT Function and FOR Values
  • ROW_NUMBER() and RANK() Queries
  • Dense Rank and Partition By Queries

DAY 16: FUNCTIONS - Level 2

  • Functions : Types, Real-world Usage
  • Inline Functions, Multi Line Functions
  • Looping Concepts in SQL Server
  • WHILE Loop Queries and UNPIVOT
  • GROUPING SETS and OUTPUT Function
  • EXISTS and RAISEERROR Functions
  • TRY_CONVERT, TRY_PARSE Functions
  • Using BULK INSERT & BULKCOLUMN
  • OPENROWSET For Data Import, CAST
  • OPENJSON For JSON Data Formats
  • JSON Files - Data Import into SQL DB
  • Json $Tag Notations, SELECT .. INTO
  • XML Options in T-SQL Queries, Joins
  • XML AUTO, XML RAW and XML PATH

DAY 4: DATABASE & SQL BASICS - Level 2

  • Creating Databases : Files [MDF, LDF]
  • Single Row Inserts, Multi Row Inserts
  • SELECT. WHERE Conditions, Operators
  • AND, OR, NOT, Mathematical Operators
  • IN, NOT IN, BETWEEN, NOT BETWEEN
  • IS NULL, LIKE, NOT LIKE. % and _
  • CHAR Versus VARCHAR Data Types
  • GO Statement, SQL BATCH Concept
  • DISTINCT, TOP, FETCH, ORDER BY
  • Basic Sub Queries with SELECT
  • UPDATE and DELETE Statements
  • TRUNCATE, ALTER, ADD and DROP
  • Table Scans, Measuring Query Time
  • CLIENT STATISTICS and Query Trails

DAY 10: Views, Functions, Procedure Basics

  • Views : Types, Usage in Real-time
  • System Predefined Views and Audits
  • Listing Databases, Tables, Indexes
  • Functions : Types, Usage in Real-time
  • Scalar, Inline and Multi-Line Functions
  • System Predefined Functions, Audits
  • DBId, DBName, ObjectID, ObjectName
  • Variables & Parameters in SQL Server
  • Procedures : Types, Usage in Real-time
  • User & System Predefined Procedures
  • Parameters and Dynamic SQL Queries
  • Sp_help, Sp_helpdb and sp_helptext
  • Sp_recompile, sp_pkeys, sp_rename
  • Compare Views, SPs and Functions

Day 17: Database, Index Architecture

  • Database Architecture - Detailed
  • Primary File, Secondary Files [mdf, ndf]
  • Database Log Files (T-LOG) For Audits [ldf]
  • Data Files, Log Files, LSN & VLF
  • Transaction Log File [LDF] & LSN
  • Filegroups : ReadWrite & Read Only
  • Indexes: Architecture and Types
  • Clustered and Non Clustered Indexes
  • Included and ColumnStore Indexes
  • FILTERED and COVERING Indexes
  • UNIQUE Indexes, Online Indexes
  • B Tree Structure, IAM Page [Root]
  • Indexed Views / Materialized Views
  • Pages, Extents, and Checkpoints

DAY 5: SQL Basics 3, Server Architecture

  • SQL Server Architecture Components
  • TDS Packets (N/W) in Client - Server
  • Protocols, SQL Native Client (SNAC)
  • Parser, Compiler, SQL Query Validations
  • Query Optimizer (QO) and SQL Manager
  • Storage Engine, File and DB Manager
  • Transaction Manager and Lock Manager
  • Buffer Manager, SQL OS and IO Buffer
  • Synchronization and Thread Scheduler
  • MDAC and CLR Components in SQL OS
  • Temporary Tables : Real-time Use
  • Local and Global Temporary Tables
  • Schemas : Real-time Usage, Creation
  • Schema - Table Transfer. 2P, 3P Naming

Day 11: Triggers, Transactions, DTC

  • Triggers - Purpose, Real-world Usage
  • FOR/AFTER Triggers - Real time Use
  • INSTEAD OF Triggers - Real time Use
  • INSERTED, DELETED Memory Tables
  • Enable Triggers and Disable Triggers
  • Database Level, Server Level Triggers
  • Auditting Triggers and Real World Use
  • Transactions : Types, ACID Properties
  • EXPLICIT & IMPLICIT Transactions
  • COMMIT and ROLLBACK Statements
  • Query Blocking Scenarios @ Real-time
  • Open Transctions in Real-world, Impact
  • NOLOCK and READPAST Lock Hints
  • Lock Hints, Joins @ T-SQL Queries

DAY 18 - 20: REAL-TIME PROJECT (BANKING)
Includes 2500 Lines of Code (COMPLETELY SOLVED).

Phase 1: DATABASE DESIGN
  • Understanding Project Requirements
  • End to End Project Work Flow
  • Naming Conventions in Real-time
  • Table Schemas : Creation and Use
  • Implementing Normal Forms (OLTP)
  • Computed Columns and Data Types
  • SQL_Variant, Bit, sysname Data Types
  • Email and Phone Number Validations
  • Data Types Conversions, Validations

Phase 2: QUERY DESIGN
  • Joining Tables for Reports
  • Views with JOIN Options
  • Implementing Indexed Views
  • Using PIVOT Tables in Queries
  • Using Functions for Queries
  • Dynamic Conditions in Queries
  • Parameterized Queries in T-SQL

Phase 3: PROGRAMMING
  • Event Handling , Error Handling
  • Stored Procedures with Transactions
  • Error Handling, Event Handling Options
  • Transaction Nesting, Save Points
  • Stored Procedures with Tables
  • Stored Procedures with Views
  • Stored Procedures with Functions
  • Automating DML with Triggers
  • Project Deployments, Project FAQ

   Project Solution Explanation
   Resume Points from the Project
   Interview FAQs from Project

DAY 6 : CONSTRAINTS, INDEXES - BASICS

  • Constraints and Keys - Data Integrity
  • NULL, NOT NULL Property on Tables
  • UNIQUE KEY Constraints: Importance
  • PRIMARY KEY Constraint: Importance
  • FOREIGN KEY Constraint: Importance
  • REFERENCES, CHECK and DEFAULT
  • Candidate Keys and Identity Property
  • Database Diagrams and ER Models
  • Relationships Verification and Links
  • Indexes : Basic Types and Creation
  • Index Sort Options, Search Advantages
  • Clustered and NonClustered Indexes
  • Primary Key and Unique Key Indexes
  • Need for Indexes with working with Keys

DAY 12 : ER MODELS, NORMAL FORMS

  • Normal Forms for Entity Relationships
  • First, Second, Third Normal Forms Usage
  • Boycee-Codd Normal Form : BNCF : Usage
  • 4 NF, EKNF, ETNF. Functional Dependency
  • Multi-Valued, Transitive Dependencies
  • Composite Keys and Composite Indexes
  • 1:1, 1:M, M:1, M:M Relationship Types
  • Self Referencing Keys and Self Joins
  • Adding NOT NULL Property to Columns
  • Adding Primary Key to Existing Tables
  • Adding Foreign Key to Existing Tables
  • Synonyms : Creation and Real-time Use
  • Using Synonyms in Self Join Queries
  • Cascading Keys. UPDATE/DELETE Types
Real-time Case Study - 1 (Sales & Retail)
Objective : DB Design, Table Design, Relations
Involves Purchases, Products, Customers
and Time Data with Various Data Types.
Solution Explanation in Day 13
DAY 13: Real-time Case Study - 2 (Sales & Retail)
Objective : Query Writing, Excel Integration
Writing Queries, Generate Excel Pivot Tables
Excel Pivot Charts, Data Formatting,
ODC Connections, Charts, Data Labelling.
PART 2 Of 2: Performance Tuning

Module IV : Query Tuning Training - Part 1

(For Plan A, B)

Module V : Query Tuning Training - Part 2

(For Plan A, B)

Day 22: Tuning 2 - AUDITS, PARTITIONS

  • Audit Long Running Queries : DMVs, DMFs
  • Activity Monitor Tool, Query Statistics
  • Logical & Physical I/O, DB IO, Waits
  • Recent and Active Expensive Queries
  • Server Dashboards and Built-In Reports
  • Memory Usage Reports, IO Statistics
  • DB Query Store : PAUSE, MAX_DURATION
  • Partition Mechanism : Database Filegroups
  • Partition Functions & Partition Schemes
  • Aligned / Indexed Partitions - Importance
  • Partition SPLIT and MERGE, NextUsed
  • Partition Compression : ROW, PAGE
  • Data Archival and Partition SWITCH
  • Partitioned Views Performance Benefits

Day 25: Tuning 5 - TUNING TOOLS

  • Tuning Tools : Workload and Trace Files
  • SQL Profiler Tool - Tuning Template, Events
  • DTA Tool with Profiler : Recommendations
  • DTA with Procedure Cache & .SQL Files
  • Execution Plans - Internals. Spooling
  • Estimated, Actual, LIVE Execution Plan
  • Query Costs : IO Cost and CPU Cost
  • Query Costs : SubTree Cost, Operator Cost
  • Numa Nodes, Boost Priority, Thread Count
  • Perfmon Counters - Important Counters
  • Resource Governor : Real-time Usage
  • Resource Pools and Workload Groups
  • Query Priority, DOP & Memory Grants
  • Classifier Function and RECONFIGURE

Day 23: Tuning 3 - FTS, STATISTICS

  • Full Text Search (FTS) - Architecture
  • StopWords, Stemmer, Thesaurus For FTS
  • Indexer, Query Processor & Compilation
  • Full Text Catalogs and Filter Daemon
  • FDHost.Exe and Daemon Threads for FTI
  • Full Text Indexes (FTI), Crawler Threads
  • Change Tracking (CT) and Data Population
  • CONTAINS(), FREETEXT() with SELECT
  • In-Memory Tables : Creation, Tuning
  • Memory Snapshots at DB and Table Level
  • FileStream Files and MOT Filegroups
  • MEMORY_OPTIMIZED_ELEVATE Snapshot
  • Stats Updates : FullScan, NoRecompute
  • Temporal Tables Usage & History Tracking
  • Statistics : Index and Column Statistics

Day 26: Tuning 6 - LOCKS, ISOLATIONS

  • LOCKS : Types, Concurrency Control
  • Lock Types and Lock Escalations
  • X, S, IS, IX,U, MD, Sch-M, Sch-S
  • Lock Audits : SP_WHO2, SP_LOCK
  • sysprocesses and Lock Waits
  • Auditting and Avoiding Blocking
  • Deadlock Simulation and Prevention
  • Deadlock Audits & Events in Profiler
  • Deadlock Graphs and XDL Files
  • Isolation Levels and Query Blocking
  • Row Versions and Page Versions
  • ReadComitted, UnComitted, Snapshot
  • Repeatable Reads and Phantom Reads
  • Read Comitted Snapshot Isolations

Day 24: Tuning 4 - INDEX MANAGEMENT

  • Index Management : Performance Tuning
  • Internal and External Fragmentation
  • Fragmentation Audits : DMFs, Thresholds
  • Proactive, Reactive Approach For Indexes
  • Index Reorganization Process, Audits
  • Index Rebuilding Process and Audits
  • Resumable Indexes: ONLINE, RESUME
  • PAUSE & RESUME Options : Index Rebuilds
  • Database Maintenance Plans (DMP) Jobs
  • Proactive Index Reorganization Process
  • Index Page Count and Index Condition
  • Degree Of Parallelism [DOP] Settings
  • DOP Settings with Index Management
  • Recently Used Indexes and Statistics
  • Fast, Detailed Scans. Statistics Updates

Day 27 : Tuning 7 - Execution Plans, Events

  • Parameter Sniffing : Real-time Issues
  • Execution Plans with Parameter Sniffing
  • DBCC SHOW_STATISTICS and Accuracy
  • Histograms and Density Vectors
  • RANGE_HI_KEY, EQ_ROWS, RANGE_ROWS
  • DISTINCT_RANGE_ROWS, Index Overlaps
  • sp_createstats & sp_autostats System SPs
  • Detection, Fixing Overlapping Indexes
  • SQL Traces : Creation - Audits. Limitations
  • Comparing SQL Profiler and Extended Events
  • Creating New Sessions, Launch Sessions
  • Watch LIVE Data and DDL/DML Audits
  • Extended Events & Traces for Query Analysis
  • Extended Events Packages and Targets
  • Extended Events Actions and Sessions
  • XEL Files, Audit Traces, Global Fields

Above Course Curriculum is applicable for registrations from July 6th, 2019

 
24x7 LIVE Online Server (Lab) with Real-time Databases. Course includes ONE Real-time Project. Register Today
 

SQL Server QueryTuning Classroom Training - Highlights :

  • Completely Practical and Real-time
  • Suitable for Starters + Working Professionals
  • Session wise Handouts and Tasks + Solutions
  • TWO Real-time Case Studies, One Project
  • Certification Guidance to MCSA Exams
  • Interview Preparation & MOCK Interviews
 
 
  • End-End Database Design & Implementation
  • Detailed SQL Server Architecture, DB Design
  • Query Tuning, Stored Procedures, Linked Servers
  • In-Memory, New Features of SQL Server 2017
  • Azure SQL Database Programming, Sharding
  • In-Memory Tables and Azure Performance Insights
 
Register Today  Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer: Mr. Sai Phanindra T [ 13+ Yrs of Technical Expertise, Microsoft Certified Trainer ]

 
 
 
 
 
24x7 LIVE Online Server (Lab) with Real-time Databases. Course includes ONE Real-time Project. Register Today