Submit a ticketCall us

Announcing NCM 7.7
With NCM 7.7, you can examine the rules that make up an access control list for a Cisco ASA device. Then you can apply filters to display only rules that meet the specified criteria, order the rules by line number or by the hit count, and much more.
See new features and improvements.

Home > Success Center > Network Performance Monitor (NPM) > NPM 12.2 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 Apr 12, 2017

Views: 736 Votes: 6 Revisions: 5

The standard SQL environment for NPM 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 server and any additional components

If there are more databases on a given SQL Server, it is strongly recommended 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.

Maximizing SQL Server performance

When planning your SQL Server configuration, consider the following information:

  • SQL Express is only suitable for small SolarWinds Orion installations without NTA. NetFlow can be a major factor in database sizing, depending on the incoming flow rates.
  • WAN connections should never be used between the SQL server and the NPM server. This includes any additional pollers.
  • Do not install the SQL Server on the Orion server.
  • The performance of the SQL Server is dependent 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 will improve performance.

Hardware settings for SQL Servers

The following section contains the recommended hardware settings for SQL Servers, taking into account different scenarios and the number of logical disks you use.

Recommendations for maximum performance

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 four logical disks

This configuration is recommended for medium deployments.

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 hard drive for data files (.mdf, .ndf) and the transaction log (.ldf)
SQL Server host system (Windows)
  • A dedicated hard drive of any type. This hard drive should be the slowest of the four available disks.

Recommendations for three logical disks

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 and
SQL Server host system (Windows)
  • A dedicated hard drive for tempdb data files (.mdf, .ndf), tempdb transaction log (.ldf), and host system.

Recommendations for two logical disks

  • Use the disk with the faster sequential writing for the host system and for the transaction log files (.ldf).
  • Use the other disk for data files (.mdf, .ldf), for the tempdb data files, and for the tempdb log files.

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

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

Every logical CPU is considered to be one CPU.

The following example shows the 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.

The previous settings can be improved in the following way:

  • 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.
  • Having more files in the filegroup help 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 in order 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, a RAM disk or an SSD disk can be used.
  • An SSD disk can be used for data files, but it is not effective for the transaction log where sequential access is most important.

Database file setting recommendations

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

Memory setting recommendations

  • Do not reserve all memory to the SQL Server, because this can lead to a lack of memory for the host operating system.
  • Reserve 1 GB of memory to the host operating system if there are no additional services running on the given host system.
  • 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.
Last modified
01:04, 12 Apr 2017

Tags

Classifications

Public