Performance best practices for SQL Server in Azure Virtual Machines

Quick check list

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

Area	Optimizations
VM size	DS3 or higher for SQL Enterprise edition. DS2 or higher for SQL Standard and Web editions.
Storage	Use Premium Storage. Standard storage is only recommended for dev/test. Keep the storage account and SQL Server VM in the same region. Disable Azure geo-redundant storage (geo-replication) on the storage account.
Disks	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.
I/O	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.

Go to here for more details

How to add additional disk to current Azure VM

Let see how we can add additional disks:

Adding an additional disk to an Azure VM was too easy. Let's see steps below.

1. Login to the Azure Management Portal here
2. In the Azure Management portal, click VM in the list of options on the left
3. In the VM pane, click the VM you want to attach a disk to.
4. A dashboard for the VM will appear, and the VM settings panel to the left. in the settings panel, click Disks under GENERAL
5. Select attach new

6.  Select appropriate name for Disk under Name
7. Select required type Standard or SSD
8. Provide Size required in GB
9. select location and host caching if required for your environment
10. Click OK

11. wait for few minutes, once tasks completed you will see a notification appear on the top right of the management portal.

How to add additional user to Azure Subscription

1. 1. Login to portal.azure.com 
2. 2. Search by subscription 
3. 3. Select right subscription name 
4. 4. Select Access Control(IAM) 
5. 
   5. Click on Add
6. 
   
7. 6. Select role you want to provide and provide Microsoft email  and click on save
8. 
   
9. 
   
   
10. 7. That all done. Now new user can access portal with provided permissions

Find SQL Server Database file I/O waits

SELECT  DB_NAME(database_id) AS [Database Name] , 

        file_id AS [File ID], 

        io_stall_read_ms AS [Total Read Waits (ms)], 

        num_of_reads AS [Number of Reads], 

        CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) 

            AS [Average Read Wait (ms)] , 

        io_stall_write_ms AS [Total Write Waits (ms)], 

        num_of_writes AS [Number of Writes], 

        CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) 

            AS [Average Write Wait (ms)] , 

        io_stall_read_ms + io_stall_write_ms AS [Total I/O Waits (ms)] , 

        num_of_reads + num_of_writes AS [Number of I/O Operations] , 

        CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads 

                                                          + num_of_writes) 

           AS NUMERIC(10,1)) AS [Average I/O Wait (ms)] 

FROM    sys.dm_io_virtual_file_stats(NULL, NULL) 

ORDER BY [Average I/O Wait (ms)] DESC ;

Check SQL Server MAX DOP Value using SQL

declare @hyperthreadingRatio bit 

declare @logicalCPUs int 

declare @HTEnabled int 

declare @physicalCPU int 

declare @SOCKET int 

declare @logicalCPUPerNuma int 

declare @NoOfNUMA int 

  

select @logicalCPUs = cpu_count -- [Logical CPU Count] 

    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio] 

    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count] 

    ,@HTEnabled = case  

        when cpu_count > hyperthread_ratio 

            then 1 

        else 0 

        end -- HTEnabled 

from sys.dm_os_sys_info 

option (recompile); 

  

select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma] 

from sys.dm_os_schedulers 

where [status] = 'VISIBLE ONLINE' 

    and parent_node_id < 64 

group by parent_node_id 

option (recompile); 

  

select @NoOfNUMA = count(distinct parent_node_id) 

from sys.dm_os_schedulers -- find NO OF NUMA Nodes  

where [status] = 'VISIBLE ONLINE' 

    and parent_node_id < 64 

  

-- Report the recommendations .... 

select 

    --- 8 or less processors and NO HT enabled 

    case  

        when @logicalCPUs < 8 

            and @HTEnabled = 0 

            then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3)) 

                --- 8 or more processors and NO HT enabled 

        when @logicalCPUs >= 8 

            and @HTEnabled = 0 

            then 'MAXDOP setting should be : 8' 

                --- 8 or more processors and HT enabled and NO NUMA 

        when @logicalCPUs >= 8 

            and @HTEnabled = 1 

            and @NoofNUMA = 1 

            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3)) 

                --- 8 or more processors and HT enabled and NUMA 

        when @logicalCPUs >= 8 

            and @HTEnabled = 1 

            and @NoofNUMA > 1 

            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3)) 

        else '' 

        end as Recommendations