Documentation forSolarWinds Platform Self-Hosted

SQL Server configuration best practices for SolarWinds Platform products

This topic applies to all SolarWinds Platform (self-hosted) products.

The standard SQL environment for the SolarWinds Platform contains the following components:

  • A dedicated SQL Standard or Enterprise Server
  • Directly attached (DAS), RAID 10 storage (I/O subsystem)
  • LAN attachment between themain SolarWinds Platform server and any additional components

If there are more databases on a SQL Server, SolarWinds strongly recommends that you use dedicated hard drives for the tempdb database. Use at least one hard drive for data files, and one hard drive for the transaction log. All databases use the same tempdb, therefore the tempdb can be the biggest bottleneck in the I/O subsystem.

Identify Performance issues

Review the following symptoms of performance issues:

  • Web performance is slow.
  • Long running queries in Orion log files.
  • SQL Client timeout errors in Orion log files.

Examples of tools for troubleshooting performance issues

  • SolarWinds DPA: if real-time data is not enough to troubleshoot performance issues, use DPA.

Troubleshoot performance issues with SolarWinds DPA

Use the SolarWinds Database Performance Analyzer (DPA) to find out the latency or disk queue length.

Latency and disk queue length

  • A healthy database has the read and write latency of 1-5 ms. When the value rises to 10ms for more than a few peak values, there are issues.
  • A healthy disk queue length can have up to a few peaks under 10 during the day. Issues start when there are peaks every hour.

Sessions

  • If you see blocked sessions, there are issues similar to issues with the disk queue length.

Performance counters

Performance counters from PERFMON or another monitoring system can also help you find out more about the database health. Check all the disks the Database uses and their reads/writes/queues/ running the following:

Reads + writes = IOPSes

Review recommended settings for SQL Server

SQL Server Software Recommendations

