How storage options in Azure impact SQL Server performance

SQL Server in azure using an IaaS deployment comes with many pros (and cons) over PaaS offerings. One of the nice things about IaaS is the full control you have over SQL Server and to a large extent the infrastructure it’s deployed on. There are many different VM SKU’s to suit your instances requirements as well as a handful of storage options. This blog will focus on storage and show you how it can be make or break for your sql server in Azure.

For IaaS the most commonly used storage is an Azure managed disk, of which there are 5 types; Ultra, PremiumV2, Premium SSD, Standard SSD and Standard HDD.

A breakdown of their capabilities at the time of writing can be seen below:

Feature	Ultra Disk	Premium SSD v2	Premium SSD	Standard SSD	Standard HDD
Disk Type	SSD	SSD	SSD	SSD	HDD
Scenario	IO-intensive workloads such as SAP HANA, top-tier databases (SQL, Oracle), and other transaction-heavy workloads.	Production and performance-sensitive workloads that require consistently low latency and high IOPS/throughput.	Production and performance-sensitive workloads	Web servers, lightly used enterprise apps, dev/test	Backup, non-critical, infrequent access
Max Disk Size	65,536 GiB	65,536 GiB	32,767 GiB	32,767 GiB	32,767 GiB
Max Throughput	10,000 MB/s	1,200 MB/s	900 MB/s	750 MB/s	500 MB/s
Max IOPS	400,000	80,000	20,000	6,000	2,000 – 3,000*
Latency (Claimed)	‘low sub millisecond’	‘~1 ms’sub millisecond’	‘single-digit millisecond latencies’	‘single-digit millisecond latencies’	Write <=10ms Read <= 20ms
Usable as OS Disk?	No	No	Yes	Yes	Yes

Test

Azure generally does what it says on the tin throughput wise. If you crank the dial to 500MBps that’s the throughput you can get. Latency is where it can get interesting though, and it’s interesting to note there are no SLA’s attached to storage latency.

As such lets take a look at how these different disks perform when inserting 50,000 integers sequentially into a single int column table; paying specific attention to write latency and writelog waits to see if there is any correlation and its impact on query execution times.

This test is loosely indicative of how storage choices can affect sql server write operations commonly found in daily processes with lots of sequential writes

All tests were also run on a Standard E8bds v5 (8 vcpus, 64 GiB memory) deployed from a marketplace image which has a max uncached Ultra Disk and Premium SSD V2 disk throughput IOPS/MBps of 44200/1200 and 33000/930 for premium. Log file throughput never reached disk or vm throughput limits adding weight to the latency correlation.

Summary

Storage	Latency ms	Insert Time
Pv2	0.6	36 seconds
Ultra	0.9	45 seconds
Premium SSD	2	1 minute 44
Standard HDD	4	3 Minutes 32

As you can see there is a strong correlation between the disks write latency and the time it takes for the query to execute, further backed up by the fact most of the session waits are writelog.

“This wait type is when a thread is waiting for a log block to be written to disk by an asynchronous I/O.”

Another interesting thing to note is I have consistently seen Pv2’s outperform ultra disk in log file write latency’s. However, ultra’s do have lower read latency’s.

Scripts used for the test can be seen below:

