Submit a ticketCall us

AnnouncementsChange Is Inevitable

Get valuable help when it comes to tracking and monitoring changes. SolarWinds® Server Configuration Monitor (SCM) is designed to help you: detect, track, and receive alerts when changes occur, correlate system performance against configuration changes, compare server and application configuration against custom baselines, and verify application and system changes.

Learn more.

Home > Success Center > Network Performance Monitor (NPM) > NPM Documentation > NPM 12.3 Administrator Guide > SolarWinds NPM requirements > SQL Server configuration best practices for NPM

SQL Server configuration best practices for NPM

Created by Lori Krell_ret, last modified by Magdalena.Markova on Jul 11, 2018

Views: 6,011 Votes: 10 Revisions: 7

Updated: July 11, 2018

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

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

If there are more databases on a SQL Server, SolarWinds 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.

Maximize SQL Server performance 

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

  • Do not use SQL Express unless you are evaluating SolarWinds Orion for a small deployment without NTA. NetFlow can be a major factor in database sizing, depending on the incoming flow rates.
  • Ensure your SQL Server is updated to the latest Service Pack and Cumulative Update.
  • Do not use WAN connections between the SQL server and the Orion server. This includes any additional polling engines.
  • Do not install the SQL Server on the Orion 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.

Find out more about the database health

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 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/

Reads + writes = IOPSes

Hardware recommendations

  • Do not virtualize the database storage system.
  • 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.
Component Recommendation
Orion 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.
  • 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.

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:

If you prefer checklists, check the one for settings and recommendations.

Maximum Degree of Parallelism (MAXDOP) 

This setting specifies the number of processors used to execute a query in a parallel plan. Use the number of physical cores in a single CPU socket.

Where: Server > Advanced

Cost Threshold of Parallelism

This setting specifies when the SQL server creates and runs parallel plans for queries. The default value 5 is rather low, consider using 50 and adjust as necessary.

Where: Server > Advanced

Instant File Initialization

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 the instant file initialization.

To enable instant file initialization, make sure the service account has the Perform Volume Maintenance Tasks privilege enabled.

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.

Database file settings in general

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

Where: Database > Properties > Files

  • Data files: autogrowth 1024 MB
  • Transaction log file:
    • initial size 8192 MB, autogrowth 8192 MB for large environments
    • initial size 2048 MB, autogrowth 1024 MB for medium environments

Initial size and autogrowth settings for data files and log files in the 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.

Memory settings

  • 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 sqlmem (©2016-2018 Microsoft, available at https://archive.codeplex.com/?p=sqlmem, obtained on July 10, 2018) or SQL Max Memory Calculator (©2018 mirontolli, available at http://sqlmax.chuvash.eu/, obtained on July 10, 2018)
  • 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 setting recommendations 

  • Ensure that power-saving technologies are disabled on the CPU.

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.
  • Use the sys.fn_hadr_backup_is_preferred_replica to get the replica preferred for backups (Review the following sample backup procedure).
    CREATE PROCEDURE p_BackupDatabaseAG 
    (
     @DatabaseName SYSNAME, 
     @BackupPath VARCHAR(256),
     @BackupType VARCHAR(4)
    )
    AS
    BEGIN
    DECLARE @FileName varchar(512) = @BackupPath + 
     CAST(@@SERVERNAME AS VARCHAR) + '_' + @DatabaseName;
    DECLARE @SQLcmd VARCHAR(MAX);
    IF sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
     IF @BackupType = 'FULL'
     BEGIN
      SET @FileName = @FileName + '_FULL_'+ 
       REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), '/', '') + 
       REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ':', '')  + '.bak';
      SET @SQLcmd = 'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + 
       ' TO DISK = ''' + @FileName + ''' WITH COPY_ONLY ;';
      --PRINT @SQLcmd
      EXECUTE(@SQLcmd);
     END;
     ELSE IF @BackupType = 'LOG'
     BEGIN
      SET @FileName = @FileName + '_LOG_'/*+ 
       REPLACE(CONVERT(VARCHAR(10), GETDATE(), 112), '/', '') + 
       REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108) , ':', '') */ + '.trn';
      SET @SQLcmd = 'BACKUP LOG ' + QUOTENAME(@DatabaseName) + 
       ' TO DISK = ''' + @FileName + ''' WITH NOFORMAT, INIT;';
      --PRINT @SQLcmd
      EXECUTE(@SQLcmd);
     END;
    END; 

Enable the Query Optimizer Fixes

In SQL 2016 and SQL 2017, make sure the Query Optimizer Fixes setting is on (Database > Properties > Options > Query Optimizer Fixes = ON)

In earlier SQL versions, enable trace flag 4199 at service level. Run the following SQL query:

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.

Check the compatibility level of the database according to SQL Server version

Where to find: Database > Properties > Options > Compatibility level

Make sure the highest available option is selected. For example, do not use SQL Server 2008(100) for SQL Server 2017.

Settings and Recommendations Checklist

  Setting Recommendation
checkbox.gif

Maximum Degree of Parallelism (MAXDOP)

Server > Advanced

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

Cost Threshold of Parallelism

Server > Advanced

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

Perform Volume Maintenance Task right for SQL user account

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.
checkbox.gif

Data files initial size and autogrowth

Database > Properties > files

Data files - autogrowth 1024 MB
checkbox.gif 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
checkbox.gif Initial size and autogrowth for tempdb
  • Data files - initial size 1024 MB, autogrowth 1024 MB 
  • Log file - initial size 2048 MB, autogrowth 1024 MB
checkbox.gif Recovery mode

Use SIMPLE if possible. FULL requires further measures.

checkbox.gif 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);
checkbox.gif

Compatibility level

Database > Properties > Options

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

 
Last modified

Tags

Classifications

Public