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.
As more and more features get added to SQL Server, more of those features trickle down to database level configuration. Not only does that give greater control and flexibility, but as new versions of the database engine are released, the need for backward compatibility increases. Compatibility level is probably one of the most crucial database settings because it determines what features a database has available to it.
More importantly, from a performance perspective, how the query optimizer works. For example, most intelligent query processing features became available in SQL Server 2019. So compatibility level 150 is enabled by default. Obviously, you cannot set a database to a compatibility level exceeding that of the server that it runs on. In this example, we have a VM running SQL Server 2017, so compatibility level 140.
Looking at the database properties, we could set the database to either 2017 compatibility or go as far back as Server 2008. We cannot fix the database to run as a 2019 version. Database features can be configured through the database properties window or using T-SQL Alter Database and Alter Database scoped Configuration commands.
In this example, Alter Database Set Query Store On is enabling the database query store, while Alter Database Scoped Configuration Set MaxDop equals one is changing MaxDop, the maximum degree of parallelism from the default of zero to one. Setting MaxDop on the database supersedes the server setting for that database and is another way of potentially rationing CPU resources.
Once the query store has been enabled, you can set Automatic Tuning to force the use of the last good plan when a query shows signs of regression. Last query plan stats is another feature only available with compatibility level 150. It keeps a record of the last actual execution plan stats for a query. Legacy cardinality estimation provides backward compatibility in the case of queries experiencing performance degradation when running under the upgraded cardinality estimator that came out with SQL Server 2014.
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.