Storage Optimization for SQL Server on Azure VMs

On this page:

Introduction

This tutorial provides guidance on optimizing storage performance for SQL Server instances deployed on Azure Virtual Machines. Efficient storage is critical for maintaining high SQL Server performance, especially for I/O-intensive workloads. By following these best practices, you can ensure your SQL Server VMs are configured for optimal throughput and latency.

Optimizing storage involves understanding Azure's storage offerings, selecting the appropriate disk types, configuring disks effectively, and implementing best practices for data, log, and TempDB placement.

Understanding Azure Storage Types

Azure offers various types of managed disks, each with different performance characteristics and cost implications. Choosing the right disk type is the first step towards optimization.

Choosing the Right Disks

For SQL Server VMs, the general recommendation is to use Premium SSDs for your data and log files. Consider the following when making your selection:

Disk Configuration Best Practices

Proper disk configuration significantly impacts performance. When setting up your VM, consider these best practices:

Recommendation:

For most SQL Server deployments, a common and effective configuration is to use separate Premium SSDs for your SQL Server data files, transaction log files, and TempDB. This provides a good balance of performance and manageability.

Separating Data and Log Files

This is a fundamental principle for SQL Server performance. Placing data files and transaction log files on separate physical disks (or virtual disks in Azure) allows SQL Server to perform read and write operations concurrently without contention.

-- Example of creating a database with separate data and log files
CREATE DATABASE MyDatabase
ON
( NAME = MyDatabase_dat,
  FILENAME = 'D:\Data\MyDatabase_dat.mdf', -- Assuming D: is your data drive
  SIZE = 100MB,
  FILEGROWTH = 5MB )
LOG ON
( NAME = MyDatabase_log,
  FILENAME = 'E:\Logs\MyDatabase_log.ldf', -- Assuming E: is your log drive
  SIZE = 50MB,
  FILEGROWTH = 10MB );

Optimizing TempDB

TempDB is a global resource used by all connections and stored procedures for temporary tables, work tables, temporary LOB storage, version stores, and more. Its performance can significantly impact overall SQL Server performance.

-- Example for pre-sizing and creating multiple TempDB files (SQL 2016+)
-- This is often handled automatically by SQL Server, but manual control is possible.
-- Check current configuration:
SELECT name, physical_name, size, max_size, growth FROM sys.master_files WHERE database_id = DB_ID('tempdb')

-- To add more files (example, adjust paths and sizes):
ALTER DATABASE tempdb
ADD FILE (NAME = tempdb_data_2, FILENAME = 'F:\TempDB\tempdb_data_2.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);

ALTER DATABASE tempdb
ADD FILE (NAME = tempdb_data_3, FILENAME = 'F:\TempDB\tempdb_data_3.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);

-- Ensure all files are roughly the same size for optimal performance.

Advanced Storage Performance Tuning

Beyond basic disk selection and placement, consider these advanced techniques:

Monitoring Storage Performance

Continuous monitoring is key to identifying storage bottlenecks and ensuring optimal performance.

-- Query to check I/O statistics for database files
SELECT
    DB_NAME(database_id) AS DatabaseName,
    name AS FileName,
    io_stall_read_ms / NULLIF(num_of_reads, 0) AS AvgReadLatencyMs,
    io_stall_write_ms / NULLIF(num_of_writes, 0) AS AvgWriteLatencyMs,
    io_stall / NULLIF(num_of_bytes_read + num_of_bytes_written, 0) AS AvgTotalLatencyMs,
    num_of_reads,
    num_of_writes,
    num_of_bytes_read,
    num_of_bytes_written,
    io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall DESC;