Tel. 02-570-8449 Mobile 088-807-9770 Payment
Mon–Fri 9:00–17:00 @itgenius
IT Genius Institute
IT Genius Institute
CoursesWorkshopSchedule
For business
In-house trainingAI Transformation (TH)Government training (TH)Development servicesTraining room rental
Articles
About us
About IT GeniusInstructorsPortfolioOur clientsPayment methods
Contact usOnline
Theme
Language
THENLA
Ask about a coursePick whichever channel suits you
LINE @itgeniusFastest reply · Mon–Fri 9:00–17:00
Call us
02-570-8449Office088-807-9770Mobile
Email & social
contact@itgenius.co.thFor quotations and documentsFacebookitgeniusonlineYouTubeitgeniusengPlan training for your team
Home / Courses / Databases / Detail
Databases · DBC-35

SQL Server Performance Tuning and Optimization

SQL Server Performance Tuning and Optimization is a course in finding and fixing slow SQL Server systems on the 2025 release, from wait statistics, memory and disk I/O to indexes, locking, execution plans and Query Store. It suits database administrators, developers who write T-SQL and people starting a database career who want to understand SQL Server in depth.

Updated
SQL Server Performance Tuning and Optimization
From 12,510 THB / person 13,900 −10% excl. VAT 7% · group rates available
Corporate training quote See open rounds
PDFDownload the course outline
  • Duration18 hours · 3 days
  • FormatOnsite / live online
  • Next round24–26 Nov 26
  • CertificateIncluded

Course overview

Systems built on SQL Server often slow down as data and users grow. Many teams respond by adding CPU or RAM, or by creating every index that is suggested, without knowing the real cause, so costs rise and the problem keeps coming back. Effective tuning starts with understanding how SQL Server works, measuring where the time actually goes, and fixing the right thing on both the administration and the development side, with baselines in place so you can spot anything unusual yourself.

This course is updated for SQL Server 2025. It starts with the internals: SQLOS, wait statistics, memory, disk I/O, the transaction log and tempdb, then builds a baseline with DMVs and Extended Events. It goes on to indexing strategy, columnstore, statistics, cardinality estimation, locking and deadlocks, including the new optimized locking, before moving into query tuning, reading execution plans, fixing parameter sensitivity, Query Store, Query Store hints and Intelligent Query Processing. Every topic has a lab on a sample database, and the course closes with a workshop that fixes a slow system from start to finish. (3 days, 6 hours per day, 18 hours in total, Intermediate level.)

What you’ll gain

  • Explain how SQLOS, memory, disk I/O and the transaction log affect performance
  • Diagnose problems with wait statistics, DMVs and Extended Events, and build a baseline
  • Plan an indexing strategy, including columnstore, and maintain indexes and statistics properly
  • Analyse and reduce blocking and deadlocks with isolation levels and optimized locking
  • Read execution plans and make queries measurably faster
  • Use Query Store, Query Store hints and Intelligent Query Processing to fix plan regressions
  • Handle more data, run faster and use fewer resources on your own systems

Who this course is for

  • SQL Server database administrators who deal with slow systems or resource shortages
  • Developers who write T-SQL and stored procedures or use an ORM with SQL Server
  • System engineers and infrastructure teams who look after database servers
  • People starting a database career who want a deeper understanding of SQL Server

Prerequisites

  • Fluent T-SQL, such as JOIN, GROUP BY, subqueries and stored procedures
  • Experience installing or looking after SQL Server and using SQL Server Management Studio
  • A basic understanding of primary keys, indexes and transactions
  • A laptop that can run SQL Server Developer Edition or Docker, with permission to install software

Curriculum

Course Details

This course is the 2026 edition of Performance Tuning and Optimization MS SQL Server, updated for SQL Server 2025. It runs for 3 days, 6 hours per day (18 hours in total, 09:00-16:00), as lectures with a lab for every topic on a sample database with load scripts. Intermediate level. It covers both the administration and the development side, from internals and diagnosis with

wait statistics and Extended Events to indexing and statistics, locking and deadlocks, query tuning, Query Store and Intelligent Query Processing. Unlike the SQL Server for Administration course, which focuses on installation and day-to-day administration, this course is about performance. Learners use SQL Server 2025 Developer Edition on their own machine and take home a lab guide, a set of diagnostic scripts and a tuning checklist.

