The course is part of this learning path
This course focuses on maximizing the performance of hardware and infrastructure relating to database servers. You will learn the best ways to improve an SQL server's performance and that of its databases through infrastructure choice and configuration settings.
- Learn how to set up disks for maximum performance
- Understand how to boost file performance and how to use instant file initialization
- Understand how to optimize TempDB and choose the right VM for your workloads
- Learn how to manage an instance's resources
- Learn how to configure your database as well as your SQL Server system accounts
- Understand how to use Intelligent Query Processing to optimize database performance
- Understand the benefits of using Azure SQL Serverless
- Database administrators
- Solutions architects
- Anyone interesting in improving the performance of their database
To the most out of this course, you will need to be familiar with basic Azure concepts, have some knowledge of computer hard drives and networking, and be familiar with databases and their servers.
In this course, we have looked at ways to improve an SQL server's performance and that of its databases through infrastructure choice and configuration settings. Because database servers are I/O intensive applications by nature, we started by looking at how disk choice in the context of a virtual machine can influence performance. In terms of hardware and configuration, the disk setup will be one of the biggest, if not the biggest, factor determining database speed due to how much time disk access contributes to total query execution time.
Disk I/O speed is orders of magnitude slower than RAM and a relative snail compared with CPU operation. The whole software architecture of database servers is built around query processing threads waiting for data coming from long-term storage, i.e. disk. Azure offers several hard drive options that differ in speed, performance, and price, enabling you to mix and match disks to tasks.
Apart from disk choice, Azure has easily implemented techniques for optimizing disk I/O, like disk striping and enhanced disk caching with BlobCache. While hard drives are an important factor in I/O performance, the configuration files which hold a databases' information is also critical. Distributing database files across several disks and splitting data into multiple files can mitigate file contention. TempDb, a database server's working memory, supplementing RAM, is particularly vulnerable to file contention in a multi-threaded environment, so special attention must be paid to its optimization.
By default, SQL Server now splits TempDb into multiple files based on CPU configuration, and you can further enhance performance by turning off auto-growth or enabling instant file initialization. Unlike their on-premise counterparts, virtual machines are easily scaled up and out, and if that is not configurable enough, then there are plenty of other database options like Azure SQL serverless, which provide computing resources tailored to the databases' current workload.
Resource Governor is an SQL Server feature that enables you to allocate and ration a machine's resources like CPU and RAM to ensure consistent performance in a multi-tenant environment. As SQL Server has evolved and features added, the need to preserve backward compatibility has increased as databases running on one server instance may require access to different server features.
Database compatibility levels that correspond to SQL Server versions allow you to take advantage of or restrict access to features. Many configurable database features that impact behavior and performance can be set through SQL Server management studio properties or T-SQL. As of SQL Server 2017 and continuing into 2019, Intelligent Query Processing represents a set of features that can be managed using the Alter Database Scoped Configuration command.
SQL Server is a complex and highly configurable application. Many of the Azure offerings, like platform as a service and managed instance, isolate you from that complexity allowing you to concentrate on application performance. However, if you need features that are specific to on-premise versions of SQL Server, then you should also know how to configure SQL Server to get the best performance value for money. This course has shown you where you make the most significant performance improvements but is by no means the complete story of what is possible to configure.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.