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.
Learning Objectives
- 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
Intended Audience
- Database administrators
- Solutions architects
- Anyone interesting in improving the performance of their database
Prerequisites
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.
It may seem that I've put the cart before the horse by talking about storage before the virtual machine. This is primarily due to the importance of storage in the context of databases. However, as I have already mentioned, not VMs are equal in terms of features and capabilities, like BlobCache availability. Virtual machines also have a ceiling on the maximum I/O operations per second regardless of storage configuration.
In most cases, a general-purpose machine will be suitable for SQL Server workloads. RAM tends to be more important than the number of CPU cores to maximize server performance, and VMs are typically offered in RAM to the vCPU ratios. So if you want more RAM, you will also get more vCPUs that you might not necessarily need. This is an issue as SQL Server is licensed on a per CPU basis.
Azure offers what it calls constrained vCPU VMs, where you can get the memory with only half or quarter of the vCPUs. On this configuration page, We can see a warning telling us that the virtual machines IOPs are kept at 1,280. Yet the two premium SSDs are capable of a combined 10,000 IOPs indicating the VM won't be able to take full advantage of the storage.
If you find the VM size cannot cope with an increased workload, upscaling or resizing the machine is a relatively quick and easy task, but does involve a VM restart. Resizing can be done through the portal by clicking on the size menu item under the virtual machine's settings and choosing a new size. You can see how the combination of the vCPUs, RAM, and max IOPs determines a VM's cost.
Adding more hard drive storage is equally as easy and doesn't require a restart. Select disks under settings and attach existing disks, or create a new disk and attach it. As you would expect, virtual disks attached to the virtual machine show up in Windows Disk Management as physical disks would appear on an on-premise computer.
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.