Day 1 Internals and Diagnosing Problems

Section 1: Lab: Setting Up SQL Server 2025 and a Tuning Mindset

  • What is new for performance in SQL Server 2025 and compatibility level 170
  • Install Developer Edition on Windows or Docker, and the defaults worth changing
  • Use the latest SSMS and VS Code in place of the retired Azure Data Studio
  • A measure-first approach: symptoms, waits, queries and plans
  • Lab: restore the sample database and run the load scripts

Section 2: SQLOS, Task Scheduling and Wait Statistics

  • SQLOS, schedulers, workers and the running, runnable and suspended states
  • Read wait statistics from sys.dm_os_wait_stats and sys.dm_exec_session_wait_stats
  • What common waits such as PAGEIOLATCH, CXPACKET, LCK and WRITELOG mean
  • Separate waits that matter from waits you can ignore
  • Lab: find the main cause of a slow system from wait statistics

Section 3: Memory and Disk I/O

  • The buffer pool, plan cache and memory grants
  • Setting max server memory, and what happens when Windows or Linux runs short of memory
  • Measure file latency with sys.dm_io_virtual_file_stats
  • Memory grants that are too large or too small, and spills to tempdb
  • Lab: simulate memory pressure and read the results from DMVs

Section 4: Data Files, Pages, the Transaction Log and tempdb

  • Pages, extents and how data is stored in files
  • Transaction log architecture, VLFs and log file growth
  • Delayed durability and accelerated database recovery
  • Configuring tempdb properly, including tempdb space limits in SQL Server 2025
  • Lab: check VLFs and resize the log file of the sample database

Section 5: Lab: Measurement Tools and Building a Baseline

  • Key DMVs for sessions, requests and running queries
  • Extended Events, including sessions that stop automatically after a set time
  • Performance Monitor counters worth collecting regularly
  • Build a baseline and alert thresholds for unusual values
  • Lab: schedule baseline collection and compare before and after adding load
Day 2 Indexes, Statistics and Concurrency

Section 6: Index Architecture and Strategy

  • The B-tree structure of clustered and nonclustered indexes
  • Choosing a clustered key and the column order of composite indexes
  • Covering indexes with INCLUDE, and filtered indexes
  • The cost of indexes for INSERT, UPDATE and DELETE
  • Lab: design indexes that match the system's main queries

Section 7: Lab: Missing Indexes and Index Maintenance

  • Read the missing index DMVs with care and merge overlapping suggestions
  • Find unused indexes with sys.dm_db_index_usage_stats
  • Fragmentation, REORGANIZE, REBUILD and resumable index operations
  • Row and page data compression, and partitioning
  • Lab: build an index maintenance plan that suits the database size

Section 8: Columnstore and Other Index Types

  • Clustered and nonclustered columnstore for reporting and analytics
  • Batch mode on rowstore and columnstore
  • Ordered columnstore indexes and the improvements in SQL Server 2025
  • Combining columnstore with rowstore in OLTP systems
  • Lab: compare report query times before and after adding columnstore

Section 9: Statistics and Cardinality Estimation

  • Histograms, density, and automatic creation and updating of statistics
  • Sampling rates and maintaining statistics on large tables
  • Cardinality estimator versions and their effect on plans
  • Cardinality estimation feedback, including for expressions in SQL Server 2025
  • Lab: fix a plan with wrong row estimates caused by stale statistics

Section 10: Lab: Locking, Blocking and Deadlocks

  • Lock modes, lock escalation and transaction modes
  • Isolation levels, including read committed snapshot and snapshot
  • Optimized locking in SQL Server 2025 and what it requires
  • Capture blocking chains and deadlock graphs from system_health
  • Lab: reproduce a deadlock and fix it by changing code and indexes
Day 3 Query Tuning, Query Store and Workshop

Section 11: Compilation, the Plan Cache and Execution Plans

  • How a query is compiled, optimised and executed
  • The plan cache, recompiles and floods of ad hoc queries
  • Read actual execution plans and Live Query Statistics
  • Key operators, estimated versus actual rows, and plan warnings
  • Lab: walk through the plan of a slow query operator by operator

Section 12: Lab: Query Tuning Techniques

  • Write SARGable predicates and avoid implicit conversions
  • Unnecessary key lookups, scans and sorts
  • Parameter sniffing, Parameter Sensitive Plan optimization and OPPO
  • Query hints and plan guides when you cannot change the code
  • Lab: tune five kinds of slow query and measure with STATISTICS IO, TIME

