Log initialization on Azure Blob

One fellow MCM posted the other day the following question about log initialization on Azure Blob storage:

********************

In SQL 2014 (VM hosted in Windows Azure) I've tested creating a DB  on the Azure Blob storage with a statement like this

CREATE DATABASE TesBlob1GB

ON

   (NAME = file_data1, FILENAME = 'https://whatever.blob.core.windows.net/sqldata/filedata9.mdf', SIZE = 10GB, FILEGROWTH = 256 MB )

LOG ON

(NAME = file_log1, FILENAME = 'https://whatever.blob.core.windows.net/sqldata/filelog9.ldf', SIZE = 100GB, FILEGROWTH = 32 MB)

and regardless of the log file size (I tested with 1 GB, 10 GB, and 100 GB) the statement completes in 2 seconds.

When I use a VM disk instead of  the Blob storage it takes about 80 sec for 1 GB log file (Instant initialization is enabled for the SQL service account)

The Azure Blob uses the sparse storage (http://blogs.msdn.com/b/windowsazurestorage/archive/2012/06/28/exploring-windows-azure-drives-disks-and-images.aspx) but for actual Writes it should allocate actual storage, shouldn't it?

Does the log file get initialized when placed into the Azure Blob storage?

********************

I looked into it and this is what I found and responded:

Indeed, SQL14 is able to store Data and TLog files in Azure Blob Storage (xstore page files) and the storage engine treats that storage type differently. It doesn’t zero initialize these files because they are like NTFS sparse files: all zeroes unless written.

So, the only part that is actually being zeroed out is the part of the log tail fixed up during recovery (for boxed product it’s 3840KB in Hekaton. See the range highlighted in red below.)

This instant page file initialization is a nice side benefit you get when you store your databases on this type of storage. On NTFS you need to have “perform volume maintenance tasks” privilege, here it’s for free.

The reason why you don’t see the information about that zeroing operation in errorlog when you enable trace flag 3604 suggested is because the function which implements zeroing a file stored in xstore (XFCX::ZeroFile) doesn’t honor such trace flag like the one which zeroes files stored on regular disks (FCB::ZeroFile) does.

 

0:048> r

rax=0000000000000000 rbx=ffffffffffffffff rcx=00000000097920e0

rdx=00000000021385a0 rsi=00000000046a6548 rdi=0000000000000000

rip=00007ffb9388001b rsp=0000000002138060 rbp=0000000002138160

r8=00000000000000ba  r9=0000000000000000 r10=0000000000000000

r11=00007ffb9f0e1735 r12=00000000046a6664 r13=0000000000000000

r14=00000000097920e0 r15=00000001393aa040

iopl=0         nv up ei pl zr na po nc

cs=0033  ss=002b  ds=002b  es=002b  fs=0053  gs=002b             efl=00000246

sqlmin!XFCB::AsyncHttpIOInternal+0x2cb:

00007ffb`9388001b ff15bfa07500    call    qword ptr [sqlmin!_imp_WinHttpSendRequest (00007ffb`93fda0e0)] ds:00007ffb`93fda0e0={WINHTTP!WinHttpSendRequest (00007ffb`98730250)}

0:048> kL

Child-SP          RetAddr           Call Site

00000000`02138060 00007ffb`938801e2 sqlmin!XFCB::AsyncHttpIOInternal+0x2cb

00000000`02138e00 00007ffb`93881b5a sqlmin!XFCB::AsyncIOInternal+0x62

00000000`02138e40 00007ffb`938fc15e sqlmin!XFCB::ZeroFile+0x1ca

00000000`02139f40 00007ffb`938fa2a2 sqlmin!SQLServerLogMgr::FixupLogTail+0x32e

.

.

.

00000000`0214b5f0 00007ffb`936c41bd sqlmin!DBTABLE::Startup+0x1167

00000000`0214c360 00007ffb`7093143a sqlmin!DBMgr::StartupDB+0x70d

.

.

.

00000000`0214d3f0 00007ffb`6fd1a058 sqllang!CStmtCreateDB::XretExecute+0xdc1

.

.

.

0:048> du @rdx l @r8

00000000`021385a0  "x-ms-version: 2012-02-12..x-ms-l"

00000000`021385e0  "ease-id: {F09D10E5-75AF-4363-90B"

00000000`02138620  "A-8BDB690DE887}..x-ms-range: byt"

00000000`02138660  "es=24576-3956735..Content-Length"

00000000`021386a0  ": 0..x-ms-page-write: clear..x-m"

00000000`021386e0  "s-if-sequence-number-eq: 0"