Creating a high-performance SQL Server instance

This tutorial shows how to create a Compute Engine VM instance runningSQL Server that is optimized for performance. This tutorial guides you throughcreating the instance and then configuring SQL Server for optimal performance onGoogle Cloud. You will learn about a number of configuration options thatare available to help you adjust the performance of the system.

This tutorial uses SQL Server Standard Edition 2022, so not every configurationoption presented in this guide works for everyone, and not all of them providenoticeable 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

ThePricing Calculator can generate a costestimate based on your projected usage. The provided link shows the costestimate for the products used in this tutorial, which can cost over 4 dollars(US) per hour and over 3,000 dollars per month.

New Google Cloud users might beeligible for afree trial.

Before you begin

  1. 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.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

    Go to project selector

  3. 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 VMinstance with SQL Server and two Persistent Disk volumes.

Persistent Disk considerations

To select the type of Persistent Disk volumes for your VM, review thefollowing considerations:

  • ALocal SSD disk provides a high-performance location fortempdb and the Windows pagefile.

    There are some important considerations to note when using a Local SSD disk.When you shut down your instance from Windows or reset it by using the API,the Local SSD disk is removed. This action renders the instance unbootable.To get the machine running again, you would need to detach your persistentdisks, create a new instance with them, and then define a new Local SSD disk.After startup you would also need to format the new disk and reboot.Therefore, you shouldn't permanently store critical data on a Local SSD disk,or power off the instance, unless you are prepared to rebuild it.

  • AnSSD Persistent Disk provides high-performance storage for the database files.

    Persistent Disk performance is based on a calculation that uses the number ofCPUs and the size of the disk. With 32 vCPUs and a 1 TB disk, theperformance peaks at 40,000 read operations per second (ops) and 30,000write ops. The total sustained throughput for reads and writes is 800 MB persecond and 400 MB per second respectively. These measurements represent asummation of all the Persistent Disk volumes attached to the virtual machine,including theC:\ drive. To ensure consistent performance, create aLocal SSD disk and offload all the IOPS needed for the paging file,tempdb, staging data, and backups.

To read more about disk performance, seeConfigure disks to meet performance requirements.

Creating Compute Engine VM with disks

To create a VM that has SQL Server 2022 Standard preinstalled on Windows Server2022, follow these steps:

  1. In the Google Cloud console, go to theCreate an instance page.

    Go to Create an instance

  2. ForName, enterms-sql-server.

  3. In theMachine configuration section, selectGeneral Purpose, andthen do the following:

    1. In theSeries list, clickN2.
    2. In theMachine type list, clickn2-highmem-16(16vCPU, 128 GB memory).

  4. In theBoot disk section, clickChange, and then do the following:

    1. On thePublic images tab, click theOperating system list, andthen selectSQL Server on Windows Server.
    2. In theVersion list, clickSQL Server 2022 Standard on WindowsServer 2022 Datacenter.
    3. In theBoot disk type list, clickStandard persistent disk.
    4. In theSize (GB) field, set the boot disk size to50 GB.
    5. To save the boot disk configuration, clickSelect.

  5. Expand theAdvanced options section, and do the following:

    1. Expand theDisks section.

    2. To create local disks, clickAdd Local SSD, and then do thefollowing:

      1. In theInterface list, select the protocol that meets yoursystem's performance requirements.
      2. In theDisk capacity list, select a disk capacity that supportsthe anticipated size oftempdb files.
      3. To finish creating this disk, clickSave.

    3. To create additional disks, clickAdd New Disk.

      1. Keep theName field unchanged.
      2. In theDisk source type list, selectBlank disk.
      3. In theDisk type list, selectSSD persistent disk.
      4. In theSize field, enter the disk size that can accommodate thedatabase size.
      5. To finish creating the second disk, clickSave.

  6. To create the VM, clickCreate.

Configuring Windows

Now that you have a working instance running SQL Server, connect to yourinstance and configure the Windows operating system. After that, you learn toconfigure SQL Server in an upcoming section.

Connect to your instance

  1. In the Google Cloud console, go to theVM instances page.

    Go to VM instances

  2. Under theName column, click the name of your instance,ms-sql-server.

  3. At the top of the instance's details page, click theSet Windows Password button.

  4. Specify a username.

  5. ClickSet to generate a new password for this Windows instance.

  6. Note the username and password so you can log into the instance.

  7. Connect to your instanceby using RDP.

Setting up disk volumes

