| title | Checklist: Best practices & guidelines |
|---|---|
| description | Provides a quick checklist to review your best practices and guidelines to optimize the performance of your SQL Server on Azure Virtual Machines (VM). |
| author | dplessMSFT |
| ms.author | dpless |
| ms.reviewer | mathoma, randolphwest |
| ms.date | 03/20/2025 |
| ms.service | azure-vm-sql-server |
| ms.subservice | performance |
| ms.topic | best-practice |
| tags | azure-service-management |
[!INCLUDE appliesto-sqlvm]
This article provides checklists as part of a series of best practices and guidelines to optimize performance of your SQL Server on Azure Virtual Machines (VMs). Use this guide to improve your VM configuration, storage setup, security posture, and troubleshoot common performance issues.
The checklists in this article provide a brief overview of the more comprehensive details found in the following articles of this series:
Enable SQL Assessment for SQL Server on Azure VMs and your SQL Server will be evaluated against known best practices with results on the SQL VM management page of the Azure portal.
For videos about the latest features to optimize SQL Server VM performance and automate management, review the following Data Exposed videos:
- Caching and Storage Capping
- Automate Management with the SQL Server IaaS Agent extension
- Use Azure Monitor Metrics to Track VM Cache Health
- Get the best price-performance for your SQL Server workloads on Azure VM
- Using PerfInsights to Evaluate Resource Health and Troubleshoot
- Best Price-Performance with Ebdsv5 Series
- Optimally Configure SQL Server on Azure Virtual Machines with SQL Assessment
- New and Improved SQL Server on Azure VM deployment and management experience
While running SQL Server on Azure Virtual Machines, continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environments. However, the performance of a relational database in a public cloud depends on many factors, such as the size of a virtual machine, and the configuration of the data disks.
There's typically a trade-off between optimizing for costs and optimizing for performance. This performance best practices series is focused on getting the best performance for SQL Server on Azure Virtual Machines.
Next steps: Start with the first VM size recommendations section, then proceed through Storage, Security, and SQL Server features sections for a complete optimization approach.
If your workload is less demanding, you might not require every recommended optimization. Consider your performance needs, costs, and workload patterns as you evaluate these recommendations.
If you plan to manually install SQL Server on an Azure VM, follow these essential steps to avoid common configuration issues:
- Make sure you have a product key ready for your installation.
- Avoid unsupported configurations such as:
- More than 64 vCores per NUMA node.
- Storage with 8-KB sector size.
- Azure Virtual Machine Scale Sets.
- If they don't already exist, create the folders for your SQL Server installation and data files prior to launching the installation media.
- Copy the installation media to a local drive instead of installing directly from the mounted ISO.
- After installation, register your SQL Server VM with the SQL Server IaaS Agent Extension to automate management tasks.
- Place the
tempdbdatabase on the local SSD ephemeral storage when possible.
[!INCLUDE sql-vm-deployment-failure]
The checklist in this section covers the VM size best practices for SQL Server on Azure VMs.
[!INCLUDE vm size best practices]
The checklist in this section covers the storage best practices for SQL Server on Azure VMs.
[!INCLUDE storage best practices]
The checklist in this section covers the security best practices for SQL Server on Azure VMs.
[!INCLUDE security best practices]
The following is a quick checklist of best practices for SQL Server configuration settings when running your SQL Server instances in an Azure virtual machine in production:
- Enable database page compression where appropriate.
- Enable backup compression.
- Enable instant file initialization for data files.
- Limit autogrowth of the database.
- Disable autoshrink of the database.
- Disable autoclose of the database.
- Move all databases to data disks, including system databases.
- Move SQL Server error log and trace file directories to data disks.
- Configure default backup and database file locations.
- Set max SQL Server memory limit to leave enough memory for the Operating System. (Use Memory\Available Bytes to monitor the operating system memory health).
- Enable lock pages in memory.
- Enable optimize for adhoc workloads for OLTP heavy environments.
- Evaluate and apply the latest cumulative updates for the installed versions of SQL Server.
- Enable Query Store on all production SQL Server databases following best practices.
- Enable automatic tuning on mission critical application databases.
- Ensure that all tempdb best practices are followed.
- Use the recommended number of files, using multiple
tempdbdata files starting with one file per core, up to eight files. - If available, configure the
tempdbdata and log files on the D: local SSD volume. The SQL IaaS Agent extension handles the folder and permissions needed upon reprovisioning. - Schedule SQL Server Agent jobs to run DBCC CHECKDB, index reorganize, index rebuild, and update statistics jobs.
- Monitor and manage the health and size of the SQL Server transaction log file.
- Take advantage of any new SQL Server features available for the version being used.
- Be aware of the differences in supported features between the editions you're considering deploying.
- Exclude SQL Server files from antivirus software scanning. This includes data files, log files, and backup files.
The following is a quick checklist of best practices for Azure-specific guidance when running your SQL Server on Azure VM:
- Register with the SQL IaaS Agent Extension to unlock a number of feature benefits.
- Use the best backup and restore strategy for your SQL Server workload.
- Ensure Accelerated Networking is enabled on the virtual machine.
- Use Microsoft Defender for Cloud to improve the overall security posture of your virtual machine deployment.
- Use Microsoft Defender for Cloud, integrated with Microsoft Defender for Cloud, for specific SQL Server VM coverage including vulnerability assessments, and just-in-time access, which reduces the attack service while allowing legitimate users to access virtual machines when necessary. To learn more, see vulnerability assessments, enable vulnerability assessments for SQL Server VMs and just-in-time access.
- Use Azure Advisor to address performance, cost, reliability, operational excellence, and security recommendations.
- Use Azure Monitor to collect, analyze, and act on telemetry data from your SQL Server environment. This includes identifying infrastructure issues with VM insights and monitoring data with Log Analytics for deeper diagnostics.
- Enable Autoshutdown for development and test environments.
- Implement a high availability and disaster recovery (HADR) solution that meets your business continuity SLAs, see the HADR options options available for SQL Server on Azure VMs.
- Use the Azure portal (support + troubleshooting) to evaluate resource health and history; submit new support requests when needed.
The checklist in this section covers the HADR best practices for SQL Server on Azure VMs.
[!INCLUDE HADR best practices]
When you encounter SQL Server performance issues, use these diagnostic resources to identify and resolve specific problems:
- Troubleshoot high-CPU-usage issues
- Understand and resolve blocking problems
- Troubleshoot slow-running queries
- Troubleshoot slow performance caused by I/O issues
- Troubleshoot query time-out errors
- Troubleshoot out of memory or low memory
- Performance dashboard provides fast insight into SQL Server performance state.
For detailed guidance on each optimization area:
- VM size - Choose the right VM series and configuration
- Storage - Optimize disk configuration and performance
- Security - Implement security best practices
- HADR settings - Configure high availability and disaster recovery
- Collect baseline - Establish performance baselines
Recommended tool: Enable SQL Assessment for SQL Server on Azure VMs to automatically evaluate your configuration against these best practices.
Review other SQL Server Virtual Machine articles at SQL Server on Azure Virtual Machines Overview. If you have questions about SQL Server virtual machines, see the Frequently Asked Questions.