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"