Creating a High-Performance SQL Server Instance

This tutorial shows how to create a Google Compute Engine VM instance running SQL Server that is optimized for performance. This tutorial walks you through creating the instance and then configuring SQL Server for optimal performance on Google Cloud Platform. You 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 2014, 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 Cloud Platform, including:

  • Google 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 3000 dollars per month. New Cloud Platform users might be eligible for a free trial.

Before you begin

  1. Sign in to your Google account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Cloud Platform project.

    Go to the Projects page

  3. Enable billing for your project.

    Enable billing

  4. If you aren't using Windows on your local machine, install a third-party RDP client such as Chrome RDP by FusionLabs.

Creating the Compute Engine instance and disks

Create the Compute Engine instance with SQL Server and two persistent disks.

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

    SSD 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/s and 400 MB/s 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 Optimizing Persistent Disk and Local SSD Performance.

Creating the Compute Engine instance

Create a VM that has SQL Server 2014 Standard preinstalled on Windows Server 2012.

  1. In the Google Cloud Platform Console, go to the VM Instances page.

    Go to the VM Instances page

  2. Click the Create instance button.

  3. Name your instance "ms-sql-server".
  4. Set Machine type to 16 vCPUs 104 GB, n1-highmem-16.
  5. In the Boot disk section, click Change to begin configuring your boot disk.
  6. In the Application images tab, choose SQL Server 2014 Standard on Windows Server 2012 R2.
  7. In the Boot disk type section, select Standard persistent disk.
  8. In the Size (GB) section, set the boot disk size to 50 GB.
  9. Click Select.
  10. Expand Management, disk, networking, SSH keys.
  11. In the Disks tab, under Additional disks, click Add item.
  12. In the Name field, select Create disk from the dropdown menu.
  13. On the Create a disk page, leave the Name field unchanged.
  14. Choose Local SSD scratch disk (maximum 8) under Disk Type.
  15. Click Create.
  16. Under Additional disks, click Add item.
  17. In the Name column, click Create disk in the dropdown menu.
  18. Choose SSD persistent disk under Disk Type.
  19. Choose None (blank disk) for Source type.
  20. Leave the Name field unchanged.
  21. Click Create to create the disk.
  22. Click Create to create the instance.

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

  1. Go to the VM Instances page in the Cloud Platform Console.

    Go to the VM Instances page

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

  3. At the top of the instance's details page, click the Create or reset Windows Password button.
  4. Specify a username.
  5. Click Set 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 instance using RDP:
  8. If you installed Chrome RDP by FusionLabs, click the RDP button at the top of the instance's details page.
  9. If you're using a different RDP client, such as the Windows Remote Desktop Connection, click the RDP button's overflow menu and download the RDP file. Open the RDP file by using your client.

Setting up disk volumes

Create and format the volumes:

  1. From the Start menu, search for “Server Manager” and then open it.
  2. Select File and Storage Services and then select Disks.

    The local SSD is named Google EphemeralDisk. Both the local SSD and persistent SSD are marked as having Unknown partitions:

    Find the Google EphemeralDisk entry

  3. Right-click the 375 GB local SSD disk named “Google Ephemeral Disk”, and then select New Volume.

  4. Proceed with the defaults and choose P: for the disk label, because this will be the paging-file disk.

  5. When you get to the file-system-settings step, change the Allocation unit size to 8192 and enter “pagefile” for the Volume label .

    New Volume Wizard

  6. Repeat the same steps above for the second SSD persistent disk, except with the following three changes:

  7. Choose D: for the drive letter.

  8. Set the Allocation unit size to "32k".

    Microsoft recommends that the SQL Server data and log disks be formatted as 64k, but the persistent disk technology inside GCP aligns better with 32k. This change also decreases the number of disk operations that count toward your persistent disk IO limit.

  9. Enter “sqldata” for the Volume label.

Fixing the Failed to mount path - Invalid Parameter error

