SQL Server & T-SQL Training (LIVE, Instructor-Led)

This impeccable Microsoft SQL Server Training Course is exclusively designed for aspiring Data Analysts, Business Analysts, Data Scientists, MSBI / Power BI Engineers and SQL Database Developers. This SQL Server and T-SQL Training Course is designed for both starters as well as for experienced professionals. This course includes SQL Basics, T-SQL Queries, Query Writing and Query Understanding required for Data Analysts and Business Analysts. This course also includes Programming, Tuning and Azure SQL (Cloud) concepts required for BI Developers and SQL Database Developers. One Real-time project is included in this SQL Server and T-SQL Training Course addressing Basic to Advanced SQL Queries, Detailed Database Design, Complex Joins, Common Table Expressions (CTEs), Complex Stored Procedures, Triggers, Remote Joins, Transactions and Query Tuning with XML, BLOB and JSON Data Sources with complete guidance to MCSA 70-761 and MCSA 70-762 Microsoft Certification Exams.

Practice Material, Certification, Resume & Interview Guidance are included in this SQL Server T-SQL Course.

SQL Server & T-SQL Training with Azure SQL

  PLAN A PLAN B PLAN C PLAN D
Applicable For Data Analysts SQL Dev (0-1 yrs) SQL Dev (1-2 yrs) SQL Dev (2+ yrs)
Course Duration 2.5 Weeks 3.5 Weeks 4.5 Weeks 5.5 Weeks
Completely Real-time, Practical Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Mock Interviews, Case Studies Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL Basics and Query Writing Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
SQL DB Design, Table Design Croos-symbol-for-Yes Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Normal Forms, Joins, Queries Croos-symbol-for-Yes Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Indexes, Stored Procedures Croos-symbol-for-Yes Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Advanced Stored Procedures Croos-symbol-for-No Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
CTE, PIVOT, XML and BLOB Data Croos-symbol-for-No Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Real-time Project [Banking] Croos-symbol-for-No Croos-symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
In-depth Query Tuning Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Performance Tools, Locks Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Complex SPs, MCSA - 70 761 Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure SQL Database (Cloud) Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure SQL Database Migrations Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
DB Deployments, MCSA 70-762 Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Total Course Fee INR 3000/- INR 4000/- INR 6000/- INR 8000/-

TSQL Training Schedules -- Trainer: Sai Phanindra

      Schedule (IST) Start Date  
1 6:30 AM - 7:30 AM July 24th Register
2 9:00 AM - 10:00 AM August 6th Register
3 10:15 AM - 11:15 AM June 27th Register
4 11:30 AM - 12:30 PM July 30th Register
5 6:30 PM - 7:30 PM July 1st Register
6 7:30 PM - 8:30 PM July 9th Register
7 6:00 AM - 7:30 AM (Wkn) July 13th Register
If none of the above schedules work for you, please opt for On-demand Video Training

 

SQL Server T-SQL Training Highlights

✔ Queries, Joins ✔ Dynamic SQL
✔ Query Tuning ✔ CTEs, TVPs
✔ Excel Imports ✔ In-Memory Tables
✔ Excel Pivots ✔ Azure SQL Concepts
✔ Complex joins ✔ Azure DB Migrations
All Session Are Completely Practical & Real Time
 

SQL Server T-SQL (DEV) Training Course Contents:

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

Module I: SQL Basics, SQL Server Concepts

Applicable for Plan A, B, C, D

Module II: T-SQL Queries, Programming Basics

Applicable for Plan A, B, C, D

Module III: Complex SPs, Real-time Project

Applicable for Plan B, C, D

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 and Azure SQL Database (Cloud)

Module IV: Performance Tuning & MCSA - 70 761

Applicable for Plan C, D

Module V: Azure SQL Database Development & MCSA - 70 762

Applicable for Plan D

DAY 21: QUERY TUNING 1 - QUERY AUDITS, PARTITIONS

  • 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 & 2019
  • ONLINE, RESUME, PAUSE, MAX_DURATION Options
  • Query Store - Settings and Advantages. Options
  • 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

