8

This was a question I was battling with earlier today and eventually figured out an answer. Wouldn't mind something better, but wanted to provide this to whomever also needed.

First, on an Azure VM you get a D:\ drive for free which is SSD. The caveat is that it usually gets destroyed when a VM is restarted. MS best practice for high write volume is to use this drive for tempdb. What they don't go into is that it's not formatted 64kb. The hidden pagefile.sys resides here, so you need to keep that into account if you try to reformat (it'll fail).

From this: https://cloudblogs.microsoft.com/sqlserver/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions/

You'll want to alter their startup powershell script to something more like below, where I'm first removing the pagefile entirely, then formatting to 64k, creating the file as the script usually would, then putting the pagefile.sys back before starting the startup services.

$SQLService=”SQL Server (MSSQLSERVER)”
$SQLAgentService=”SQL Server Agent (MSSQLSERVER)”
$tempfolder=”D:\SQLTEMP”
if (!(test-path -path $tempfolder)) {
    (Get-WmiObject -Class Win32_PageFileSetting).Delete() 
    Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "Temporary Storage"  -Confirm:$false
    New-Item -ItemType directory -Path $tempfolder
    Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{ Name = 'D:\pagefile.sys';} 
}
Start-Service $SQLService
Start-Service $SQLAgentService

Does anyone have anything better or see any holes in this process?

For a little more automation/help for others, I also script the above script out as well as the triggers to create it and run it 30s after startup using below. This basically allows someone to automatically do the steps in the cloudblogs article I reference.

#1 - Set Services to manual startup so that windows scheduler will start after tempdb adjusted
Set-Service -Name MSSQLSERVER -StartupType Manual
Set-Service -Name SQLSERVERAGENT -StartupType Manual
IF (Get-Service MsDtsServer130 -ErrorAction SilentlyContinue)
{
Set-Service -Name MsDtsServer130 -StartupType Manual
}

<#2 - using below powershell script to create the startup script#>
$script=
'
$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMP"
if (!(test-path -path $tempfolder)) {
    (Get-WmiObject -Class Win32_PageFileSetting).Delete() 
    Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "Temporary Storage"  -Confirm:$false
    New-Item -ItemType directory -Path $tempfolder
    Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{ Name = "D:\pagefile.sys";} 
}
Start-Service $SQLService
Start-Service $SQLAgentService
IF (Get-Service MsDtsServer130 -ErrorAction SilentlyContinue)
{
Start-Service -Name MsDtsServer130
}
'

Out-File -FilePath C:\SQL-startup.ps1 -InputObject $script -NoClobber


<#3 - using below script to create a scheduled task to call a powershell script that creates a folder on the d drive.#>
$trigger = New-JobTrigger -AtStartup -RandomDelay 00:00:30
Register-ScheduledJob -Trigger $trigger -FilePath C:\SQL-startup.ps1 -Name CreateSqlFolderOnDSsd

**Edit, above assumes SQL 2016 and at least powershell 5 (though I'm unaware of specific dependencies, should work on v4 at least).

1
  • Microsoft responded that formatting wouldn't be necessary in this scenario and said they'd update the documentation, so I guess omit the pagefile drop/create and drive formatting if someone's looking to reuse. Mar 19, 2020 at 13:09

1 Answer 1

6

Does anyone have anything better or see any holes in this process?

As for holes, the blog post contains several, but your script appears to plug them.

As for something better, TempDB on the local SSD scratch drive is now available out-of-the-box for the SQL Server on Azure VM, along with separate storage pools for data and log. Here's a screenshot of the storage configuration pane from the Azure Portal:

enter image description here

The VMs drives will be formatted as follows:

PS C:\> Get-Volume  | Format-List AllocationUnitSize, FileSystemLabel


AllocationUnitSize : 4096
FileSystemLabel    : Temporary Storage

AllocationUnitSize : 65536
FileSystemLabel    : SQLVMLOG

AllocationUnitSize : 65536
FileSystemLabel    : SQLVMDATA1

AllocationUnitSize : 0
FileSystemLabel    :

AllocationUnitSize : 0
FileSystemLabel    :

AllocationUnitSize : 4096
FileSystemLabel    : System Reserved

AllocationUnitSize : 4096
FileSystemLabel    : Windows

The recommendation here to use a 64KB allocation unit size for TempDb may only be intended for when TempDb is on remote storage, not on the local NVMe flash disk, and in any case shouldn't have a large performance impact.

1
  • 1
    Yeah, I've gone through the setup instructions but unfortunately don't have access to my local msdn at work to test and don't have access to our subscription to test spinning a VM up. When you configure tempdb to use scratch storage, does it set the ntfs size to 64k? I'm trying to balance optimizing performance with cost and need to account for things like offloading state cache to Redis which seems like a fairly expensive service. Feb 19, 2020 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.