CREATE TABLE [dbo].[insert_test](
[col1] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[insert_test](

[col1] [int] NULL

) ON [PRIMARY]

Scripts to run the insert and look at session wait statistics

IF EXISTS
    (
        SELECT *
        FROM [tempdb].[sys].[objects]
        WHERE [name] = N’##SQLskillsStats10′
    )
        DROP TABLE [##SQLskillsStats10];
 
    IF EXISTS
    (
        SELECT *
        FROM [tempdb].[sys].[objects]
        WHERE [name] = N’##SQLskillsStats20′
    )
        DROP TABLE [##SQLskillsStats20];
 
 
    SELECT [database_id],
           [file_id],
           [num_of_reads],
           [io_stall_read_ms],
           [num_of_writes],
           [io_stall_write_ms],
           [io_stall],
           [num_of_bytes_read],
           [num_of_bytes_written],
           [file_handle]
    INTO ##SQLskillsStats10
    FROM sys.dm_io_virtual_file_stats(NULL, NULL);
 
    IF OBJECT_ID(‘tempdb..#session_Waits’) IS NOT NULL
    DROP TABLE #session_Waits
 
    IF OBJECT_ID(‘tempdb..#session_Waits2’) IS NOT NULL
    DROP TABLE #session_Waits2
     
    select * into #session_Waits from sys.dm_exec_session_wait_stats where session_id = @@SPID
 
 
    Declare @StorageStart datetime = getdate()
 
    declare @loop_Control int = 1
 
    while (@loop_Control <= 50000)
    begin
 
        insert into dbo.insert_test values (1)
 
        set @loop_Control = @loop_Control + 1
 
    end
 
    Declare @storage_end datetime = getdate()
 
    select * into #session_Waits2 from sys.dm_exec_session_wait_stats where session_id = @@SPID
 
    select sw2.waiting_tasks_count – sw1.waiting_tasks_count as waiting_tasks_count, sw2.wait_type ,sw2.wait_time_ms – sw1.wait_time_ms as wait_time_ms, sw2.max_wait_time_ms, sw2.signal_wait_time_ms – sw1.signal_wait_time_ms as signal_Wait_time_ms
    from #session_Waits sw1
    left join #session_Waits2 sw2 on sw1.session_id = sw2.session_id and sw1.wait_type = sw2.wait_type
    order by wait_time_ms desc
 
 
    select * from sys.dm_exec_session_wait_stats where session_id = @@SPID
 
    Declare @duration datetime = datediff(millisecond, @storagestart, @storage_end)
 
    select @duration as Duration
 
    –WAITFOR DELAY @WaitForTime
 
    SELECT [database_id],
           [file_id],
           [num_of_reads],
           [io_stall_read_ms],
           [num_of_writes],
           [io_stall_write_ms],
           [io_stall],
           [num_of_bytes_read],
           [num_of_bytes_written],
           [file_handle]
    INTO ##SQLskillsStats20
    FROM sys.dm_io_virtual_file_stats(NULL, NULL);
 
 
    WITH [DiffLatencies]
    AS (SELECT
            — Files that weren’t in the first snapshot
            [ts2].[database_id],
            [ts2].[file_id],
            [ts2].[num_of_reads],
            [ts2].[io_stall_read_ms],
            [ts2].[num_of_writes],
            [ts2].[io_stall_write_ms],
            [ts2].[io_stall],
            [ts2].[num_of_bytes_read],
            [ts2].[num_of_bytes_written]
        FROM [##SQLskillsStats20] AS [ts2]
            LEFT OUTER JOIN [##SQLskillsStats10] AS [ts1]
                ON [ts2].[file_handle] = [ts1].[file_handle]
        WHERE [ts1].[file_handle] IS NULL
        UNION
        SELECT
            — Diff of latencies in both snapshots
            [ts2].[database_id],
            [ts2].[file_id],
            [ts2].[num_of_reads] – [ts1].[num_of_reads] AS [num_of_reads],
            [ts2].[io_stall_read_ms] – [ts1].[io_stall_read_ms] AS [io_stall_read_ms],
            [ts2].[num_of_writes] – [ts1].[num_of_writes] AS [num_of_writes],
            [ts2].[io_stall_write_ms] – [ts1].[io_stall_write_ms] AS [io_stall_write_ms],
            [ts2].[io_stall] – [ts1].[io_stall] AS [io_stall],
            [ts2].[num_of_bytes_read] – [ts1].[num_of_bytes_read] AS [num_of_bytes_read],
            [ts2].[num_of_bytes_written] – [ts1].[num_of_bytes_written] AS [num_of_bytes_written]
        FROM [##SQLskillsStats20] AS [ts2]
            LEFT OUTER JOIN [##SQLskillsStats10] AS [ts1]
                ON [ts2].[file_handle] = [ts1].[file_handle]
        WHERE [ts1].[file_handle] IS NOT NULL)
 
    SELECT  DB_NAME([vfs].[database_id]) AS [Database],
           LEFT([mf].[physical_name], 2) AS [Drive],
           [mf].[type_desc],
           [num_of_reads] AS [Reads],
           [num_of_writes] AS [Writes],
           [ReadLatency(ms)] = CASE
                                   WHEN [num_of_reads] = 0 THEN
                                       0
                                   ELSE
           ([io_stall_read_ms] / [num_of_reads])
                               END,
           [WriteLatency(ms)] = CASE
                                    WHEN [num_of_writes] = 0 THEN
                                        0
                                    ELSE
           ([io_stall_write_ms] / [num_of_writes])
                                END,
           — [Latency] =
           — CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
           — THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
           [AvgBPerRead] = CASE
                               WHEN [num_of_reads] = 0 THEN
                                   0
                               ELSE
           ([num_of_bytes_read] / [num_of_reads])
                           END,
           [AvgBPerWrite] = CASE
                                WHEN [num_of_writes] = 0 THEN
                                    0
                                ELSE
           ([num_of_bytes_written] / [num_of_writes])
                            END,
           — [AvgBPerTransfer] =
           — CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
           — THEN 0 ELSE
           — (([num_of_bytes_read] + [num_of_bytes_written]) /
           — ([num_of_reads] + [num_of_writes])) END,
           [mf].[physical_name],
           (num_of_bytes_written / 1048576) AS num_of_mb_written,
           (num_of_bytes_read / 1048576) AS num_of_mb_read,
           (num_of_bytes_written / 1048576 / convert(int,(convert(int,@duration)/1000))) AS mb_per_sec_Written,
           (num_of_bytes_read / 1048576 / convert(int,(convert(int,@duration)/1000))) AS mb_per_sec_read
    FROM [DiffLatencies] AS [vfs]
        JOIN sys.master_files AS [mf]
            ON [vfs].[database_id] = [mf].[database_id]
               AND [vfs].[file_id] = [mf].[file_id]
    — ORDER BY [ReadLatency(ms)] DESC
    ORDER BY [WriteLatency(ms)] DESC;
 
 
    select * from sys.dm_exec_session_wait_stats where session_id = @@SPID

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

IF EXISTS

(

SELECT *

FROM [tempdb].[sys].[objects]

WHERE [name] = N‘##SQLskillsStats10’

)

DROP TABLE [##SQLskillsStats10];

IF EXISTS

(

SELECT *

FROM [tempdb].[sys].[objects]

WHERE [name] = N‘##SQLskillsStats20’

)

DROP TABLE [##SQLskillsStats20];

SELECT [database_id],

[file_id],

[num_of_reads],

[io_stall_read_ms],

[num_of_writes],

[io_stall_write_ms],

[io_stall],

[num_of_bytes_read],

[num_of_bytes_written],

[file_handle]

INTO ##SQLskillsStats10

FROM sys.dm_io_virtual_file_stats(NULL, NULL);

IF OBJECT_ID(‘tempdb..#session_Waits’) IS NOT NULL

DROP TABLE #session_Waits

IF OBJECT_ID(‘tempdb..#session_Waits2’) IS NOT NULL

DROP TABLE #session_Waits2

select * into #session_Waits from sys.dm_exec_session_wait_stats where session_id = @@SPID

Declare @StorageStart datetime = getdate()

declare @loop_Control int = 1

while (@loop_Control <= 50000)

begin

insert into dbo.insert_test values (1)

set @loop_Control = @loop_Control + 1

end

Declare @storage_end datetime = getdate()

select * into #session_Waits2 from sys.dm_exec_session_wait_stats where session_id = @@SPID

select sw2.waiting_tasks_count – sw1.waiting_tasks_count as waiting_tasks_count, sw2.wait_type ,sw2.wait_time_ms – sw1.wait_time_ms as wait_time_ms, sw2.max_wait_time_ms, sw2.signal_wait_time_ms – sw1.signal_wait_time_ms as signal_Wait_time_ms

from #session_Waits sw1

left join #session_Waits2 sw2 on sw1.session_id = sw2.session_id and sw1.wait_type = sw2.wait_type

order by wait_time_ms desc

select * from sys.dm_exec_session_wait_stats where session_id = @@SPID

Declare @duration datetime = datediff(millisecond, @storagestart, @storage_end)

select @duration as Duration

–WAITFOR DELAY @WaitForTime

SELECT [database_id],

[file_id],

[num_of_reads],

[io_stall_read_ms],

[num_of_writes],

[io_stall_write_ms],

[io_stall],

[num_of_bytes_read],

[num_of_bytes_written],

[file_handle]

INTO ##SQLskillsStats20

FROM sys.dm_io_virtual_file_stats(NULL, NULL);

WITH [DiffLatencies]

AS (SELECT

— Files that weren’t in the first snapshot

[ts2].[database_id],

[ts2].[file_id],

[ts2].[num_of_reads],

[ts2].[io_stall_read_ms],

[ts2].[num_of_writes],

[ts2].[io_stall_write_ms],

[ts2].[io_stall],

[ts2].[num_of_bytes_read],

[ts2].[num_of_bytes_written]

FROM [##SQLskillsStats20] AS [ts2]

LEFT OUTER JOIN [##SQLskillsStats10] AS [ts1]

ON [ts2].[file_handle] = [ts1].[file_handle]

WHERE [ts1].[file_handle] IS NULL

UNION

SELECT

— Diff of latencies in both snapshots

[ts2].[database_id],

[ts2].[file_id],

[ts2].[num_of_reads] – [ts1].[num_of_reads] AS [num_of_reads],

[ts2].[io_stall_read_ms] – [ts1].[io_stall_read_ms] AS [io_stall_read_ms],

[ts2].[num_of_writes] – [ts1].[num_of_writes] AS [num_of_writes],

[ts2].[io_stall_write_ms] – [ts1].[io_stall_write_ms] AS [io_stall_write_ms],

[ts2].[io_stall] – [ts1].[io_stall] AS [io_stall],

[ts2].[num_of_bytes_read] – [ts1].[num_of_bytes_read] AS [num_of_bytes_read],

[ts2].[num_of_bytes_written] – [ts1].[num_of_bytes_written] AS [num_of_bytes_written]

FROM [##SQLskillsStats20] AS [ts2]

LEFT OUTER JOIN [##SQLskillsStats10] AS [ts1]

ON [ts2].[file_handle] = [ts1].[file_handle]

WHERE [ts1].[file_handle] IS NOT NULL)

SELECT DB_NAME([vfs].[database_id]) AS [Database],

LEFT([mf].[physical_name], 2) AS [Drive],

[mf].[type_desc],

[num_of_reads] AS [Reads],

[num_of_writes] AS [Writes],

[ReadLatency(ms)] = CASE

WHEN [num_of_reads] = 0 THEN

ELSE

([io_stall_read_ms] / [num_of_reads])

END,

[WriteLatency(ms)] = CASE

WHEN [num_of_writes] = 0 THEN

ELSE

([io_stall_write_ms] / [num_of_writes])

END,

— [Latency] =

— CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

— THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,

[AvgBPerRead] = CASE

WHEN [num_of_reads] = 0 THEN

ELSE

([num_of_bytes_read] / [num_of_reads])

END,

[AvgBPerWrite] = CASE

WHEN [num_of_writes] = 0 THEN

ELSE

([num_of_bytes_written] / [num_of_writes])

END,

— [AvgBPerTransfer] =

— CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

— THEN 0 ELSE

— (([num_of_bytes_read] + [num_of_bytes_written]) /

— ([num_of_reads] + [num_of_writes])) END,

[mf].[physical_name],

(num_of_bytes_written / 1048576) AS num_of_mb_written,

(num_of_bytes_read / 1048576) AS num_of_mb_read,

(num_of_bytes_written / 1048576 / convert(int,(convert(int,@duration)/1000))) AS mb_per_sec_Written,

(num_of_bytes_read / 1048576 / convert(int,(convert(int,@duration)/1000))) AS mb_per_sec_read

FROM [DiffLatencies] AS [vfs]

JOIN sys.master_files AS [mf]

ON [vfs].[database_id] = [mf].[database_id]

AND [vfs].[file_id] = [mf].[file_id]

— ORDER BY [ReadLatency(ms)] DESC

ORDER BY [WriteLatency(ms)] DESC;

select * from sys.dm_exec_session_wait_stats where session_id = @@SPID

Premium V2 insert

Log Drive

Premium SSD v2 LRS
14740IOPs
313MBps Throughput

Test

36 Seconds
0.6 ms write latency on the log drive via perfmon
< 1ms write latency via DMV’s
34048ms of WRITELOG waits (35 seconds of writelog….)

Ultra Disk Insert

Log Drive

ULTRA SSD
14740IOPs
1024MBps Throughput

Test

45 Seconds
0.9 ms write latency on the log drive via perfmon
< 1ms write latency via DMV’s
42762ms of WRITELOG waits (42 seconds of writelog….)

Premium SSD

Log Drive

Premium SSD LRS
500 IOPs
100MBps Throughput

Test

1 minute 44
2ms write latency on the log drive via perfmon
1ms write latency via DMV’s
102157ms of WRITELOG waits (1.7 minutes of writelog….)

Standard HDD

Log Drive

Standard HDD LRS
500 IOPs
60MBps Throughput

Test

3 minutes 32
4ms write latency on the log drive via perfmon
4ms write latency via DMV’s
207050ms of WRITELOG waits (4.45 minutes of writelog….)

Conclusion

It’s no surprise that disk types affect query performance, its just good to see why and by how much. Oh also, latency is pretty un-predictable, these were best cases seen over a few runs, but I have witnessed higher latency’s…

Conversion failed when converting sanity from job..

Test

Summary

Premium V2 insert

Log Drive

Test

Ultra Disk Insert

Log Drive

Test

Premium SSD

Log Drive

Test

Standard HDD

Log Drive

Test

Conclusion

Leave a Reply Cancel reply

Comments