Best Server Machine

Best Server Machine Price Huawei Fusionserver Rh8100 V3 Intel Xeon

The following is a quick check list for optimal performance of SQL Server on Azure Virtual Machines:

Area Optimizations
DS3 or higher for SQL Enterprise edition.
Use Premium Storage. Standard storage is only recommended for dev/test.
Use a minimum of 2 P30 disks (1 for log files; 1 for data files and TempDB).

Avoid using operating system or temporary disks for database storage or logging.

Enable read caching on the disk(s) hosting the data files and TempDB.

Do not enable caching on disk(s) hosting the log file.

Important: Stop the SQL Server service when changing the cache settings for an Azure VM disk.

Stripe multiple Azure data disks to get increased IO throughput.

Format with documented allocation sizes.

Enable database page compression.

Enable instant file initialization for data files.

Limit or disable autogrow on the database.

Disable autoshrink on the database.

Move all databases to data disks, including system databases.

Move SQL Server error log and trace file directories to data disks.

Setup default backup and database file locations.

Enable locked pages.

Apply SQL Server performance fixes.

Back up directly to blob storage.

For more information on how and why to make these optimizations, please review the details and guidance provided in following sections.

VM size guidance

  • SQL Server Enterprise Edition: DS3 or higher
  • SQL Server Standard and Web Editions: DS2 or higher
  • Warning

    Standard Storage has varying latencies and bandwidth and is only recommended for dev/test workloads. Production workloads should use Premium Storage.

    In addition, we recommend that you create your Azure storage account in the same data center as your SQL Server virtual machines to reduce transfer delays. When creating a storage account, disable geo-replication as consistent write order across multiple disks is not guaranteed. Instead, consider configuring a SQL Server disaster recovery technology between two Azure data centers. For more information, see High Availability and Disaster Recovery for SQL Server in Azure Virtual Machines.

    Disks guidance

    There are three main disk types on an Azure VM:

  • OS disk: When you create an Azure Virtual Machine, the platform will attach at least one disk (labeled as the C drive) to the VM for your operating system disk. This disk is a VHD stored as a page blob in storage.
  • Temporary disk: Azure Virtual Machines contain another disk called the temporary disk (labeled as the D: drive). This is a disk on the node that can be used for scratch space.
  • Data disks: You can also attach additional disks to your virtual machine as data disks, and these will be stored in storage as page blobs.
  • The following sections describe recommendations for using these different disks.

    Operating system disk

    An operating system disk is a VHD that you can boot and mount as a running version of an operating system and is labeled as C drive.

    Default caching policy on the operating system disk is Read/Write. For performance sensitive applications, we recommend that you use data disks instead of the operating system disk. See the section on Data Disks below.

    Temporary disk

    The temporary storage drive, labeled as the D: drive, is not persisted to Azure blob storage. Do not store your user database files or user transaction log files on the D: drive.

    For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (e.g. for temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.

    For VMs that support Premium Storage (DS-series, DSv2-series, and GS-series), we recommend storing TempDB on a disk that supports Premium Storage with read caching enabled. There is one exception to this recommendation; if your TempDB usage is write-intensive, you can achieve higher performance by storing TempDB on the local D drive, which is also SSD-based on these machine sizes.

    Data disks

  • Use data disks for data and log files: At a minimum, use 2 Premium Storage P30 disks where one disk contains the log file(s) and the other contains the data and TempDB file(s). Each Premium Storage disk provides a number of IOPs and bandwidth (MB/s) depending on its size, as described in the following article: Using Premium Storage for Disks.
  • Disk Striping: For more throughput, you can add additional data disks and use Disk Striping. To determine the number of data disks, you need to analyze the number of IOPS and bandwidth required for your log file(s), and for your data and TempDB file(s). Notice that different VM sizes have different limits on the number of IOPs and bandwidth supported, see the tables on IOPS per VM size. Use the following guidelines:

    1. Set the interleave (stripe size) to 64 KB (65536 bytes) for OLTP workloads and 256 KB (262144 bytes) for data warehousing workloads to avoid performance impact due to partition misalignment. This must be set with PowerShell.
    2. Set column count = number of physical disks. Use PowerShell when configuring more than 8 disks (not Server Manager UI).

    For example, the following PowerShell creates a new storage pool with the interleave size to 64 KB and the number of columns to 2:

    $PoolCount = Get-PhysicalDisk -CanPool $True $PhysicalDisks = Get-PhysicalDisk | Where-Object {$_.FriendlyName -like "*2" -or $_.FriendlyName -like "*3"} New-StoragePool -FriendlyName "DataFiles" -StorageSubsystemFriendlyName "Storage Spaces*" -PhysicalDisks $PhysicalDisks | New-VirtualDisk -FriendlyName "DataFiles" -Interleave 65536 -NumberOfColumns 2 -ResiliencySettingName simple –UseMaximumSize |Initialize-Disk -PartitionStyle GPT -PassThru |New-Partition -AssignDriveLetter -UseMaximumSize |Format-Volume -FileSystem NTFS -NewFileSystemLabel "DataDisks" -AllocationUnitSize 65536 -Confirm:$false

  • For Windows 2008 R2 or earlier, you can use dynamic disks (OS striped volumes) and the stripe size is always 64 KB. Note that this option is deprecated as of Windows 8/Windows Server 2012. For information, see the support statement at Virtual Disk Service is transitioning to Windows Storage Management API.
  • If your workload is not log intensive and does not need dedicated IOPs, you can configure just one storage pool. Otherwise, create two storage pools, one for the log file(s) and another storage pool for the data file(s) and TempDB. Determine the number of disks associated with each storage pool based on your load expectations. Keep in mind that different VM sizes allow different numbers of attached data disks. For more information, see Sizes for Virtual Machines.
  • Caching policy: For Premium Storage data disks, enable read caching on the data disks hosting your data files and TempDB only. If you are not using Premium Storage, do not enable any caching on any data disks. For instructions on configuring disk caching, see the following topics: Set-AzureOSDisk and Set-AzureDataDisk.

    Stop the SQL Server service when changing the cache setting of Azure VM disks to avoid the possibility of any database corruption.

  • Related posts: