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.
TempDB, which is the database server's working memory, should be placed on its own drive or volume for production systems. The local SSD D drive is the preferred option as it is not persisted which also makes the D drive completely unsuitable for data or log file storage. If the local D drive isn't large enough use a Stripe to premium SSD pool with Read caching enabled. There is no knowing how TempDB could grow while performing intensive or long running processes. It is best to have it on its own volume so that if some process gets out of hand and uses up all the disc space, it won't take down log or data files. This begs the question, how should TempDB be sized and configured?
TempDB configuration is the source of some debate and not all database workload scenarios will have the same requirements. As a rule of thumb do not use is Autogrow on TempDB as the grow events will adversely impact performance. Give TempDB the same number of data files. As there are cores in the CPU up to a maximum eight. Eight is not a hard limit and multi CPUs or CPUs with lots of cores may benefit from more TempDB data files when workloads intensify.
The logic behind the files core relationship is each core, which could be running different queries or threads of the same query, shouldn't be hampered by TempDB file contention. Whatever the number of data files, they should be evenly sized taking up to 80 to 90% of the available drive space. Assuming that they are the only files on the drive. Since SQL server 2016, the default installation behavior creates as many evenly sized TempDB files as there are cause, up to a maximum of eight. Although auto growth is enabled.
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.