If needed, modify the D:\ folder to the one you specified in Step 1. Using Configuration Manager set the Start Mode of SQL Server and SQL Agent as Manual.Ĥ) Create a Powershell script to recreate the folder in D:\ if needed and start SQL serverĬopy and paste the following script and save it as a Powershell file in the C:\ drive (OS drive), for example as “C:\ SQL-startup.ps1”.
( FILENAME = ‘D:\SQLTEMP\ExtensionFile.BPE’, SIZE = )ģ) Configure the Start Mode of SQL Server and SQL Agent startup as Manual The general recommendation is to set the size to 4-6 times the size of the VM memory. Execute the following T-SQL commands to configure the Buffer Pool Extension, specifying the location and size of its file. Using SSMS connect to your SQL Server instance. Execute the following T-SQL commands to change location of the TempDB files:ĪLTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= ‘D:\SQLTEMP\tempdb.mdf’)ĪLTER DATABASE tempdb MODIFY FILE (name = templog, filename = ‘D:\SQLTEMP\templog.ldf’)Ģ) To configure Buffer Pool Extensions in the SSD This is the folder that you will store TempDB and/or Buffer Pool Extensions in, for example as “D:\SQLTEMP”.
The following section describes how to store SQL Server TempDB and/or Buffer Pool Extensions on the SSD drive and automatically recreate directory if the VM moves to a different host.
Use (persistent) drives from Azure Storage. Do not store your data or log files there.This can happen in case of a host failure or a VM resize operation. The SSD drive (D:\) is not persistent, so its contents and permissions will be lost if the VM moves to a different host. Now you can do the same in Azure VMs using the new D-Series VM Sizes. The latter improves the performance of read workloads which working set doesn’t fit in memory. queries handling large recordsets, index rebuilds, row versioning isolation levels, temp tables, and triggers).
The former improves the performance of workloads that use temporary objects heavily (e.g. A common practice on-premise to improve the performance of SQL Server workloads is storing TempDB and/or Buffer Pool Extensions (SQL Server 2014) on SSDs.