Create and format the volumes:

  1. From theStart menu, search for "Computer Management" and then open it.
  2. UnderStorage section, selectDisk Management.
  3. When prompted to initialize disks, accept the default selections and clickOK.

  4. Create partition for Local SSD disk(s):

    To locate a Local SSD disk, right-click on a disk and selectProperties.The Local SSD disk properties name will beGoogle EphemeralDisk for a SCSIinterface ornvme_card for an NVMe interface. Both Local SSD disks andpersistent SSDs are marked as havingUnallocated partitions.

    1. If the VM contains only 1 Local SSD drive, follow these steps:

      1. Under the list of disk drives, right-click the 374.98 GB local SSDdisk and selectNew Simple Volume.
      2. On the Welcome screen, clickNext to start the disk volumewizard.
      3. In theSpecify Volume Size step, leave the volume size at thedefault value and clickNext to proceed.
      4. In theAssign Drive Letter or Path step, chooseP: for thedrive letter and clickNext to proceed.

      5. In theFormat Volume step, change theAllocation unit sizeto 8192 and enter "pagefile" for theVolume label. ClickNext to proceed.

        New Volume Wizard

      6. ClickFinish to complete the disk volume wizard.

    2. If the VM contains multiple local SSD drives, follow these steps:

      1. Under the list of disk drives, right-click the first 374.98 GB localSSD disk and selectNew Striped Volume.
      2. On the Welcome screen, clickNext to start the disk volumewizard.

      3. In theSelect Disks step, add all the available disks with thesize 383,982 MB to the Selected section. ClickNext to proceed.

        Add striped disks

      4. In theAssign Drive Letter or Path step, chooseP: for thedrive letter and clickNext to proceed.

      5. In theFormat Volume step, change theAllocation unit sizeto 8192 and enter "pagefile" for theVolume label. ClickNext to proceed.

        New Volume Wizard

      6. ClickFinish to complete the disk volume wizard.

  5. Repeat the previous steps to create aNew Simple Volume for the SSD disk,with the following three changes:

Moving the Windows paging file

Now that the new volumes are created and mounted, move the Windows paging fileonto the Local SSD disk, which frees up Persistent Disk IOPS and improves the accesstime of your virtual memory.

  1. From theStart menu, search forView advanced system settings andthen open the dialog.
  2. Click theAdvanced tab, and in thePerformance section, clickSettings.
  3. In theVirtual memory section, click theChange button.
  4. Clear the checkboxAutomatically manage paging file size for all drives. Thesystem should have already set up your paging file on theC:\ drive, and youneed to move it.
  5. ClickC: and then click theNo paging file radio button.
  6. Click theSet button.
  7. To create the new paging file, click theP: drive, and then click theSystem managed size radio button.
  8. Click theSet button.

  9. ClickOK three times to exit the advanced system properties.

    Microsoft Support has publishedadditional tips for virtual memorysettings.

Setting the power profile

Set the power profile toHigh-Performance instead ofBalanced.

  1. From theStart menu, search for "Choose a Power Plan", and then open thepower options.
  2. Select theHigh Performance radio button.
  3. Exit the dialog.

Configuring SQL Server

Use SQL Server Management Studio to perform most administrative tasks. Thepreconfigured images for SQL Server come with Management Studio alreadyinstalled. Launch Management Studio and then clickConnect toconnect to the default database.

Moving the data and log files

The preconfigured image for SQL Server comes with everything installed ontheC:\ drive, including the system databases. To optimize your setup,move those files to the newD:\ drive you created. Also remember to createall new databases on theD:\ drive. Because you are using an SSD, you don'tneed 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 theinstaller or moving the files manually.

Using the installer

To use the installer, runc:\setup.exe and select a new installation path onyour secondary disk.

Moving the files manually

Move the system databases and configure SQL Server to save the data and logfiles on the same volume:

  1. Create a new folder namedD:\SQLData.
  2. Open a Command Window.

  3. Enter the following command to grant full access toNT Service\MSSQLSERVER:

    icaclsD:\SQLData/Grant"NT Service\MSSQLServer:(OI)(CI)F"

  4. Use Management Studio and the following guides tomove your systemdatabasesandchange the default file locations for new databases.

  5. If you plan on usingReport Server features, move theReportServerandReportServerTempDB files as well.

Important: Don't modify thetempdb files yet because you must first changesome permission settings and then move the DB to the Local SSD volume.

After you move the primary configuration database files and restart, you need toconfigure the system to point to the new location for the model and MSDBdatabases. 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:

  1. Use theservices.msc snap-in to stop the SQL Server database service.
  2. Use the Windows file explorer to move the physical files from theC:\ drivewhere themaster database was located to theD:\SQLData directory.
  3. Start the SQL Server database service.

Setting system permissions

After moving the system databases, modify some additional settings, startingwith permissions for the Windows user account created to run your SQLServer process, which is namedNT Service\MSSQLSERVER.

Granting theLock Pages in Memory permission

The group policyLock Pages in Memory permission prevents Windows frommoving pages in physical memory to virtual memory. To keep physicalmemory free and organized, Windows tries to swap old, rarely modified pages tothe 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, soit becomes a target for the paging file. If this information gets moved to thepaging file, SQL Server performance can degrade. Granting the group policyLockPages in Memory permission for SQL Server's service account prevents thisswapping.

Follow these steps:

  1. ClickStart and then search forEdit Group Policy to open the console.
  2. ExpandLocal Computer Policy >Computer Configuration >Windows Settings > Security Settings >Local Policies >User Rights Assignment.
  3. Search for and then double-clickLock pages in memory.
  4. ClickAdd User or Group.
  5. Search for "NT Service\MSSQLSERVER".
  6. If you see multiple names, double-click theMSSQLSERVER name.
  7. ClickOK twice.
  8. Keep theGroup Policy Editor console open.