Day 27: AZURE CLOUD & AZURE SQL DATABASE

  • Introduction to Cloud. Need for Cloud, Advantages
  • Cloud Architecture Basics - IaaS, PasS and SaaS
  • Advantages of Microsoft Cloud - Azure Platform
  • Azure Products and Azure Services - MarketPlace
  • Comparing Azure with Google Cloud for SQL Server
  • Comparing Azure with AWS Cloud for SQL Server
  • Azure Sources - Types, Microsoft Market Place
  • SQL Database Implementations in Azure Platform
  • Logical Servers, Virtual Machines, Managed Instance
  • Azure SQL Database Architecture Components
  • Creating Azure SQL Server (Logical Server)
  • Creating Azure SQL Databases and Pricing Tiers
  • Price Tiers: Basic, Standard, Premium, PremiumRS
  • DTUs Allocation for Database Size, Cost Models
  • Firewall Settings for Azure SQL Server (Logical Server)
  • Adding Firewall Rules - IP for Remote Access
  • Password Resets and Azure SQL Server Name Format

Day 22: QUERY TUNING 2 - FULL TEXT SEARCH, TABLE TYPES

  • Full Text Search (FTS) Mechanism - Architecture, Tuning
  • Stop Words, Stemmer and Thesaurus For FullText Queries
  • Indexer Program, Query Processor & FT Query Compilation
  • Database Catalogs (FTC) and FDHost.exe. Daemon Threads
  • Full Text (FT) Indexes for Query Tuning with Tokenization
  • Crawler Threads and User Tables. Filter Daemon Host Exe
  • CONTAINS() Queries and FREETEXT() Queries with SELECT
  • In-Memory Tables : Creation and Practical Usage for Tuning
  • Memory Snapshots and Database Level and Table Level
  • FileStream Files and Memory Snapshot Filegroups for MOT
  • MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT Settings
  • Manual Table Stats Updates with FullScan, NoRecompute
  • Temporal Tables : Real-time Usage and History Tracking
  • Statistics : Purpose, Creation Scenarios and Usage
  • Index Statistics and Column Statistics for Tables, ReUse
  • Comparing Triggers with Temporal Tables for Audits

