Optimizing SQL Server Operations and Scale Technical Solution Design

Four cores

4GB of RAM

Network cards/Ethernet adapters with at least 2 x 10Gb Ethernet ports for application traffic

Network cards/Ethernet adapters with at least 2 x 25Gb Ethernet ports for storage traffic (if using iSCSI, NVM Express over TCP, or Server Message Block/Network File System file storage)

Fibre Channel adapter with at least 2 x 16Gb Ethernet ports for storage traffic using Fibre Channel Protocol

Highest availability/lowest performance—active/passive (Mode 1)

Highest performance with no switch-side configuration—active load balancing (Mode 6)

Highest performance and highest availability—802.3ad/LACP (Mode 4)

If host limits allow it, use all available Fibre Channel Protocol ports on FlashArray.

Use single-initiator, multi-target zoning, where one SQL Server instance acts as the initiator and targets multiple FlashArray devices. This will create multiple paths to a single volume.

Use consistent port speeds across the fabric for the best experience.

Verify that all network paths are clean; address any cyclic redundancy checks or other errors.

Ensure that the maximum transmission unit is set consistently end-to-end.

Disable network adapter power management.

Verify all network paths are clean; address any cyclic redundancy checks or other errors.

FlashArray file services are built using physical Ethernet interfaces mapped to a virtual interface. Use a virtual interface to combine one or more physical ports on both controllers into a group for managing the storage network.

The FlashArray file services support page contains comprehensive details on how to configure and manage virtual interfaces.

FlashBlade network fabrics are built using link aggregation groups, subnets, and virtual network interfaces.

Link aggregation groups are aggregated physical uplinks that are used to optimize availability and load balancing.

A subnet is a virtual representation of a network range and its associated configurations, and it is bonded to a link aggregation group. Subnet settings include options for VLANs and maximum transmission units.

A virtual network interface is an interface that connects clients to specific storage protocols and is bonded to a subnet. A virtual network interface usually uses a dedicated static IP address, and it includes options for VLANs and maximum transmission units.

To avoid network bottlenecks, the best practice recommendation is to implement a link aggregation group capable of the same speed as the number of hosts times the speed of one network port on each. For example, if the servers have 2 x 25Gb Ethernet ports, then the FlashBlade link aggregation group needs to be capable of 100Gb/s minimum. All subnets and virtual interfaces should have the same maximum transmission unit configuration.

Place SQL Server data files (.mdf and .ndf) and log files (.ldf) on separate volumes to manage input/output more effectively.

Distribute data files across multiple volumes to balance the input/output load.

Align the file system and block size to avoid misaligned input/output, which can degrade performance.

Use a 64KB allocation unit size for NTFS volumes hosting SQL Server data and log files

Place SQL Server data files (.mdf and .ndf) and log files (.ldf) on separate volumes to manage input/output more effectively.

Distribute data files across multiple volumes to balance the input/output load.

As TempDB is heavily utilized by SQL Server, make sure to provision low-latency volumes for these files.

Utilize space-efficient, easily captured volume snapshots for data protection, backups, and restores.

Use ActiveDR for asynchronous replication to a secondary site for disaster recovery that will not affect the performance of the primary site.

Use ActiveCluster to provide high availability for SQL Server, using zero and near-zero recovery point objective capabilities.

Use data encryption and access controls to secure data at rest. Regularly review permissions to ensure data security.

Configure parallel backup and restore to enable high-frequency backups and minimize downtime during data recovery.

Use snapshots to create point-in-time database copies that can be used for fast data recovery without impacting performance.

Use high-speed network connectivity between SQL Server and FlashBlade and configure multiple network paths to facilitate storage redundancy and load balancing.

Format and organize data to enhance query performance. Use parquet or optimized row columnar file formats and configure volume partitioning to optimize data access.

Use the T-SQL query language to directly access FlashBlade, along with PolyBase to query external tables or the OPENROWSET function, enabling database integration and efficient processing.

Use PolyBase for fast access to large datasets stored as objects in S3 buckets.

SQL Server binaries: Install SQL Server on the operating system drive.

Data files: Do not place database and backup files on the same disk where the operating system and binaries exist. Do not put SQL Server data and log files on the root of a drive or a mount point. Do not create data files on the root of a file system in Windows Server. Use naming conventions for drive letters or mount points that make sense for that environment. The following are two examples of naming conventions:

Transaction log files: All transaction log files can be located on a single virtual disk, or each database can have a virtual disk provisioned for it (recommended) and then mounted under this structure. The following is an example of a naming convention:

TempDB files: Provision a single virtual disk to contain the data and log files for TempDB. Either T:\TempDB or a mount point under the operating system drive, such as C:\SQL\TempDB.

Spread database files across multiple volumes: If leveraging the FlashArray SQL Server Management Studio extension, the best practice structure for multiple databases uses a consistent deployment of data and transaction log files. Either place all the data and log files for multiple databases into a single volume/folder or use a dedicated volume for storing the data and log files of a single database. Avoid a multiple database structure that dedicates data files for each database to a separate volume/folder while placing transaction log files for multiple databases on a single volume/folder.

Data: /var/opt/mssql/data

Log: /var/opt/mssql/log

TempDB: /var/opt/mssql/tempdb

Input/output scheduler: Use the None scheduler for all virtual disks that contain SQL Server data files. For more information, see the "How to use the Noop or None IO Schedulers" solution page.

Disk readahead: Set the readahead setting to 4,096.

vm.swappiness: Set vm.swappiness to 1 for high-performance systems.

vm.dirty_*: Set vm.dirty_ratio to 80 and vm.dirty_background_ratio to 3.

kernel.sched_*: Set kernel.sched_min_granularity_ns to 15,000,000 and kernel.sched_wakeup_granularity_ns to 2,000,000.

Data files contain data and objects such as tables, indexes, stored procedures, and views. Data files can be either primary (with the .mdf extension), containing database startup information, or secondary (with the .ndf extension), containing optional user-defined data. Data files can be grouped together into filegroups for allocation and administration purposes.

Transaction log files (with the .ldf extension) contain the information required to recover all transactions in a database.

SQL Server Management Studio: Use the graphical user interface to configure server and database settings.

PowerShell: Use command-line interface, scripting language, and cmdlets to configure server and database settings.

mssql extension: Use this command-line interface for T-SQL commands to set various parameters.

Set Max Memory to recommend 75% of server memory. There is no need to set the minimum value. Microsoft recently released recommendations on memory settings, and you can find more information about it in this guidance.

Set Max Degree of Parallelism (MAXDOP) to less than or equal to the number of cores in a NUMA node of less than or equal to eight. This is an advanced setting. It is recommended that you read the Microsoft documentation before changing this setting.

The Cost Threshold for parallelism should be optimized while a workload is running, but start at a value of fifty.

Lock pages in memory, when set, can prevent external pressure to deallocate SQL Server memory processes.

Turn on accelerated database recovery (SQL Server 2019 and newer) to improve database availability, especially in the presence of long-running transactions.

Enable query store (SQL Server 2016 and newer) to collect and store information about query processing and runtimes. While query store should be enabled for most cases, test with your workloads to confirm optimal performance. Query store does not work in some scenarios, such as Always On Availability Group readable replicas.

Turn on ad-hoc workload optimization, which limits an execution plan from being cached until it is executed a second time. The first execution plan is saved only as a stub, which prevents memory bloat from too many execution plans being cached. Additional information can be found at the "optimize for ad hoc workloads (server configuration option)" page.

Turn on auto-update statistics asynchronously, which allows queries to continue executing during the update of index statistics.