Tech Paper - Best Practices for the Database Server

Version 3

    Deploying the ArcFM Solution on SQL Server White Paper

     

    Following are some best practices to use when configuring the server that will host your geodatabase.

     

    Server Recommendations

    For best performance, always use the latest software versions. The latest ArcFM Solution versions and their dependencies are listed on the ArcFM Solution Technical Support web page. This white paper topic addresses configuratins with Microsoft SQL Server 2008 R2.

    Note: On a database server running Windows Server 2008 R2, you should apply Windows® hotfix KB976700 to avoid excessive kernel time when an application performs many large I/O operations. The excessive kernel time occurs if the total I/O bandwidth of the computer is not large enough, decreasing application performance. For more information, see Microsoft Support Article 976700.

     

    Use a 64-Bit Server

    64-bit servers provide efficient access to the memory and the number of cores required.

     

    Use a Dedicated Server

    If you are mixing workloads on one physical computer, you should use virtual machines or Windows/SQL Server mechanisms to control and monitor resource usage. Diagnosing performance problems requires more effort and precision on a system with mixed workloads.

     

    Storage Design Recommendations

    Processing large volumes of transactions and providing high availability and disaster recovery (HADR), as well as reporting and extracting to data warehouse (DW) systems, requires substantial I/O system resources.

    Following are best practices you should use when designing storage for your ArcFM Solution implementation.

     

    Sue SAN and RAID 10

    You should use RAID 10 if possible for all logical unit numbers (LUNs). RAID 10 offers better performance and availability than RAID 5 and offers better support for write-heavy environments. For optimum and predictable performance, the LUNs must be made up of physical drives that are not used by other applications.

    It is generally better to have a larger number of small drives than a smaller number of large drives. If your storage area network (SAN) has multiple buses (sometimes called shelves), create each LUN using drives from each bus to improve the internal bandwidth. Table 1 shows how to choose drives from multiple buses to make up one LUN.

     

    LUN 1

    LUN 2...

    ...LUN 4

    Bus 1

    1

    4

    7

    10

    13

    ...

    46

    Bus 2

    2

    5

    87

    11

    14

    ...

    47

    Bus 3

    3

    6

    9

    12

    15

    ...

    48

    Table 1, Choosing drives from multiple busses to create a LUN

     

    Refer to the article Predeployment I/O Best Practices for procedures to test your I/O subsystem performance.

    Note: For an online transaction processing (OLTP) system the ideal latency values on a well-tuned I/O subsystem are:

      • < 5 milliseconds (ms) for log (ideally 1 ms on arrays with cache)
      • < 20 ms for data on OLTP systems (ideally 10ms or less)

     

    Set the Partition Offset

    If you are using the Windows Server® 2003 operating system, you should use the Diskpart.exe tool to create the disk partition. Use Diskpart.exe to specify a starting offset of 1 megabyte (MB) to avoid split writes and reads, as these can seriously degrade performance. If the offset is not optimal, a single logical I/O becomes multiple physical I/Os. Some storage manufacturers claim to handle this within their architecture, but there usually is some degradation that can be easily avoided by setting the appropriate offset.

    In Windows Server® 2008 and Windows Server 2008 R2, the partition offset is set appropriately by default for new storage. (Note that the offset of existing storage is not changed when upgrading from Windows Server 2003.)

     

    Set the File Allocation Unit Size and Stripe Size

    When formatting the partition that will be used for SQL Server data files, you should use a 64-kilobyte (KB) allocation unit size for data, logs, and the tempdb database.

    The stripe size is also important to reach an optimal configuration. This is set in the SAN management software, not through Windows Server. The following two equations can be used to determine if you have an optimal configuration. Each should result in an integer value:

    Partition_Offset ÷ Stripe_Unit_Size

    Stripe_Unit_Size ÷ File_Allocation_Unit_Size

    For details on managing disk partition sector alignment, see Disk Partition Alignment Best Practices for SQL Server.

     

    Dedicate a High Percentage of SAN Cache to Writes

    Most SANs have a large cache that can be split between a read cache and a write cache. SQL Server provides read-ahead and read caching, and SQL Server does not benefit much from a SAN read cache. If you have the option (within the constraints of SAN usage by applications), you should dedicate 90% of the SAN cache to writes to improve write performance (a cache ratio of 10/90 read/write).

    Note that virtually all SANs sold today have battery-backed cache. If this is not the case with your SAN, you must disable write caching or risk losing committed data in the event of a power outage.

     

    Configure Windows Drives

    Once you have configured LUNs on your SAN, you need to assign the LUNs to drives in Windows, using disk management to create the drives. Table 3 shows a sample Windows Server storage configuration for use by SQL Server.

    Physical Drive

    LUN

    Drive

    Usage

    1-12

    1

    E

    data

    13-24

    2

    F

    data

    25-36

    3

    G

    tempdb

    37-48

    4

    H

    log

    Table 2, Sample storage configuration for SQL Server.

     

    You can also present storage to SQL Server through mount points, disk volumes that are mounted as folders on other physical disks, without incurring a performance penalty.

    For further information about storage design for SQL Server, see Storage Top 10 Best Practices.

     

    Hardware and Network Guidance

    The following are some important best practices for the hardware and networking of your database implementation.

     

    Use a Private Network Segment

    The traffic between the application servers and SQL Server and the traffic between SQL Server and the storage system is quite high. You should use a private network segment for application–SQL Server communication. This should be at least a 1-Gigabit Ethernet network, and a 10-Gigabit Ethernet network for very large installations. This can be in addition to a standard 100-Mb Ethernet connection to SQL Server for administration purposes.

     

    Enable Receive-Side Scaling

    You should enable Receive-Side Scaling (RSS) on the SQL Server network interface card (NIC) that is serving the application servers. This setting is found on the Advanced Property tab of the network card. Also, be sure that offloading options are enabled. See the Microsoft® Developer Network (MSDN®) articles Introduction to Receive-Side Scaling and Receive-Side Scaling Enhancements in Windows Server 2008 for more information. If your NIC does not support these options, consider replacing it with one that does.

    You should configure the maximum number of RSS processors by setting the MaxNumRssCpus registry key value to 8 on a computer system with 32 or more CPU cores. For computer systems with less than 32 cores, use the default setting.

    The RSS base CPU number (RssBaseCpu) is the CPU number of the first CPU that RSS can use. RSS cannot use the CPUs that are numbered below the base CPU number. You should set RssBaseCpu carefully so it does not overlap with the starting CPU.

    Lab testing has shown good results with setting both registry key values to 8 (on a computer system with more than 32 cores); this means that 8 RSS processors are used starting with core number 8 to process network traffic.

    Note: You should use the Windows RSS registry keys to configure these values instead of NIC settings because NIC settings can be overridden by the Windows registry keys.

     

    Consider NIC Teaming

    If you have three or more application servers, NIC teaming on the SQL Server side of the network segment may help, but often this type of NIC teaming disables RSS and other offloading options. Check with your vendor to select a NIC teaming option that can support RSS or provide equivalent features. You should be sure to test this configuration against your original performance to be sure that any benefit of NIC teaming is not offset by the disabling of any of the RSS and offloading options.

     

    Use Multiple HBAs and Set the HBA Queue to Depth

    You should use at least two host bus adapters (HBAs) to provide redundancy and to increase bandwidth between the SAN and SQL Server. The HBA cards should be set as load balanced and configured to provide high availability among them. Connections between the SAN and server are ideally 8 gigabyte (GB)/sec (but not less than 2 GB/sec).

    The HBA queue depth may need to be increased if you have a small number of LUNs. A queue depth value between 128 (if there are few LUNs) and 32 (if there are many LUNs) should be considered. Note that this is a new recommendation; queues now default to “per LUN” rather than “per target.” When the queue depth is set too low, you may get increasing latency and less-than-expected throughput given the bandwidth between host/storage and the number of disks in a particular configuration.

     

    Data, Log, tempdb, and Backup File Recommendations

    The location of SQL Server files affects performance. You should use multiple files for each filegroup supporting your databases, and use distinct LUNs for each of the data, tempdb, log, and backup files. Using separate LUNs also helps you monitor the disks based on the type of use. You should avoid using very large LUNs (several hundred GB or more) so that chkdsk does not run for an excessive length of time if it is invoked by the operating system on startup.

     

    Configure Data Files

    The filegroup used for the application data should be composed of multiple files. Best practice is to use one file for every two CPU cores on computer systems with 32 or more cores. On computer systems with less than 32 cores, use the same number of files as the number of CPU cores (the ratio should be 1:1). The data files should be equal in size. Note that the out-of-the-box configuration uses only one file in the primary filegroup, so you need to add additional files for optimal configuration.

    You should pre-allocate enough space in the data files based on the initial size of the computer system. You should monitor the database free space and if necessary extend each file simultaneously so that all of the files have the same amount of free space. SQL Server optimizes writes by spreading its write operations across the files based on the ratio of free space among the files, so extending all files at once maintains this optimization.

    You can leave the autogrowth setting on file set on as an insurance policy so that SQL Server does not stop when it runs out of space; however, do not rely on autogrowth to extend the database files as a standard way of operating. Doing so may cause uneven distribution of freespace among files, which can minimize the effectiveness of using multiple data files.

    If autogrowth is enabled, choose a reasonable, fixed size for the growth size increment. A size of 100 MB to 500 MB should be reasonable.

    To prevent blocking of application processes on autogrowth events, instant file allocation should be enabled. To enable instant file initialization, the SQL Server Service account should be granted the "Perform Volume Maintenance Tasks" security privilege. Instant file initialization is not available for transaction log files.

    For more information on Instant File Initialization, see Database File Initialization.

     

    Configure the Log File

    The transaction log file, generally a sequentially written file, must be written as quickly as possible—even before the data is written to the data files (the data portion can be rebuilt from the log if necessary). While there is no performance benefit from using more than one file, multiple files can be beneficial for maintenance purposes (for example, if you are running out of space on the log drive). Adding physical devices to support the LUN can benefit performance.

    To avoid autogrowth operations on the transaction log file, monitor its size on a regular basis and adjust it if necessary. Regular transaction log backups are necessary to prevent the log from becoming too large.

     

    Configure tempdb Files

    SQL Server tempdb files are used for the storage of temporary data structures. The tempdb files are responsible for managing temporary objects, row versioning, and online index rebuilds. The application uses a read-committed snapshot isolation level as its default isolation level, which uses row versioning. For more information, see Isolation Levels in the Database Engine.

    To ensure efficient tempdb operation:

      • Create one tempdb file per physical CPU core. This reduces page free space (PFS) contention.
      • Pre-size the tempdb files, and make the files equal in size. As a starting point, you can use a 64-GB total size.
      • Do not rely on autogrow (see previous section).

    For information on how to set startup settings for SQL Server, see the article Configure Server Startup Options (SQL Server Configuration Manager).

    For further information, see the MSDN article Optimizing tempdb Performance.

     

    Recommendations for Memory Settings

    Following are recommendations for the memory settings for SQL Server.

     

    SQL Server Memory Settings

    You should then configure the SQL Server "max server memory (MB)" setting by taking the amount of memory allocated to the database system and subtracting one GB for every four cores (round up). This leaves the operating system with enough memory to work efficiently without having to "grab" memory back from SQL Server. For example, if the server has 64 GB of RAM and 24 cores, set the maximum memory to 58 GB (64 GB minus 6 [24 cores divided by 4]).

     

    Lock Pages in Memory

    To reduce SQL Server paging, you can grant the SQL Server service account “Lock Pages in Memory” privilege through the Windows Group Policy editor. Enable this privilege for both 32-bit and 64-bit servers.

    For detailed instructions, see How to reduce paging of buffer pool memory in the 64-bit version of SQL Server on the Microsoft® Support site.

     

    Guidance for SQL Server Configuration Settings

    Following is guidance for SQL Server configuration settings.

     

    Keep Default Setting for Degree of Parallelism

    Change the max degree of parallel option to the lesser of 8, or the number of cores in a single NUMA node.

     

    Keep default Setting for Number of Worker Threads

    Leave the default setting of max worker threads option unchanged.

     

    Encrypt Client Communication If Required

    If required, you can enable encrypting client connection communication to SQL Server. SQL Server supports Secure Sockets Layer (SSL) to encrypt the data transmitted between a client and the database server. SQL Server can be configured to require encrypted connections, in which case it rejects connections from clients who are not able to support encryption. Clients can also request encryption when connecting to SQL Server.

    For more Information, see Encrypting Connections to SQL Server and How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).