If you encounter this error, follow these steps:

  1. Click Close.
  2. Click the refresh disks icon in the upper right.
  3. Click the 500 GB persistent disk in the list.
  4. In the Volumes panel, right-click the volume and then choose Manage Drive Letter and Access Paths.

    Manage Drive Letter and Access Paths

  5. Select D: for the drive letter.

  6. Click OK.

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.

  1. From the Start menu, search for View advanced system settings and then open the dialog.
  2. Click on the Advanced tab, and in the Performance section, click Settings.
  3. In the Virtual memory section, click the Change button.
  4. Uncheck the box 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.
  5. Click C: and then click the No paging file radio button.
  6. Click the Set button.
  7. To create the new paging file, click the P: drive, and then click the System managed size radio button.
  8. Click the Set button.
  9. Click OK three times to exit the advanced system properties.

Microsoft Support has published a additional tips for virtual memory settings.

Setting the power profile

Set the power profile to High-Performance instead of Balanced.

  1. From the Start menu, search for “Choose a Power Plan”, and then open the power options.
  2. Select the High Performance radio button.
  3. Exit the dialog.

Configuring SQL Server

Use SQL Server Management Studio to perform most administrative tasks. The preconfigured images for SQL Server 2014 come with Management Studio already installed, but if you are using the SQL Server 2016 image you need to download and install it manually. After installation, 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. In order 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. Since you are using an SSD persistent disk, 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:

  1. Create a new folder named D:\SQLData.
  2. Open a command window.
  3. Enter the following command to grant full access to NT Service\MSSQLSERVER:

    icacls D:\SQLData /Grant "NT Service\MSSQLServer":(OI)(CI)F
    
  4. Use Management Studio and the following guides to move your system databases and change the default file locations for new databases.

  5. If you plan on using Report Server features, move the ReportServer and ReportServerTempDB files as well.

After you move the master 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:

  1. Use the services.msc snap-in to stop the SQL Server database service.
  2. Use the Windows file explorer to move the physical files from the C:\ drive where the Master database was located to the D:\SQLData directory.
  3. 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. In order 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:

  1. Click Start and then search for Edit Group Policy to open the console.
  2. Expand Local Computer Policy > Computer Configuration > Windows Settings > Local Policies > User Rights Assignment.
  3. Search for and then double-click Lock pages in memory.
  4. Click Add User or Group.
  5. Search for "NT Service\MSSQLSERVER".
  6. If you see multiple names, double-click the MSSQLSERVER name.
  7. Click OK twice.
  8. Keep the Group Policy Editor console open.

Lock pages

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.

  1. In the Group 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 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 T-SQL script inside SQL Server Management Studio to move the TempDB files to a folder in the P:\ drive.

  1. Create the directory p:\tempdb.
  2. Grant full security access to the “NT Service\MSSQLSERVER” user account:

    icacls p:\tempdb /Grant "NT Service\MSSQLServer":(OI)(CI)F
    
  3. 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
    

  4. Restart SQL Server.

  5. 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;
    

  6. Restart SQL Server again.

  7. Delete the model, MSDB, master, and tempdb files from the original location on the C:\ 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.

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.

Cleaning up

After you've finished the SQL Server tutorial, you can clean up the resources you created on Google Cloud Platform so you won't be billed for them in the future. 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 you created for the tutorial.

To delete the project:

  1. In the Cloud Platform Console, go to the Projects page.

    Go to the Projects page

  2. In the project list, select the project you want to delete and click Delete project. After selecting the checkbox next to the project name, click
      Delete project
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

Deleting instances

To delete a Compute Engine instance:

  1. In the Cloud Platform Console, go to the VM Instances page.

    Go to the VM Instances page

  2. Click the checkbox next to the instance you want to delete.
  3. Click the Delete button at the top of the page to delete the instance.

Deleting persistent disks

To delete a persistent disk:

  1. In the Cloud Platform Console, go to the Disks page.

    Go to the Disks page

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

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

What's next

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...

Compute Engine