Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

Home > Success Center > Network Performance Monitor (NPM) > Network Automation Manager > Network Automation Manager Installation Guide > Sizing and Best Practices resources > SQL Server Configuration Best Practices

SQL Server Configuration Best Practices

Created by Caroline Juszczak, last modified by Caroline Juszczak on Oct 04, 2016

Views: 811 Votes: 1 Revisions: 2

The standard SQL environment 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:

  • WAN connections should never be used between the SQL server and the Orion 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
10:41, 4 Oct 2016

Tags

Classifications

Public