Call Us: +32 2 466 00 16
Email: info@u2u.be
Follow Us:

SQL Server Performance Tuning and Optimization

5days
Training code
USQLOP
Book this course

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 database.

  • Databases
  • Data files
  • Filegroups
  • Log files

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

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.

  • Heaps
  • 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
  • Parameterization
  • 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.

  • Transactions
  • 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
  • Profiler
  • Extended Events
  • Data collector

Extended Events

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 Azure databases.

© 2018 U2U All rights reserved.