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 wander 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 stats analysis
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
- Data files
- Log files
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
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
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 the newer types of storage: ColumnStore indexes, which mainly aim
at reporting and other Business Intelligence applications, and In-memory OLTP, which can speed up operational applications.
- Clustered and non-clustered indexes
- Monitoring allocation units
- Non-clustered and clustered columnstore indexes
- Delayed durability
- In-memory OLTP
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 its
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.
- Execution context
- Execution plans
- Plan cache and plan reuse
- Plan recompilation
- 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
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 SQL Server 2016 Query Store
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.
- Optimistic versus pessimistic concurrency control
- Locking and lock types
- Isolation levels
- Monitoring locks
Monitoring and baseline performance
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 both in Windows and in SQL Server.
- Performance monitor
- Extended Events
- Data collector
Extended events are one of the most advanced monitoring tools we have in SQL Server. Since they will replace the often used
Profiler tool they deserve more detailed attention, which they get in this module!
- Profiler versus Extended events
- Defining sessions
- Using extended events to detect common performance issues
This course is designed to give the right amount of Internals knowledge and wealth of practical tuning & optimization
techniques that you can put into production. The 5 day class offers a comprehensive coverage of SQL Server architecture,
indexing and statistics strategies, optimize transaction log operations, tempdb and data file configuration, transactions
and isolation levels, and locking and blocking. The course also teaches how to create baselines and benchmark SQL Server
performance, how to analyze workload and figure out where performance problems are, and how to fix them. The course has
a special focus on SQL Server I/O, CPU usage, memory usage, query plans, statement execution, parameter sniffing and procedural
code, deadlocking, plan cache, wait and latch statistics, Extended Events, DMVs and PerfMon.
Most topics are relevant for SQL Server 2008 and later, but some topics are SQL Server 2014 or 2016 only.
The primary audience for this course is individuals who develop, administer and maintain SQL Server databases and are responsible
for optimal performance of SQL Server Instances that they develop or manage. These individuals also write queries against
data and need to ensure optimal execution performance of the workloads.
Although the course mainly focusses on on-site SQL Servers, about 70% of the content is also relevant when dealing with