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.5 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 5000/-

USD 68
INR 10000/-
USD 136

Trainer : Mr. Sai Phanindra

 

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

Performance Training Course Contents:

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

Module I: SQL Queries & T-SQL Programming

Applicable for Plans A, B, C

Video 1: SQL SERVER INTRODUCTION

  • Data, Databases and RDBMS Software
  • Database Types : OLTP, DWH, OLAP
  • Microsoft SQL Server Advantages, Use
  • Versions and Editions of SQL Server
  • SQL : Purpose, Real-time Usage Options
  • SQL Versus Microsoft T-SQL [MSSQL]
  • Microsoft SQL Server - Career Options
  • SQL Server Components and Usage
  • Database Engine Component and OLTP
  • BI Components, Data Science Components
  • ETL, MSBI and Power BI Components
  • Course Plan, Concepts, Resume, Project
  • 24 x 7 Online Lab for Remote DB Access
  • Software Installation Pre-Requisites

Video 7: JOINS, T-SQL Queries : Level 1

  • JOINS - Table Comparisons Queries
  • INNER JOINS For Matching Data
  • OUTER JOINS For (non) Match Data
  • Left Outer Joins with Example Queries
  • Right Outer Joins with Example Queries
  • FULL Outer Joins - Realtime Scenarios
  • Join Queries with "ON" Conditions
  • Join Unrelated Tables in SQL Server
  • NULL, IS NULL Operators in Joins
  • CROSS JOIN and CROSS APPLY
  • CROSS JOIN Versus CROSS APPLY
  • One-way & Two Way Data Comparisons
  • Important Join Queries in T-SQL
  • Join Options: Merge, Loop, Hash

Video 13: STORED PROCEDURES Level 2

  • Table Valued Parameters (TVP)
  • ReadOnly Parameters, Stored Procedures
  • Output Parameters, Stored Procedures
  • User Data Types and Real-time Use
  • Dynamic Data Insertions with SPs
  • Table Cloning, Inserts @ Table Variables
  • SQL Injection Attacks - Precautions
  • 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

Video 2: SQL SERVER INSTALLATIONS

  • System Configuration Checker Tool
  • Versions and Editions of SQL Server
  • SQL Server and SSMS Installation Plan
  • SQL Server Pre-requisites : S/W, H/W
  • SQL Server 2016 / 2017 Installation
  • SQL Server 2019 Installation
  • Instance Name and Server Features
  • Instances : Types and Properties
  • Default Instance, Named Instances
  • Port Numbers, Instance Differences
  • Service and Service Account Use
  • Authentication Modes and Logins
  • Windows Logins and SQL Logins
  • FileStream and Collation Properties
  • DBEngine and Replication Components

Video 8: Group By, T-SQL Queries Level 2

  • GROUP BY Queries and Aggregations
  • Group By Queries with Having Clause
  • Group By Queries with Where Clause
  • Using WHERE and HAVING in T-SQL
  • Rollup : Usage and T-SQL Queries
  • Cube : Usage and T-SQL Queries
  • UNION and UNION ALL Operator
  • EXISTS Operator, Query Conditions
  • 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

Video 14: STORED PROCEDURES - Level 3

  • DML Triggers and DDL Triggers
  • FOR and INSTEAD OF Triggers
  • Magic Tables : Inserted, Deleted
  • Views on Tables - SCHEMABINDING
  • ENCRYPTION and CHECK OPTION
  • Cascaded Views, Encrypted Views
  • Updatable Views, Joins with Triggers
  • Stored Procedures @ Triggers, Views
  • 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

Video 3: SSMS Tool, SQL BASICS - 1

  • SQL Server Management Studio
  • Local and Remote Connections
  • System Databases: Master and Model
  • MSDB, TempDB, Resource Databases
  • Creating Databases : Files [MDF, LDF]
  • Creating Tables in User Interface
  • Data Insertion & Storage. Limitations
  • SQL : Purpose and Real-time Usage
  • SQL Versus T-SQL : Basic Differences
  • DDL, DML, SELECT, DCL and TCL
  • Creating Tables using SQL Scripts
  • Data Storage, Inserts - Basic Level
  • Table Data Verifications with Select
  • SELECT Statement for Table Retrieval

Video 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

