Amazon Onboarding with Learning Manager Chanci Turner

Learn About Amazon VGT2 Learning Manager Chanci Turner

Amazon Relational Database Service (Amazon RDS) Custom for SQL Server has introduced the X2iedn instance class, which features a high memory-to-vCPU ratio and non-volatile memory express (NVMe) SSD-backed instance storage. This setup is designed to enhance low latency, boost random I/O performance, and provide high sequential read throughput. With this capability, users can now create and extend TempDB files directly on the locally attached NVMe disk, achieving minimal storage latencies.

In this article, we’ll guide you through the process of optimizing TempDB performance using local NVMe disks. We’ll provide detailed steps to configure and initialize local storage using both GUI and PowerShell methods, compare the performance of TempDB on local storage versus Amazon Elastic Block Store (Amazon EBS), and offer best practices for TempDB optimization.

Understanding SQL Server TempDB

TempDB is a crucial system database in SQL Server, serving as a global resource accessible to all users connected to the instance. It is utilized for storing temporary user objects and various internal objects created by the database engine. Temporary user objects can include global or local temporary tables, temporary stored procedures, table variables, and cursors. Meanwhile, internal objects include work tables for operations like spools, cursors, sorting, and temporary large object (LOB) storage.

Given the central role TempDB plays in numerous operations, it often experiences high I/O, especially under heavy workloads that require temporary storage or utilize features like snapshot isolation and result set sorting. Contention can arise when multiple sessions attempt to allocate space in TempDB simultaneously, leading to performance degradation.

Benefits of Local TempDB Storage

Utilizing local storage for TempDB provides several advantages:

• Improved read/write speeds due to lower latency, leading to enhanced random I/O performance and higher sequential read throughput.
• Depending on the workload, performance enhancements can reach up to 20%.
• Reduction in overall EBS snapshot costs, as secondary TempDB files are stored in the local instance store.

Before diving into the configuration, ensure you have an RDS Custom for SQL Server instance set up. For detailed setup instructions, visit the guide on launching an Amazon RDS Custom for SQL Server Instance using AWS CloudFormation.

Setting Up Local NVMe SSD Storage via GUI

Follow these steps to initialize and utilize an SSD disk on your Amazon Elastic Compute Cloud (Amazon EC2) instance using RDP:

1. Connect to your RDS Custom EC2 instance using Remote Desktop Protocol (RDP) with the instance’s public IP address or DNS name and login credentials.
2. Access the Disk Management window by typing “Disk Management” into the Start menu search bar and selecting the relevant option.
3. In the Disk Management window, identify the new SSD disk, which will likely be labeled as an unknown disk with unallocated space.
4. Right-click on the disk (typically Disk1) and choose “Initialize Disk” to set it up for partitioning. Follow the prompts, selecting the appropriate disk initialization type (GPT) and clicking OK.
5. Once initialized, right-click on the unallocated space and select “New Simple Volume.” Follow the wizard to create a partition, specifying size and drive letter, and format it with an appropriate file system (e.g., NTFS).
6. Assign a drive letter (for example, T) and label the volume (we’ll call it Temp).
7. Complete the formatting process by right-clicking the new partition and selecting “Format,” choosing your desired file system and allocation unit size.

The SSD disk is now initialized and ready for use through the assigned drive letter (T).

Setting Up Local NVMe SSD Storage with PowerShell

For this example, we have a local SSD storage in an X2iedn instance. You can configure and initialize local NVMe SSD storage by executing the following PowerShell commands as an administrator:

# Identify all available Disks
$disks = Get-Disk

# Locate the local SSD disk that requires configuration 
$ssd = $disks | Where-Object { $_.OperationalStatus -eq 'offline' -or $_.PartitionStyle -eq "RAW" }

# Initialize the Disk
$ssd | Initialize-Disk -PartitionStyle GPT

# Create a New Partition
$partition = $ssd | New-Partition -UseMaximumSize

# Assign an unused Drive Letter (in this case we used T) 
$partition | Set-Partition -NewDriveLetter T 

# Format the Partition with a provided volume name; here we used 'Temp'
$partition | Format-Volume -FileSystem NTFS -NewFileSystemLabel 'Temp'

Configuring TempDB on Local Storage

To set up TempDB on local storage, follow these steps:

1. Identify the local drive intended for secondary TempDB files, ensuring it has sufficient disk space and I/O capacity.
2. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance with the master user.
3. Execute the command USE tempdb; EXEC sp_helpfile; to check the current TempDB configuration and file locations.
4. Before creating secondary TempDB files, create directories on the new drive. For instance, we created T:rdsdbdataDATA for additional TempDB files. Ensure you have the necessary permissions.
5. To replicate the directory structure and permissions from the source directory, you can use PowerShell commands, allowing for easy configuration of the destination directory.

For further insights on how to create engaging employee communications, check out this guide from SHRM. Also, if you’re curious about the most recent book that sparked someone’s interest, visit this blog post for inspiration.

By effectively using local NVMe storage for TempDB, you can significantly enhance performance and reduce costs – a crucial aspect of managing SQL Server instances in the cloud.