SQL Server Performance Tuning and Optimization

Introduction into SQL Performance tuning

In a course like this many different topics are discussed, so we start this training by briefly discussing the different topics and show how they relate to each other.

CPU and Process Scheduling

Sometimes people wonder if their SQL Server needs more CPU power. In this module we see how SQL Server schedules queries to workers for running them on a thread. A very important concept is looking into wait statistics, where we basically learn to ask SQL Server what it's waiting upon.

• Threads and Workers
• Worker Thread Binding
• Wait Statistics Analysis
• LAB: Analyzing Wait Statistics

IO and Database Structure

SQL Server stores its data on disk. In this module we discuss how data for regular data structures is stored, how the data can be spread over multiple disks and we discuss common performance pitfalls people encounter when they setup a SQL Server database.

• Databases in SQL Server
• Working with Data files
• Using and Configuring Filegroups
• Log Files
• LAB: Working with Databases

Memory Use

SQL Server cannot run queries on data stored on disk. It must first be loaded into main memory before it can be used. But how does SQL Server decide how long to cache data in memory, how can we inspect what data is cached right now, and what else besides data is kept in memory? These are the questions we answer in this module.

• Dynamic Memory Allocation
• Memory Consumers
• Monitoring Memory Consumption
• LAB: Monitoring Memory Usage

Data Types and Tables

When developing the tables within a database we have to take care as well. In this module we discuss the impact that data types have on the size of a row, and bigger rows often result in slower queries. Another thing to worry about are the implicit data type conversions, which can cause SQL Server a lot of extra work, or can even result in SQL Server not being able to use some indexes.

• Importance of Data Type Selection
• Storage Cost
• Variable versus Fixed Length
• Implicit and Explicit Data Type Conversions
• LAB: Optimizing Data Types and Tables

Heaps, Clustered and Nonclustered Indexes

The way data is stored on disk and in memory has a huge impact on the performance of both read operations as well as operations that modify the data. In SQL Server, indexes determine how you store your tables. This module explains how the 3 most common indexes work: Heaps, Clustered index and Nonclustered indexes.

• Disecting data files in Pages and Extends
• How Heaps influence data operations
• Creating a Clustered Index
• The need for Nonclustered Indexes
• LAB: Speed up queries with Clustered and Nonclustered Indexes

Advanced index features

In this module you dive deeper in the way clustered on nonclustered indexes work. You see how statistics help decide which index to use, and why you might want to filter your indexes and statistics. Finally, you will learn how indexing a view can help in computing subtotals much faster.

• Monitoring Allocation Units
• Creating Filtered Indexes
• Indexes and Statistics
• Reading Statistics
• How the Query Optimizer Uses Statistics
• Using Filtered Statistics
• Working with Indexed Views
• LAB: Filtered indexes, Statistics and Indexed Views

Query Execution and Query Plans

Having an index is one thing, using the index is another story: How can we see which indexes SQL Server uses and how it's using them? Execution plans are the answer to that question. We discuss in this part of the training how to get execution plans and how to analyze them.

• Execution Context
• Execution Plans
• Plan Cache and Plan Reuse
• Plan Recompilation
• Parameterization
• Cardinality Estimator versions
• LAB: Analyzing Execution plans

Working with the Query Store

The SQL Server Query Store logs every query executed, and helps to get insights on which queries are slow, how frequently they run, how many different execution plans they use, and much more.

• Activating the Query store
• Using the Query Store reports
• Inspecting the Query Store views
• Adaptive Query Processing
• Automatic Regressed Plan Detection
• LAB: Working with the Query Store

Index and Query Tuning

This module combines the skills we gained in the two previous modules. We see how changing queries, indexes and constraints has an influence on the execution plan ad performance of a query.

• Understanding the Query Plan Operators
• How to Measure Query Cost
• Common Query Tuning Techniques
• LAB: Index and Query Tuning

Concurrency and Transactions

A database must store data in a consistent way. But if everybody can change all the data in parallel, we lose transactional consistency. This module discuss how SQL Server provides us with some options for allowing sessions in parallel to access the same data yet keeping this data transactional consistent.

• Transactions
• Optimistic versus Pessimistic Concurrency Control
• Locking and Lock Types
• Isolation Levels
• Monitoring Locks
• Deadlocks
• LAB: Working with Transactions and Transaction Isolation Levels

Monitoring SQL Server

To apply performance optimizations in practice we must first monitor the SQL Server to identify the types of performance problems we have. But ideally we start monitor the SQL Server before problems arrive. This way we establish a baseline against which we can compare the monitored values when things start to go wrong. In this module we discuss different types of monitoring tools in SQL Server.

• Performance Monitor
• Profiler
• Configuring Extended Event Sessions
• LAB: Configuring Extended Events

Working with ColumnStore Indexes

The main usage of ColumnStore indexes is to improve query performance for data warehouses and data marts workloads. This chapter describes how ColumnStore indexes store data in a columnar format instead of the row-based storage that is used by 'classic' tables and indexes in SQL Server. Then you will learn how to create columnstore indexes and strategies for using them in On-Premise and Azure SQL Databases.

• Introduction to Columnar Data Storage
• Introduction to Rowgroups and Segments
• Creating Clustered ColumnStore Indexes
• Creating Non-Clustered ColumnStore Indexes
• Fragmentation in ColumnStore Indexes
• Real-time Operational Analytics
• LAB: Working with columnstore Indexes

Working with In-Memory OLTP

In-Memory OLTP can significantly improve the performance of transaction processing, data ingestion and data load, and transient data scenarios in SQL Server on-premise and Azure SQL Databases. In-Memory OLTP improves performance of transaction processing tables by removing lock and latch contention between concurrently executing transactions.

• Planning for In-Memory Tables
• Creating In-Memory Tables
• In-Memory Tables and Indexes
• Working with Native Compiled Stored Procedures