Lock pages

Important: After completing these steps and restarting SQL Server, you canverify the setting is working by viewing the SQL Server log. The log contains anentry such as, "Using locked pages in the memory manager."

Granting thePerform volume maintenance tasks permission

By default, when an application requests a slice of disk space from Windows, theoperating system locates an appropriately sized chunk of disk space, and thenzeroes 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, thisbehavior is not optimal.

There is a separate API for allocating disk space to an application, oftenreferred to asinstant file initialization. Unfortunately, this setting onlyworks for data files, but you will learn in an upcoming section about log-filegrowth. Instant file initialization requires the service account running the SQLServer process to have another group policy permission, calledPerform volumemaintenance tasks.

  1. In theGroup Policy Editor, search for "Perform volume maintenance tasks".
  2. Add the "NT Service\MSSQLSERVER" account as you did in the previous section.
  3. Restart the SQL Server process to activate both settings.

Setting uptempdb

It used to be a best practice to optimize the SQL ServerCPU usage by creating onetempdb file per CPU. However, because CPU countshave grown over time, following this guideline can cause performance todecrease. As a good starting point, use 4tempdb files. As you measure yoursystem's performance, in rare cases you might need to incrementally increase thenumber oftempdb files to a maximum of 8.

You can run a Transact-SQL (T-SQL) script inside SQL Server Management Studio tomove thetempdb files to a folder in the `p:` drive.

  1. Create the directoryp:\tempdb.

  2. Grant full security access to the "NT Service\MSSQLSERVER" user account:

    icaclsp:\tempdb/Grant"NT Service\MSSQLServer:(OI)(CI)F"

  3. Run the following script inside SQL Server Management Studio, to move thetempdb data file and log file:

    USE masterGOALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf')GOALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf')GO

  4. Restart SQL Server.

  5. Run the following script to modify the file sizes and create three additional data files for the newtempdb.

    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 additionaltempdb files to removeafter you do the previous steps:

    ALTER DATABASE [tempdb] REMOVE FILE temp2;ALTER DATABASE [tempdb] REMOVE FILE temp3;ALTER DATABASE [tempdb] REMOVE FILE temp4;

  6. Restart SQL Server again.

  7. Delete themodel,MSDB,master, andtempdb files from the originallocation on theC:\ drive.

You successfully moved yourtempdb files onto the Local SSD disk partition.This move carries some risks, mentioned earlier, but if they are lost for anyreason,SQL Server rebuilds thetempdb files. Movingtempdb gives you theadded performance of the Local SSD, and decreases the IOPS used on yourPersistent Disk volumes.

Caution: Local SSD is not intended for storage of permanent or critical data,because the disk is pinned to the local host. Therefore, use Local SSD onlyfor temporary storage. In some cases, if you shut down Windows from within theinstance, the Local SSD disk is also removed, and you have to delete andrecreate your instance.

Settingmax degree of parallelism

The recommended default setting formax degree of parallelism is to match itto the number of CPUs on the server. However, there is a point where executing aquery in 16 or 32 parallel chunks and merging the results is much slower thanrunning it in a single process. If you are using a 16- or 32-core instance, youcan set themax degree of parallelism value to 8 using the following T-SQL:

USE masterGOEXEC sp_configure 'show advanced options', 1GORECONFIGURE WITH OVERRIDEGOEXEC sp_configure 'max degree of parallelism', 8GORECONFIGURE WITH OVERRIDEGO

Settingmax server memory

This setting defaults to a very high number, but you should set it to the numberof megabytes of available physical RAM, minus a couple gigabytes for operatingsystem and overhead. The following T-SQL example adjustsmax server memory to100 GB. Modify it to adjust the value to match your instance. Review theServer memory server configuration options documentfor more information.

EXEC sp_configure 'show advanced options', 1GORECONFIGURE WITH OVERRIDEGOexec sp_configure 'max server memory', 100000GORECONFIGURE WITH OVERRIDEGO

Finishing up

Restart the instance one more time to make sure all of the new settings takeeffect. Your SQL Server system is configured and you are ready to create yourown databases and start testing your specific workloads. Review theSQL ServerBest Practices guide for more information on operational activities, other performanceconsiderations, 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:

    Caution: Deleting a project has the following effects:
    • Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
    • Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

    If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

  1. In the Google Cloud console, go to theManage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then clickDelete.
  3. In the dialog, type the project ID, and then clickShut down to delete the project.

Deleting instances

To delete a Compute Engine instance:

  1. In the Google Cloud console, go to theVM instances page.

    Go to VM instances

  2. Select the checkbox for the instance that you want to delete.
  3. To delete the instance, clickMore actions, clickDelete, and then follow the instructions.

Deleting Persistent Disk volumes

To delete the Persistent Disk:

  1. In the Google Cloud console, go to theDisks page.

    Go to Disks

  2. Select the checkbox next to the name of the disk you want todelete.

  3. Click theDelete button at the top of the page.

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-09 UTC.