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

Indexes and Statistics

This is the most important tool to improve SQL Server performance. We first discuss for each of the three basic storage options (heaps, clustered indexes and non-clustered indexes) how the data is stored and the influence of this on SELECT, INSERT, UPDATE and DELETE statements. Then we switch over to how statistics are used by the SQL Server Query Optimizer to decide which index to use when queries are being executed by SQL Server.

• Heaps
• Clustered and Non-Clustered Indexes
• Monitoring Allocation Units
• Creating Filtered Indexes
• Indexes and Statistics
• Reading Statistics
• How the Query Optimizer Uses Statistics
• Using Filtered Statistics
• Working with Indexed Views
• Configuring Delayed Durability
• LAB: Working with Clustered, Non-Clustered Indexes and Statistics

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. This is done using the traditional techniques we already have in SQL Server for many years as well as with the Query Store, which is new since SQL Server 2016 and is also available in Azure SQL Databases.

• Execution Context
• Execution Plans
• Plan Cache and Plan Reuse
• Plan Recompilation
• Parameterization
• Query Store
• Adaptive Query Processing
• Automatic Regressed Plan Detection
• LAB: Analyzing Query Plans

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

Improved Cardinality Estimator

The SQL Server cardinality estimator uses statistics to make an estimate of the number of rows returned by operations such as joins and filters. These estimates are then used by the query optimizer to build execution plans. Microsoft changed in the 2014 and 2016 version of SQL Server how these estimates are computed. In this module we dive into these changes, discuss the overall benefit of the new estimates, but also discuss how you can keep on using the old ones if they did a better job for certain queries.

• Cardinality Estimates
• Improvements in the New Cardinality Estimator
• Which Estimator is Used?
• Discovering Slower Queries with the Query Store
• LAB: Monitoring Cardinality Estimator Changes

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