Section 13: Query Store

  • Enable and configure Query Store to suit the system
  • The Regressed Queries and Top Resource Consuming Queries reports
  • Plan forcing and Query Store hints, including ABORT_QUERY_EXECUTION
  • Query Store on readable secondaries, and how Query Store works internally
  • Lab: detect a plan regression and fix it with Query Store

Section 14: Intelligent Query Processing

  • Memory grant feedback, and DOP feedback, which is now on by default
  • Adaptive joins, batch mode and approximate query processing
  • Optimized sp_executesql and reducing compilation storms
  • Planning a safe compatibility level upgrade with Query Store
  • Lab: enable IQP features and compare with the previous level

Section 15: Workshop: Fixing a Slow System from Start to Finish

  • Take on a slow system with several overlapping causes
  • Diagnose from waits, queries, plans, indexes and locking
  • Fix, measure against the baseline and write a summary report
  • Use an AI assistant to explain plans, and check its suggestions before applying them
  • A performance tuning checklist to take back to work

Schedule & training options

For individuals — public rounds

24–26November
Classroom24–26 Nov 26
20 seats left
19–21January
Classroom19–21 Jan 27
20 seats left
23–25March
Classroom23–25 Mar 27
20 seats left
17–19May
Classroom17–19 May 27
20 seats left
22–24June
Classroom22–24 Jun 27
20 seats left
24–26August
Classroom24–26 Aug 27
20 seats left
26–28October
Classroom26–28 Oct 27
20 seats left
7–9December
Classroom7–9 Dec 27
20 seats left

For organisations — in-house / private

  • Tailor the content to your team’s tools and projects
  • Your dates, at your office or live online
  • Quotation with tax ID for procurement
Corporate training quote

Instructors

Frequently asked questions

How do you read an execution plan in SQL Server to find what makes a query slow?

Enable the actual execution plan in SSMS and run the query. Read the plan from right to left and look for operators that handle a lot of time or rows, such as scans on large tables, many key lookups or sorts that spill to tempdb. Compare estimated rows with actual rows: a large gap usually points to stale statistics or parameter sensitivity. Also check warnings such as missing indexes or implicit conversions.

What does SARGable mean, and why does it affect query speed?

SARGable (search-argument-able) describes a WHERE condition that lets SQL Server use an index seek, such as OrderDate >= '2025-01-01'. Wrapping the column in a function, as in YEAR(OrderDate) = 2025, using LIKE with a leading %, or mismatching data types so values must be converted usually forces a scan of the whole index or table. The fix is to leave the column bare on one side of the comparison and match parameter types to the column.

What is the difference between blocking and a deadlock in SQL Server, and how does optimized locking help?

Blocking happens when one session holds a lock and another must wait; it clears when the first transaction ends. A deadlock is when two sessions each hold a lock the other needs, and SQL Server ends one of them with error 1205. Keep transactions short, access tables in the same order and use suitable indexes. SQL Server 2025 adds optimized locking, which holds fewer locks, avoids lock escalation and reduces blocking during updates. It requires accelerated database recovery to be enabled.

Who is the SQL Server Performance Tuning and Optimization course for, and what should I know beforehand?

It suits database administrators, developers who write T-SQL or use an ORM with SQL Server, infrastructure teams and anyone who wants a deeper understanding of SQL Server. You should be fluent in T-SQL, have installed or looked after SQL Server and used SSMS, and bring a laptop that can run SQL Server Developer Edition or Docker for the labs.

What will I be able to do after the SQL Server Performance Tuning and Optimization course?

You will be able to explain how SQLOS, memory, disk I/O and the transaction log work, diagnose problems with wait statistics, DMVs and Extended Events, build a baseline, plan indexes including columnstore, maintain statistics, reduce blocking and deadlocks, read execution plans and tune queries, and use Query Store, Query Store hints and Intelligent Query Processing to fix plan regressions. Participants who complete the training receive a Certificate of Completion with its own number, which can be verified online.

Can the SQL Server Performance Tuning and Optimization course be run in-house for our team?

Yes. It can be run in-house for your team, with examples adapted to your own work, such as analysing queries and indexes based on the workloads your systems actually handle. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.

Related articles

Articles are published in Thai.