Course Intro & Overview
Storage, Disks & Files
SQL for Azure
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.
Enabling instant file initialization is another way to mitigate the adverse effects of file growth events. SQL Server's default behavior when creating or growing a file is to initialize the new file space on the disk with zeros. This is primarily a security feature to erase any information left by previously deleted files.
File initialization happens when creating a database, adding log or data files to an existing database, increasing the size of a file, auto-growth, and restoring a database or filegroup. Find out if instant file initialization is on by running xp_readerrorlog with a search parameter of Database Instant File Initialization, or by querying the instant file initialization enabled field of sys.dm_server_services.
Enabling this feature will significantly speed up disk space allocation, thereby improving the performance of auto-growth. Microsoft does recommend enabling this feature, saying that the benefit far outweighs any security risks. Instant File Initialization has to be enabled through local security policy by adding the SQL Service account to Perform volume maintenance tasks under local policies user rights assignment. After enabling instant file initialization, the queries reflect the new setting
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.