When planning your SQL Server configuration, make sure your database server meets the requirements and consider the following hints:

  • When using SQL Express, be aware of MS SQL Express limits, such as 10 GB as the maximum size for the database. See the latest system requirements for details.

  • Ensure your SQL Server is updated to the latest Service Pack and Cumulative Update.

    This ensures that addressed customer issues, as well as supportability, logging, security, and reliability updates, are applied on your SQL Server. See Announcing updates to the SQL Server Incremental Servicing Model (ISM) in Microsoft help (© 2020 Microsoft, available at https://docs.microsoft.com/en-us/archive/blogs/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism, obtained on October, 2020).

  • Do not use WAN connections between the SQL server and the SolarWinds Platform server. This includes any additional polling engines.

  • Do not install the SQL Server on the SolarWinds Platform server.

  • The performance of the SQL Server depends on the performance of the I/O subsystem.

  • The more disks there are in a RAID 10 array, the better.

  • Many RAID controllers do not handle RAID 01 well.

  • Solid state drives improve performance.

Review the Hardware recommendations and SQL Server Settings.

Hardware recommendations

  • If you virtualize the database storage system, ensure the SQL storage system has exclusive access to the storage.
  • Dedicate the storage to the database server.
  • Use a disk/RAID for the database data. Do not use just a volume in Windows created at the same physical disk/RAID shared for all data.
  • Use a disk/RAID for transaction log file.
  • Use an SSD disk for tempdb. You do not need to have tempdb data on redundant disks in some kind of RAID, a single dedicated fast storage is enough.
    • You can configure tempdb at two disks but it is usually not necessary.
    • Use one disk for tempdb data.
    • Use one disk for tempdb transaction log.
  • Use a disk/RAID for OS and installation.
  • Use a disk/RAID for Memory Optimized Filegroup.
  • Use a disk/RAID for backups.

Why not RAID 5?

RAID 5 is the most common Disk subsystem topology deployed on servers. RAID 5 or 6 may be fine for small environments but will cause performance issues when scaled. This topology is meant for availability and not performance and may cause IO issues in SQL.

Recommended configuration

Component Recommendation
SolarWinds Platform database
  • A dedicated RAID 1+0 hard drive for data files (.mdf, .ndf).
  • A dedicated RAID 1+0 hard drive with fast sequential writing for transaction files (.ldf).
SQL Server temporary
directory (tempdb) database
  • A dedicated RAID 1+0 hard drive for data files (.mdf, .ndf).
  • A dedicated RAID 1+0 hard drive with fast sequential writing for transaction files (.ldf).
SQL Server host system (Windows)
  • A dedicated hard drive of any type.

Recommendations for multi-CPU systems and the optimal settings of the I/O subsystem

On multi-CPU systems, you can increase the performance of some operations by creating more data files on a single hard drive.

Every logical CPU is considered to be one CPU.

  • Having more files in the filegroup helps the SQL Server to distribute the load generated by multiple threads while working with files.
  • The recommended ratio between the number of cores and the files in the filegroup is typically 4:1 or 2:1 (for example, 16 cores and four files, or 16 cores and eight files).
  • The size and growth setting for all files in a filegroup must be set to identical values to distribute the load evenly.
  • The Transaction log should have enough space to grow to 50% of the total database size.
  • For the transaction log, it is not effective to create more files, because the SQL Server can only use the first file.
  • For the tempdb database, use an SSD disk.
  • RAID 1+0 - striping and mirroring for database files
  • Attached Storage (© 2017 Wikimedia Foundation, Inc., available at https://en.wikipedia.org, obtained on February 24th, 2017) (SAN/NAS) Arrays are supported if throughput is high enough. See SCSI, Fiber Channel (© 2017 Wikimedia Foundation, Inc., available at https://en.wikipedia.org, obtained on February 24th, 2017).

Example

Original settings of a system with 16 CPU cores:

  • One hard drive for data with the SolarWindsOrionDatabase.MDF file in the Primary filegroup.
  • One hard drive for the transaction log with the SolarWindsOrionDatabase.LDF file.
  • One hard drive for the tempdb data with the tempdb.MDF file in the Primary filegroup.
  • One hard drive for the tempdb transaction log with the tempdb.LDF file.

Improved settings:

  • One hard drive for data, with the following files in the Primary file group:
    • SolarWindsOrionDatabase01.MDF
    • SolarWindsOrionDatabase02.NDF
    • SolarWindsOrionDatabase03.NDF
    • SolarWindsOrionDatabase04.NDF
  • One hard drive for the transaction log with the SolarWindsOrionDatabase.LDF file.
  • One hard drive for tempdb data, with the following files in the Primary filegroup:
    • tempdb01.MDF
    • tempdb02.NDF
    • tempdb03.NDF
    • tempdb04.NDF
  • One hard drive for the tempdb transaction log with the tempdb.LDF file.

SQL Database Settings

Review the following settings for your SQL server and the database:

Setting Recommendation

Maximum Degree of Parallelism (MAXDOP) specifies the number of processors used to execute a query in a parallel plan.

Server > Advanced

Use the number of physical cores in a single CPU socket.

If you use hyper-threading, divide the number of physical cores in a single CPU socket by two.

Cost Threshold of Parallelism specifies when the SQL server creates and runs parallel plans for queries.

Server > Advanced

 Change the default 5 to 50, and adjust as necessary.

Perform Volume Maintenance Task right for SQL user account

When your SQL server needs to allocate space for operations, it fills the necessary space with zeros. This is often not necessary. To skip this step and use the allocated space for data files immediately, enable instant file initialization.

In the Windows Local Security Policy tool, navigate to Security Settings > Local Policies > User Rights Assignment.

Add the account under which the SQL Service is running to the Perform volume maintenance tasks policy.

During SQL Server Setup

  1. In SQL Server Setup wizard, go to Server Configuration > Service Accounts.
  2. Select the Grant Perform Volume Maintenance Task box.

Using the Windows Local Security Policy tool

  1. Open the Local Security Policy tool, and navigate to Security Settings > Local Policies > User Rights Assignment.
  2. In the pane on the left, select Perform volume maintenance tasks, and add the account under which the SQL Service is running.

New empty database files are created faster. The growth of transaction log files is not affected because log files need all the zeros.

Data files initial size and autogrowth

 

Database > Properties > files
  • Pre-allocate as much disk space as possible to save time.
  • Define an absolute auto-growth setting with a reasonable size, such as 1GB, and so on), instead of an auto-growth percentage.

Data files - autogrowth 1024 MB
Transaction log file initial size and autogrowth
  • Initial size 8192 MB, autogrowth 8192 MB for large environments
  • Initial size 2048 MB, autogrowth 1024 MB for medium environments
Initial size and autogrowth for tempdb

Adjust the initial and autogrowth settings for tempdb:

  • Data files - initial size 1024 MB, autogrowth 1024 MB
  • Log file - initial size 2048 MB, autogrowth 1024 MB

In earlier versions of SQL Server for Linux (2017), there was an inconsistency between the size declared in the Management Studio and the real size on disks.
Enable Query Optimizer fixes
Database > Properties > Options

  • SQL 2016 and 2017
    Query Optimizer Fixes = ON

  • SQL earlier versions:
    Enable trace flag 4199 at service level: 

    DBCC TRACEON (4199);

Flag 4199 allows you to install bug fixes or performance fixes created after a new major version release. If trace flag 4199 is not enabled, these fixes are hidden and blocked to prevent them from harming other programs.

Compatibility level

Database > Properties > Options

 Select the highest available option. For example, do not use SQL Server 2008(100) for SQL Server 2017.
Memory Settings

SQL does not often release memory once granted. Because of this it may appear that SQL is using all of the available memory on the system. This may not cause performance issues as long as the Operating System has sufficient memory to run effectively. You may need to limit the amount of memory SQL can use to prevent this from happening. (How to limit SQL memory (© 2017 Microsoft, available at https://technet.microsoft.com, obtained on February 24, 2017.)

  • Do not reserve all memory to the SQL Server, because this can lead to a lack of memory for the host operating system.
  • The amount of memory to reserve for the operating system depends on the maximum system memory. Use a memory calculator to find out how much to reserve for your system, such as SQL Max Memory Calculator (content provided by Anatoly Mironov, Erik Kronberg, Johannes Milling, Björn Roberg, Stina Qvarnström, Vlad Catrinescu, available at http://sqlmax.chuvash.eu/, obtained on December 13, 2021).
  • If additional resource-intensive services are running on the host operating system, reserve sufficient memory for the host operating system. SolarWinds does not recommend such configuration.
CPU settings
  • Ensure that power-saving technologies are disabled on the CPU.
Recovery mode

Use SIMPLE if possible. FULL requires further measures.

Database Recovery Mode

Recommended Database recovery mode: SIMPLE

When you do not need High Availability, cluster, or replication, use the SIMPLE recovery mode.

Where to set: Database > Properties > Options > Recovery model

If the database was in FULL recovery for some time, the transaction log grew large and you already switched to SIMPLE:

  • Call CHECKPOINT from the Management Studio session window once or twice to flush the log.
  • Change the size of the transaction log file to one of the above-recommended values (both initial size and autogrowth).

When FULL recovery needed

  • Create a SQL Agent job that executes database log backups every 5-10 minutes to ensure that your log file does not get too big.

  • Create a SQL Agent job that executes regular FULL database backups.

  • Ensure that the SQL Agent is enabled at all replicas and the log backup job runs there.

  • Monitor the size of the transaction log file and adjust the backup policy so that the backup file does not grow too large.

  • Plan your backup according to your needs and resources.

SQL Server Replication

SQL Replication (© 2016 Microsoft, available at http://www.microsoft.com, obtained on March 31, 2016) is a Microsoft technology for sharing a database across multiple SQL servers.

All SQL Replication technologies may cause performance issues. Please consult your Database Administrator to verify requirements before enabling any Replication technology.

See Considerations for All Types of Replication (© 2018 Microsoft, available at https://technet.microsoft.com/en-us/library/ms152479(v=sql.105).aspx, obtained on July 11, 2018).

The only redundancy options for Microsoft SQL supported by HA are AlwaysOn Availability Groups and traditional SQL clustering. While Log Shipping and Database Mirroring can be used with the SolarWinds Platform database, they cannot be used as a database redundancy option with SolarWinds Platform High Availability.

Supported server replications:

  • Availability Groups

    For larger environments, synchronous commit mode is not supported because it significantly affects performance.

  • AlwaysOn availability

These replications require full recovery mode. When you set the database to full recovery mode, you need to have fine-tuned backup policy. You need to set up incremental backup logging, or the server gets overloaded fast.

Not supported server replications:

  • SQL Mirroring (causes issues)

  • Log shipping

  • Transaction log

Review recommendations for SQL Server and Virtual Machines

SQL Server is a Microsoft product, and SolarWinds can thus provide only general, experience-based recommendations.

The SolarWinds Platform writes a large number of small writes to the SQL database. To insert data to a table, SQL requires a few reads before the write operation can be done. A large SolarWinds Platform installation thus leads to a high-traffic database (high throughput). The more elements SolarWinds Platform polls, the more information it needs to write to the database in each polling cycle.

Internal virtual machine vs public cloud

For larger SolarWinds Platform deployments, running the SolarWinds Platform database on an internal virtualized SQL server might cause performance issues.

To deploy your SQL Server in a public cloud, make sure the cloud instance meets the database requirements:

What affects the SQL Server performance on internal virtual machines?

  • Host Operating System

    The SQL Server manages its own space in the database files itself, it reads from and writes directly to the disk. Most other applications let the operating system (OS) control their reads to the disk, through a cache.

    When you place the SQL Server in a virtual environment, the virtual machine itself is on an external OS. This external OS controls the disks, and so the SQL Server writes could be cached by the host OS without the SQL Server’s knowledge. This might delay writes to the disk on your virtual environment.

  • Disk space shared by VMs

    In virtual environments, the disk space provided to one VM is carved from a larger array that is used for other VMs. If your SQL Server is installed on a VM, its performance could be affected by other VMs attempting to read/write to the same disks.

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.