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.