Video 15: XML,BLOB,FUNCTIONS Level 2

  • Functions : Types, Real-world Usage
  • Scalar Value Returning Functions
  • Inline Table Value Functions
  • Multi-Line Table Value Functions
  • WHILE Loops and Iteractions in T-SQL
  • Table Variables Usage in T-SQL
  • Data Type Conversions with Functions
  • Composite Keys , Computed Columns
  • XML AUTO, XML RAW and XML PATH
  • Using BULK INSERT & BULKCOLUMN
  • OPENROWSET For Data Import, CAST
  • XML Options in T-SQL Queries, Joins
  • XML AUTO, XML RAW and XML PATH
  • JSON Files - Data Import into SQL DB

Video 4: SQL BASICS - 2

  • Creating Databases & Tables in SSMS
  • Single Row Inserts, Multi Row Inserts
  • Rules for Data Insertion Statements
  • SELECT Statement @ Data Retrieval
  • SELECT with WHERE Conditions
  • Batch Concept and Go Statement
  • AND and OR Operators Usage
  • IN Operator and NOT IN Operator
  • Between, Not Between Operators
  • LIKE and NOT LIKE Operators
  • UPDATE Statement & Conditions
  • DELETE & TRUNCATE Statements
  • Logged and Non-Logged Operations
  • ADD, ALTER and DROP Columns
  • ALTER & DROP Table Statements

Video 10: View, SPs, Function Basics

  • Views : Types, Usage in Real-time
  • System Predefined Views and Audits
  • Listing Databases, Tables, Schemas
  • 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_pkeys, sp_rename and sp_help
  • Important System Objects and Metadata
  • When to use Which Database Objects

Video 16: Server, DB Architecture

  • Server Architecture and Protocols
  • Database Engine and Query Processor
  • Parser, Optimizer, SQL & DB Manager
  • Storage Engine Components, SQL OS
  • File Manager and Database Files
  • Transaction Services, Buffer Manager
  • Lock Manager, IO Manager, MDAC
  • CLR, WAL, Lazy Writer, Checkpoint
  • Database Architecture - Data Files
  • Database Architecture - Log Files
  • Primary (mdf), Secondary Files (ndf)
  • Filegroups Usage, ReadOnly Filegroups
  • Database Files : Size and Location
  • Pages, Extents. Uniform, Mixed Extents
  • Transaction Log File [LDF], LSN, VLF

Video 5: SQL Basics - 3, T-SQL INTRO

  • Database Objects : Tables and Schemas
  • Schemas : Group Tables in Database
  • Schemas : Security Management Object
  • Creating Schemas & Batch Concept
  • Using Schemas for Table Creation
  • Data Storage in Tables with Schemas
  • Data Retreival and Usage with Schemas
  • Table Migrations across Schemas
  • Import and Export Wizard in SSMS
  • Data Imports with Excel File Data
  • Performing Bulk Operations in SSMS
  • Temporary Tables : Real-time Use
  • Local and Global Temporary Tables
  • # and ## Prefix, Scope of Usage
  • Session Level, Connection Level Use

Video 11: Triggers & Transactions

  • Triggers - Purpose, Real-world Usage
  • FOR/AFTER Triggers - Real time Use
  • INSTEAD OF Triggers - Real time Use
  • INSERTED, DELETED Memory Tables
  • Using Triggers for Data Replication
  • Enable Triggers and Disable Triggers
  • Database Level, Server Level Triggers
  • Auditting Triggers and Real World Use
  • Transactions : Types, ACID Properties
  • Transaction Types and AutoCommit
  • EXPLICIT & IMPLICIT Transactions
  • COMMIT and ROLLBACK Statements
  • Open Transaction Scenarios & Cause
  • Query Blocking Scenarios @ Real-time
  • NOLOCK and READPAST Lock Hints

Video 17 - 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
  • Primary (mdf) and Secondary (ndf) Files
  • 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
  • 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

Video 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 - working with Keys

Video 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.
Real-time Case Study - 2 (Sales & Retail)
Objective : Query Writing, Excel Integration
Excel Pivot Tables, Pivot Charts,
Data Formatting, ODC Connections & Labelling
PART 2 Of 2: Performance Tuning

Module II: Performance Tuning & MCSA - 70 761

Applicable for Plans B, C

Module II: Performance Tuning & MCSA - 70 761

