This tutorial shows how to create a Compute Engine VM instance running SQL Server that is optimized for performance. This tutorial guides you through creating the instance and then configuring SQL Server for optimal performance on Google Cloud. You will learn about a number of configuration options that are available to help you adjust the performance of the system.
This tutorial uses SQL Server Standard Edition 2022, so not every configuration option presented in this guide works for everyone, and not all of them provide noticeable performance benefits for every workload.
Objectives
- Setting up the Compute Engine instance and disks.
- Configuring the Windows operating system.
- Configuring SQL Server.
Costs
This tutorial uses billable components of Google Cloud, including:
- Compute Engine high-memory instance
- Compute Engine SSD Persistent Disk storage
- Compute Engine Local SSD disk storage
- SQL Server Standard preconfigured image
The Pricing Calculator can generate a cost
estimate based on your projected usage. The provided link shows the cost
estimate for the products used in this tutorial, which can cost over 4 dollars
(US) per hour and over 3,000 dollars per month.
Before you begin
- Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
Creating the Compute Engine VM with disks
To create a high-performance SQL Server instance, you must first create a VM instance with SQL Server and two persistent disks.
Persistent disks considerations
To select the type of persistent disks for your VM, review the following considerations:
A local SSD provides a high-performance location for
tempdb
and the Windows pagefile.There are some important considerations to note when using a local SSD. When you shut down your instance from Windows or reset it by using the API, the local SSD is removed. This action renders the instance unbootable. To get the machine running again, you would need to detach your persistent disks, create a new instance with them, and then define a new local SSD. After startup you would also need to format the new disk and reboot. Therefore, you should not permanently store critical data on a local SSD, or power off the instance, unless you are prepared to rebuild it.
An SSD Persistent Disk provides high-performance storage for the database files.
Persistent Disk performance is based on a calculation that uses the number of CPUs and the size of the disk. With 32 vCPUs and a 1 TB disk, the performance peaks at 40,000 read operations per second (ops) and 30,000 write ops. The total sustained throughput for reads and writes is 800 MB per second and 400 MB per second respectively. These measurements represent a summation of all the persistent disks attached to the virtual machine, including the
C:\
drive. This is why you should create a local SSD to offload all the IOPS needed for the paging file,tempdb
, staging data, and backups.
To read more about disk performance, see Configure disks to meet performance requirements.
Creating Compute Engine VM with disks
To create a VM that has SQL Server 2022 Standard preinstalled on Windows Server 2022, follow these steps:
In the Google Cloud console, go to the Create an instance page.
For Name, enter
ms-sql-server
.In the Machine configuration section, select General Purpose, and then do the following:
- In the Series list, click N2.
- In the Machine type list, click n2-highmem-16 (16vCPU, 128 GB memory).
In the Boot disk section, click Change, and then do the following:
- On the Public images tab, click the Operating system list, and then select SQL Server on Windows Server.
- In the Version list, click SQL Server 2022 Standard on Windows Server 2022 Datacenter.
- In the Boot disk type list, click Standard persistent disk.
- In the Size (GB) field, set the boot disk size to 50 GB.
- To save the boot disk configuration, click Select.
Expand the Advanced options section, and do the following:
- Expand the Disks section.
To create local disks, click Add Local SSD, and then do the following:
- In the Interface list, select the protocol that meets your system's performance requirements.
- In the Disk capacity list, select a disk capacity that supports the anticipated size of
tempdb
files. - To finish creating this disk, click Save.
To create additional disks, click Add New Disk.
- Keep the Name field unchanged.
- In the Disk source type list, select Blank disk.
- In the Disk type list, select SSD persistent disk.
- In the Size field, enter the disk size that can accommodate the database size.
- To finish creating the second disk, click Save.
To create the VM, click Create.
Configuring Windows
Now that you have a working instance running SQL Server, connect to your instance and configure the Windows operating system. After that, you learn to configure SQL Server in an upcoming section.
Connect to your instance
In the Google Cloud console, go to the VM instances page.
Under the Name column, click the name of your instance,
ms-sql-server
.At the top of the instance's details page, click the Set Windows Password button.
Specify a username.
Click Set to generate a new password for this Windows instance.
Note the username and password so you can log into the instance.
Connect to your instance by using RDP.
Setting up disk volumes
Create and format the volumes:
- From the Start menu, search for "Computer Management" and then open it.
- Under Storage section, select Disk Management.
- When prompted to initialize disks, accept the default selections and click OK.
Create partition for local SSD disk(s):
To locate a local SSD disk, right-click on a disk and select Properties. The local SSD disk properties name will be
Google EphemeralDisk
for a SCSI interface ornvme_card
for an NVMe interface. Both local SSDs and persistent SSDs are marked as havingUnallocated
partitions.If the VM contains only 1 local SSD drive, follow these steps:
- Under the list of disk drives, right-click the 374.98 GB local SSD disk and select New Simple Volume.
- On the Welcome screen, click Next to start the disk volume wizard.
- In the Specify Volume Size step, leave the volume size at the default value and click Next to proceed.
- In the Assign Drive Letter or Path step, choose P: for the drive letter and click Next to proceed.
In the Format Volume step, change the Allocation unit size to 8192 and enter "pagefile" for the Volume label. Click Next to proceed.
Click Finish to complete the disk volume wizard.
If the VM contains multiple local SSD drives, follow these steps:
- Under the list of disk drives, right-click the first 374.98 GB local SSD disk and select New Striped Volume.
- On the Welcome screen, click Next to start the disk volume wizard.
In the Select Disks step, add all the available disks with the size 383,982 MB to the Selected section. Click Next to proceed.
In the Assign Drive Letter or Path step, choose P: for the drive letter and click Next to proceed.
In the Format Volume step, change the Allocation unit size to 8192 and enter "pagefile" for the Volume label. Click Next to proceed.
Click Finish to complete the disk volume wizard.
Repeat the previous steps to create a New Simple Volume for the SSD disk, with the following three changes:
Choose D: for the drive letter.
Set the Allocation unit size to
64k
.For details on selecting an allocation unit size, see Best practices for SQL Server instances.
Enter
sqldata
for the Volume label.
Moving the Windows paging file
Now that the new volumes are created and mounted, move the Windows paging file onto the local SSD, which frees up Persistent Disk IOPS and improves the access time of your virtual memory.
- From the Start menu, search for View advanced system settings and then open the dialog.
- Click the Advanced tab, and in the Performance section, click Settings.
- In the Virtual memory section, click the Change button.
- Clear the checkbox Automatically manage paging file size for all drives. The
system should have already set up your paging file on the
C:\
drive, and you need to move it. - Click C: and then click the No paging file radio button.
- Click the Set button.
- To create the new paging file, click the P: drive, and then click the System managed size radio button.
- Click the Set button.
Click OK three times to exit the advanced system properties.
Microsoft Support has published additional tips for virtual memory settings.
Setting the power profile
Set the power profile to High-Performance
instead of Balanced
.
- From the Start menu, search for "Choose a Power Plan", and then open the power options.
- Select the High Performance radio button.
- Exit the dialog.
Configuring SQL Server
Use SQL Server Management Studio to perform most administrative tasks. The preconfigured images for SQL Server come with Management Studio already installed. Launch Management Studio and then click Connect to connect to the default database.
Moving the data and log files
The preconfigured image for SQL Server comes with everything installed on
the C:\
drive, including the system databases. To optimize your setup,
move those files to the new D:\
drive you created. Also remember to create
all new databases on the D:\
drive. Because you are using an SSD, you do not need to store the data files and log files on separate disk
partitions.
There are two ways to move the installation to the secondary disk: using the installer or moving the files manually.
Using the installer
To use the installer, run c:\setup.exe
and select a new installation path on
your secondary disk.
Moving the files manually
Move the system databases and configure SQL Server to save the data and log files on the same volume:
- Create a new folder named
D:\SQLData
. - Open a Command Window.
Enter the following command to grant full access to
NT Service\MSSQLSERVER
:icacls D:\SQLData /Grant "NT Service\MSSQLServer:(OI)(CI)F"
Use Management Studio and the following guides to move your system databases and change the default file locations for new databases.
If you plan on using Report Server features, move the ReportServer and ReportServerTempDB files as well.
After you move the primary configuration database files and restart, you need to configure the system to point to the new location for the model and MSDB databases. Here is a helper script to run in Management Studio:
ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\SQLData\model.mdf' ) ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\SQLData\modellog.ldf' ) ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\SQLData\MSDBData.mdf' ) ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:\SQLData\MSDBLog.ldf' )
After you execute these commands:
- Use the
services.msc
snap-in to stop the SQL Server database service. - Use the Windows file explorer to move the physical files from the
C:\
drive where themaster
database was located to theD:\SQLData
directory. - Start the SQL Server database service.
Setting system permissions
After moving the system databases, modify some additional settings, starting
with permissions for the Windows user account created to run your SQL
Server process, which is named NT Service\MSSQLSERVER
.
Granting the Lock Pages in Memory
permission
The group policy Lock Pages in Memory
permission prevents Windows from
moving pages in physical memory to virtual memory. To keep physical
memory free and organized, Windows tries to swap old, rarely modified pages to
the virtual-memory paging file on disk.
SQL Server stores important information in memory, such as table structures,
execution plans, and cached queries. Some of this information rarely changes, so
it becomes a target for the paging file. If this information gets moved to the
paging file, SQL Server performance can degrade. Granting the group policy Lock
Pages in Memory
permission for SQL Server's service account prevents this
swapping.
Follow these steps:
- Click Start and then search for Edit Group Policy to open the console.
- Expand Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment.
- Search for and then double-click Lock pages in memory.
- Click Add User or Group.
- Search for "NT Service\MSSQLSERVER".
- If you see multiple names, double-click the MSSQLSERVER name.
- Click OK twice.
- Keep the Group Policy Editor console open.
Granting the Perform volume maintenance tasks
permission
By default, when an application requests a slice of disk space from Windows, the operating system locates an appropriately sized chunk of disk space, and then zeroes out the entire chunk of disk, before handing it back to the application. Because SQL Server is good at growing files and filling disk space, this behavior is not optimal.
There is a separate API for allocating disk space to an application, often
referred to as instant file initialization. Unfortunately, this setting only
works for data files, but you will learn in an upcoming section about log-file
growth. Instant file initialization requires the service account running the SQL
Server process to have another group policy permission, called Perform volume
maintenance tasks
.
- In the Group Policy Editor, search for "Perform volume maintenance tasks".
- Add the "NT Service\MSSQLSERVER" account as you did in the previous section.
- Restart the SQL Server process to activate both settings.
Setting up tempdb
It used to be a best practice to optimize the SQL Server
CPU usage by creating one tempdb
file per CPU. However, because CPU counts
have grown over time, following this guideline can cause performance to
decrease. As a good starting point, use 4 tempdb
files. As you measure your
system's performance, in rare cases you might need to incrementally increase the
number of tempdb
files to a maximum of 8.
You can run a Transact-SQL (T-SQL) script inside SQL Server Management Studio to
move the tempdb
files to a folder in the `p:` drive.
- Create the directory
p:\tempdb
. Grant full security access to the "NT Service\MSSQLSERVER" user account:
icacls p:\tempdb /Grant "NT Service\MSSQLServer:(OI)(CI)F"
Run the following script inside SQL Server Management Studio, to move the
tempdb
data file and log file:USE master GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf') GO ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf') GO
Restart SQL Server.
Run the following script to modify the file sizes and create three additional data files for the new
tempdb
.ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf', SIZE=8GB) ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf' , SIZE = 2GB) ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:\tempdb\tempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0); ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:\tempdb\tempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0); ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:\tempdb\tempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0); GO
If you use SQL Server 2016, there are 3 additional
tempdb
files to remove after you do the previous steps:ALTER DATABASE [tempdb] REMOVE FILE temp2; ALTER DATABASE [tempdb] REMOVE FILE temp3; ALTER DATABASE [tempdb] REMOVE FILE temp4;
Restart SQL Server again.
Delete the
model
,MSDB
,master
, andtempdb
files from the original location on theC:\
drive.
You successfully moved your tempdb
files onto the local SSD partition. This
move carries some risks, mentioned earlier, but if they are lost for any
reason,SQL Server rebuilds the tempdb
files. Moving tempdb
gives you the
added performance of the local SSD, and decreases the IOPS used on your
persistent disks.
Setting max degree of parallelism
The recommended default setting for max degree of parallelism
is to match it
to the number of CPUs on the server. However, there is a point where executing a
query in 16 or 32 parallel chunks and merging the results is much slower than
running it in a single process. If you are using a 16- or 32-core instance, you
can set the max degree of parallelism
value to 8 using the following T-SQL:
USE master GO EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'max degree of parallelism', 8 GO RECONFIGURE WITH OVERRIDE GO
Setting max server memory
This setting defaults to a very high number, but you should set it to the number
of megabytes of available physical RAM, minus a couple gigabytes for operating
system and overhead. The following T-SQL example adjusts max server memory
to
100 GB. Modify it to adjust the value to match your instance. Review the
Server memory server configuration options document
for more information.
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OVERRIDE GO exec sp_configure 'max server memory', 100000 GO RECONFIGURE WITH OVERRIDE GO
Finishing up
Restart the instance one more time to make sure all of the new settings take effect. Your SQL Server system is configured and you are ready to create your own databases and start testing your specific workloads. Review the SQL Server Best Practices guide for more information on operational activities, other performance considerations, and Enterprise Edition capabilities.
Clean up
After you finish the tutorial, you can clean up the resources that you created so that they stop using quota and incurring charges. The following sections describe how to delete or turn off these resources.
Deleting the project
The easiest way to eliminate billing is to delete the project that you created for the tutorial.
To delete the project:
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
Deleting instances
To delete a Compute Engine instance:
- In the Google Cloud console, go to the VM instances page.
- Select the checkbox for the instance that you want to delete.
- To delete the instance, click More actions, click Delete, and then follow the instructions.
Deleting persistent disks
To delete the Persistent Disk:
In the Google Cloud console, go to the Disks page.
Select the checkbox next to the name of the disk you want to delete.
Click the Delete button at the top of the page.
What's next
- Load test your SQL Server instance.
- Review the SQL Server Best Practices guide.
- Explore reference architectures, diagrams, and best practices about Google Cloud. Take a look at our Cloud Architecture Center.