title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | tags |
---|---|---|---|---|---|---|---|---|---|
Checklist: Best practices & guidelines |
Provides a quick checklist to review your best practices and guidelines to optimize the performance of your SQL Server on Azure Virtual Machines (VM). |
bluefooted |
pamela |
mathoma, randolphwest |
03/11/2024 |
virtual-machines-sql |
performance |
conceptual |
azure-service-management |
[!INCLUDE appliesto-sqlvm]
This article provides a quick checklist as a series of best practices and guidelines to optimize performance of your SQL Server on Azure Virtual Machines (VMs).
For comprehensive details, see the other articles in this series: VM size, Storage, Security, HADR configuration, Collect baseline.
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. 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.
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
tempdb
data files starting with one file per core, up to eight files. - If available, configure the
tempdb
data 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]
The following is a list of resources that help you further troubleshoot SQL Server performance issues.
- 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.
Consider enabling SQL Assessment for SQL Server on Azure VMs.
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.