Applicable for Plans B, C<

Video 21: QUERY TUNING 1 - AUDITS, INDEXES

  • 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
  • Server Dashboards and Memory Consumption Reports
  • Factors Impacting the Query Executions, Performance
  • Indexes: Architecture and Types : Clustered, Non Clustered
  • B Tree Architecture and Advantages : IAM Page, Branches
  • GAM, SGAM, PFS and Index Page Architecture
  • Clustered & Non Clustered Indexes : Creation and Usage
  • Included and ColumnStore Indexes : Creation and Usage
  • Covering Indexes and Filtered Indexes : Creation and Usage
  • Indexed View (Materialized View) : Performance Advantages
  • SORT_IN_TEMPDB, ONLINE = ON. Real-time Considerations

Video 23: QUERY TUNING 3 - TUNING TOOLS, LOCK MANAGEMENT

  • Tuning Tools : Creating Workload Files and Trace Files
  • SQL Profiler Tool - Tuning Template and TSQL / SP Events
  • DTA Tool with Profiler Trace Files: Tuning Recommendations
  • DTA with Query Cache (Procedure Cache) & .SQL File Inputs
  • Perfmon Counters : Processor, Disk, Memory, Transactions, DB
  • Execution Plans - Internals. Actual, LIVE Execution Plans
  • Query Costs : IO, CPU Cost, SubTree Cost, Operator Cost
  • NUMA Nodes, Boost SQL Priority, Thread Count, IO Affinity
  • Execution Plan Issues with Spooling Mechanism
  • Lock Types: Shared Lock, Intent Shared, Exclusive Lock
  • Intent Exclusive, Update, Metadata, Schema Lock
  • Lock Audits : SP_WHO2, SP_LOCK, sysprocesses
  • Isolation Levels - ReadCommitted, Read Uncommitted
  • Serializable, Snapshot, Repeatable Read Isolations
  • Read Committed Snapshot Isolation Level in Real-time
  • Deadlock Simulation, Deadlock Prevention Scenarios
  • Deadlock Audits and Lock Events in Profiler Tool

Video 22: QUERY TUNING 2 - PARTITIONS, INDEX MANAGEMENT

  • Query Store - Settings and Advantages. Options
  • PARTITIONS Mechanism : Advantages, Performance
  • Partition Functions and Partition Schemes - Usage
  • Partitioning Un-partitioned Tables using GUI
  • Table Data Compression Techniques : ROW, PAGE
  • Compression of Speific Partitions, REBUILDS
  • Partition and Compression Recommendations for OLTP
  • Auditing and Verifying Partitions with sys.partitions
  • Index Management - DOP : Degree Of Parallelism
  • Index Rebuilding Process and Fragmentation Audits
  • Index Page Count and Index Condition Checks
  • Resumable Indexes: ONLINE and RESUME Options
  • PAUSE and RESUME Options in Index Rebuilds
  • Fast, Detailed Scans and Stats NoRecompute
  • Statistics Management : Index and Column Stats

Video 24: QUERY TUNING 4 - FULL TEXT SEARCH, MOT TABLES

  • Full Text Search (FTS) Mechanism - Architecture, Tuning
  • Stop Words, Stemmer and Thesaurus For FullText Indexes
  • Indexer Program, Query Processor & FT Query Compilation
  • Database Catalogs (FTC) and FDHost.exe. Daemon Threads
  • Full Text (FT) Indexes : Query Tuning. Filter Daemon Host
  • Creating Full Text Catalogs (FTCs) and FT Indexes
  • CONTAINS() Queries and FREETEXT() Queries with SELECT
  • Query Performance Impact with SQL Server T-SQL Queries
  • Resumable Indexes, Usage in SQL Server 2017 & 2019
  • ONLINE, RESUME, PAUSE, MAX_DURATION Options
  • In-Memory Tables : Creation and Practical Usage for Tuning
  • Memory Snapshots at Database Level and Table Level
  • FileStream Files and Memory Snapshot Filegroups for MOT
  • MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT
  • Temporal Tables : for DML Audits, System Versioning
  • Using Temporal Tables for Data Audits, Timestamps
  • Statistics : Purpose, Auto Creation and Updates
Video 25 : MCSA 70 761 Exam Pattern, Examples, Guidance

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