SQL Server Performance Tuning Part 1

Download the virtual box platform, Windows Server 2016 ISO, and SQL Server 2016, plus SQL Server Management Studio, to create virtual machines for SQL Server performance tuning.

Explore the building blocks of SQL Server performance tuning and diagnose issues before hardware changes, covering slow queries, indexing types, wait stats, tempdb, and DMVs to resolve common performance problems.

Create a virtual machine with VirtualBox, install Windows Server 2016, install SQL Server 2016 with SSMS, and prepare the AdventureWorks backup file to practice SQL performance tuning.

Configure Windows Server for SQL Server 2016 by setting up a shared folder and VirtualBox guest additions, then install SQL Server 2016 on the VM.

Install SQL Server 2016 on a Windows 2016 server and optimize performance by placing data, log, and tempdb on separate drives or virtual disks using disk management.

Install SQL Server 2016 with a minimal footprint by redirecting data, log, and temp files to dedicated drives, selecting only essential features, configuring service accounts, and addressing prerequisites.

Install SQL Server Management Studio (SSMS) and use the front-end interface to control SQL Server; follow the wizard, reboot the virtual machine, and start the server.

Master restoring a database in SQL Server using SSMS, including relocating data and log files for Adventureworks 2016, and preview the first part of a two‑part performance tuning course.

Define SQL performance tuning as ensuring SQL statements and stored procedures run fastest. Identify bottlenecks and monitor with performance tools to improve throughput and reduce response time.

Learn to tune SQL Server performance by examining disks, CPU, memory, and network using performance counters; identify bottlenecks and avoid buying faster servers as a first remedy.

Build a solid foundation for sql performance tuning by exploring the many parts, including indexes and b-tree fundamentals, page fill factor and page splitting, statistics, locking, blocking, and execution plans.

Learn to configure auto growth for each production database in SQL Server, pre-size data and log files, and monitor growth to prevent pauses and fragmentation and improve performance.

Explore indexing and its role in SQL Server performance, including clustered and nonclustered indexes, types like covered, columnstore, and filtered indexes, and how DMVs identify missing or unused indexes.

Explore how indexing speeds data retrieval in SQL Server by using clustered and non-clustered indexes to avoid table scans, reduce logical reads, and optimize execution plans.

Create a table with a primary key to automatically create a clustered index. Observe inserts appearing in order under the clustered index and the primary key's impact.

Learn how non-clustered indexes are separate structures from the base table that improve performance using row locators, while noting the extra disk space required.

Understand how a b-tree index speeds data retrieval using root, intermediate, and leaf nodes. Clustered indexes store full rows at leaf nodes; non-clustered indexes point to data.

Explore how a non-clustered index works with a clustered index, using pointers to data to navigate from root to leaf and reduce table scans.

Explore the anatomy of a SQL page, an eight kilobyte storage unit with a header and data area, including data page, index page, and IMA page types and implications.

Examine how page splits affect SQL Server performance and learn to observe data and index pages during inserts.

This lecture covers clustered and non-clustered indexes, plus included, filtered, unique, and covered indexes, their use in OLTP vs OLAP, and the role of execution plans in performance tuning.

Explore how the SQL query optimizer selects the most efficient execution plan for a query in SQL Server by analyzing statistics, parsing, and generating access and execution plans.

Explore the execution plan and its operators—table scans, clustered and nonclustered index scans, sorts, joins (nested hash, merge), and key lookups—and read data flow via arrows and cost estimates.

Explore how a table scan on a heap becomes efficient with a clustered index, using where clauses and index seeks guided by execution plans and IO statistics.

Learn how non-clustered indexes improve query performance and when to apply them. See how they work with clustered indexes, cover queries, and impact execution plans.

Discover how non-clustered indexes boost performance by replacing scans with seeks, minimizing key lookups, and using covering indexes for city and address queries.

See how column order in a non-clustered covered index drives index seek over scan, and how swapping city and address line enables the optimizer to reach a seek.

Learn how non-clustered filtered indexes use a where clause to reduce disk space, boost query performance, and simplify maintenance.

Discover how the sort operator costs memory and I/O, and avoid it by creating a clustered index on order by column, letting the optimizer skip sorting, as shown with city.

Explore how nested loop, hash, and merge joins operate in production SQL databases, and learn how indexes and data ordering influence execution plans and performance.

Learn how locking in SQL Server ensures data integrity and concurrency with exclusive, shared, and update locks, blocking, and deadlocks, and how transactions pass the acid test.

Demonstrate blocking in SQL Server using two users and an open transaction with an exclusive lock. See how the second user waits until the first commits, revealing locking dynamics.

Explore how deadlocks occur when sessions lock resources and each requests the other's lock, and learn how SQL Server resolves them by killing a deadlock victim with error 1205.

Explore wait statistics, CPU cycles, and DMVs to diagnose blocking, locking, and performance issues in production SQL databases, and learn to interpret wait types and page IO latch waits.

Explore how the query optimizer compiles a stored procedure by parsing, validating, and generating an execution plan, then caching it for fast reuse. Understand recompilation and how parameterization mitigates it.

Configure tempdb for optimal performance by sizing and adding data files based on CPU cores, enabling auto growth, and placing data and log on separate drives to support temporary objects.