DAY 28: DTU ARCHITECTURE, ELASTIC QUERIES

  • DTU : Data Transaction Units : Architecture, Pools
  • DTU - Memory and IO Resources for Reads & Writes
  • Bounding Box Model for Optimal Performance
  • Static Pools (DTU) and Elastic Pools (eDTU)
  • eDTUs and Elastic Pool, per Database Settings
  • EDTU Cost, eDTU max/min Limits and Performance
  • Configuring Elastic Pools for Azure SQL Databases
  • Elastic Pools & Tier Selection - Recommendations
  • Elastic Scale for Azure SQL Database - Strategies
  • Vertical Partitioning and Horizontal Partitioning
  • Elastic Database Tool Libraries for Elastic Queries
  • Sharding - Topology for Elastic Query Processing
  • Split-Merge Service for SaaS Software Applications
  • Elastic Database Features - ShardMap, ShardKey
  • MultiShard Queries and Elastic Transactions
  • LOOKUP, HASH and RANGE Strategies for Sharding

    Day 23: QUERY TUNING 3 - INDEX MANAGEMENT, LOCKS

    • Index Management Concepts for Performance Tuning
    • 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
    • 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

    DAY 29 : AZURE SQL DB MIGRATIONS, AZURE DATA STUDIO

    • Data Migration Assistant (DMA) Tool
    • On-premise to Azure SQL Database Migration
    • Logical Server, Virtual Machine, Managed Instance
    • Schema Generation and Compatibility For Migration
    • Generating Data Scripts and Assessment
    • Generate and Validate Schemas. Migrations
    • Migration Scopes : Schema, Data, Schema & Data
    • Compatibility Checks and Assessment Checks
    • Resolving Database Migration Compatibility Issues
    • Azure Data Studio Tool for Database Connections
    • Azure Data Studio Features - Insights, Metrics
    • Comparing Azure Data Studio with SQL OPS Tool
    • SSMS Tool for Azure SQL Database Connections
    • SQLOPS Tool for Azure SQL Database Connections
    • SSDT (Visual Studio) for SQL Database Connections
    • Query Editor for Azure Database Access - in Portal
    • Azure Database Connections with Other Tools

    Day 24: QUERY TUNING 4 - TUNING TOOLS, PERFMON

    • 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
    • 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
    • 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 Issues - Causes, Precautions and Solutions
    • Memory Issues - Causes, Precautions and Solutions
    • Performance Tuning - Final Checklist and Precautions

    DAY 30: Azure SQL DATABASE TUNING, AZURE SEARCH

    • Azure SQL Server Level Tuning Options
    • Azure SQL Database Level Tuning Options
    • Automated Tuning Options and Peak-Loads
    • Force Plan, Create Index and Drop Index
    • Query Performance Insight, Intelligence
    • Index Recommendations with CPU and IO
    • IO Metrics, CPU Metrics & Query Statistics
    • Data File IO, Log File IO, Custom Reports
    • Identify Long Running Queries, Intensive Queries
    • Query Level Recommendations and Query Costs
    • Azure Search Service - Configuration, Pricing Tiers
    • Azure Search for Data Import and Indexer Options
    • Suggester and Analyzer Index Modes for Tuning
    • Retrievable, Facetable, Filterable Indexes
    • Facetable and Searchable Indexes for Tuning
    • Change Tracking Options, Watermark Columns

    DAY 25: EXECUTION PLAN ANALYSIS and RESOURCES

    • Parameter Sniffing : Real-time Scenarios, Issues
    • Execution Plan Issues with Parameter Sniffing
    • DBCC SHOW_STATISTICS - Statistics Accuracy
    • Histograms & Density Vectors for Query Optimizer
    • RANGE_HI_KEY, EQ_ROWS and RANGE_ROWS
    • DISTINCT_RANGE_ROWS and Index Overlapping
    • sp_createstats and sp_autostats System Procedures
    • Detection and Fix of (Overlapping) Duplicate Indexes
    • Resource Governor : Real-time Usage
    • Resource Pools and Workload Groups
    • Query Priority and DOP, Grants, Classifier Function
    • Policy Based Management (PBM) : Facets and Conditions
    • Resource Governor : Real-time Usage & Conditions
    • Resource Pools : Creation and Usage Options
    • Workload Groups : Creation, Settings and Limits
    • Query Priority : LOW, MEDIUM and HIGH
    • DOP : Degree Of Parallelism and Allocation Grants

    DAY 31: AZURE SQL DB & ON-PREMISE, XEL FILES

    • Azure SQL Server Architecture Differences
    • Network Protocols and DB Engine Differences
    • File Structure and Filegroup Allocations
    • Secondary Files and FileStream Differences
    • Table Architecture for Partitions, FT Queries
    • Query Processing Differences with TDS Packets
    • Query Monitoring and Resources - Dashboards
    • Unsupported Commands with T-SQL Queries
    • SQL Traces : Creation and Audits. Limitations
    • SQL Traces : Events, Class, Category, Filter, Conditions
    • Extended Events and Traces for T-SQL Query Analysis
    • Extended Events Packages, Targets, Actions, Sessions
    • Extended Events Architecture and Engine Sessions
    • TSQL and SP Debug Events with XEL Files for EventInfo
    • Global Fields and Event Filters with XEL Files @ Traces
    • XE Objects : Catalog Views, Dynamic Management Views
    • XE Profiler - Default Templates for Event Profiling

    Day 26 : MCSA - 70 761 Exam : Pattern, Examples & Guidance

    Day 32 : MCSA - 70 762 Exam : Pattern, Examples & Guidance

     
    Above Course Curriculum is applicable for registrations from March 20th, 2019

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

    SQL Server T-SQL 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