SQL Server Performance Tuning Part 2

Master production SQL server performance tuning by addressing tempdb configuration, index tuning, page splits, and wait stats. Build on SQL administration and performance tuning prerequisites.

download and set up the required software for SQL performance tuning part two, including VirtualBox, Windows Server 2016, and SQL Server 2017, and configure the virtual environment for testing.

Create a virtual machine in Oracle VirtualBox, install Windows Server 2016, allocate 2 GB RAM and a 40 GB fixed disk, and prepare the OS for SQL performance tuning training.

install sql server 2017 part 2, disable python and polybase, install sql server management studio 17.4, and compare 2017 with 2016 and older versions for performance tuning prep.

Compare SQL Server versions from 2014 to 2017, showing identical t-sql behavior across versions and noting Linux deployment is possible with newer releases.

Explore the Windows Performance Monitor (perfmon) to monitor SQL Server performance. Learn to add counters and create data collection sets for SQL Server CPU, memory, network, and disk.

Explore essential performance counters across cpu, memory, network, and disk to diagnose SQL Server issues, including buffer cache hit ratio, page life expectancy, batch requests per second, and locks.

Create and save performance monitor counter sets with MMC to automate monitoring of memory, network, disk, and SQL counters for daily baselines.

Create and manage data collection sets with Perfmon to build a baseline for SQL Server performance, capturing counters like CPU, memory, disk, and network for time-based comparison.

Create a manual data collector set, choose memory and CPU counters, set a 10-second interval, save as CSV, and schedule weekly collection to build a quarterly performance baseline.

Learn how to restore the AdventureWorks 2017 database on SQL Server 2017 and set up a data collection set for performance monitoring.

Create a baseline of sql server and windows performance metrics with perfmon data collector sets, export to csv, and analyze averages, mins, and maxes to guide tuning decisions.

Create a baseline CPU with the SQL Query Stress Utility, stress the SQL Server, and compare the baseline and stress data to identify CPU bottlenecks.

Learn to use sql profiler with the perfmon to correlate cpu spikes to hardware resource use, while noting profiler's deprecation and the shift to extended events.

Use SQL profiler to limit data by columns and filters, saving traces to file or table to reduce resource use and focus on the target database.

Correlate performance monitor with SQL profiler to identify queries causing CPU plateau, capturing traces and importing data to pinpoint the culprit query.

Explore SQL Server extended events and why they replace profiler for production tuning, with a lightweight GUI-based setup to track security, long-running queries, page splits, and locks.

Explore extended events in SQL Server, create and filter event sessions, capture SQL text, database name, and user details, and analyze security and long-running queries for performance tuning.

Learn to create and alter extended events sessions with T-SQL, add events such as database created, started, stopped, and login, start the session, and monitor live data.

Identify the longest running queries with extended events, add useful columns, and use dmvs to monitor sessions and events for production performance with lightweight overhead.

Learn how DMVs and DMFs monitor SQL Server performance across server- and database-scoped views to reveal indexing, wait stats, and cpu, memory, and disk usage with practical examples.

Set up and use Query Store to monitor SQL Server performance, including creating a stretch database, migrating data with the export wizard, and preparing the environment for Query Store demonstrations.

Learn how to use query store in SQL Server 2016 to capture and view historical data for queries, execution plans, statistics, and workload changes, enabling troubleshooting of long running queries.

Tune SQL Server performance by configuring and monitoring query store, adjusting max size and capture modes, and analyzing regressed and top resource queries with DMVs and execution plans.

Learn how the query store enables a forced plan, comparing execution plans for the same query and enforcing the best plan. Observe how a non-clustered index lowers logical reads.

Force the most efficient query plan by selecting plan 13 via UI or a force plan, then create a composite index and reduce logical reads with the query store.

Explore how updates cause index fragmentation, measure it with sys.dm_db_index_physical_stats, and resolve it using reorganize or rebuild, considering fill factor 90 and maintenance automation.

Create an automated index fragmentation plan using maintenance plan, SQL agent job, or Ola's script; configure rebuild/reorganize options, online indexing, and scheduling to manage fragmentation across multiple indexes.

Learn to create and run SQL agent jobs for index maintenance, compare maintenance plans with manual execution, and implement Ola Hallengren scripts to optimize fragmentation across databases.

Learn to use the query store and database tuning advisor to identify and apply targeted indexing across a single database, turning workload data into optimized, tested indexes.

Explore how compilation and recompilation affect SQL Server performance and CPU usage, and learn to monitor and reduce them using SQL Profiler, DMVs, and execution plan caching.

Learn how interleaved DDL and DML in a stored procedure triggers recompilations, and how to monitor them with Perfmon, SQL Profiler, and Extended Events to optimize performance.

explores how page splits occur during mid-page inserts, explains how fill factor affects data and index pages, and demonstrates remedies to minimize splits and IO in OLTP workloads.

Explore how data and index pages fill toward 8060 bytes, triggering page splits during inserts, and use dbcc page and performance monitor to observe and differentiate good and bad splits.

Explore viewing page splits with extended events to troubleshoot performance, using scripted T-SQL sessions and live data from performance monitor counters.

Explore blocking and locking in SQL Server, diagnose open transactions with extended events, sp_who, DMVs, and performance monitors, and resolve issues caused by poor indexing, bad queries, or limited resources.

Explore tempdb's role as a busy system database and learn how to improve performance by moving to a fast separate drive, sizing files, adding data files, and tuning auto growth.

Move the tempdb from the C drive to a dedicated fast drive, restart services, and add extra tempdb data files based on CPU count up to eight to boost performance.

Resolve backup performance issues by scheduling heavy database backups after business hours, monitoring duration, using compression to save time and space, and leveraging always on replica backups for production.

Explore how wait stats diagnose SQL Server performance issues in SQL Server Performance Tuning Part 2, using dm_os_wait_stats, extended events, and top wait types to identify locks and processor bottlenecks.