Archive Shuttle 11.5 - SQL Best Practices

CPU considerations

The power of a server is not necessarily determined by the CPU speed in terms of cycles per second. Factors such as the server architecture and number and type of processors and cores can provide a far greater benefit over increasing CPU speed.

Hyper-threading technology is said to provide up to 30% improvement in performance. These processors contain two architectural states on a single processor core, making each physical processor act as two logical processors. However, the two logical processors must share the execution resources of the processor core, so performance gains may not be attained and in some circumstances can even lead to degradation of performance.

Multi-core technology provides similar performance to a comparable multi-CPU server. These processors contain multiple complete processor cores, which act as complete physical processors. Each physical core has its own architectural state and its own execution resources, so the performance gains are reliable.

With the ever-increasing number of processor core combinations and high clock speeds, the traditional x86 Front Side Bus architecture can start to become a bottleneck beyond eight processor cores. A popular and cost-effective method of scaling up the x86 architecture is to use an architecture that supports non-uniform memory access (NUMA). Processors and memory are grouped into nodes that have high-speed local access. However, access to memory co-located with other processor nodes is slower. Therefore, the operating system (and potentially application software) needs to be NUMA-aware and optimized to make the best use of processors, cores, and their associated resources. Windows server and SQL Server support NUMA.

The recommended number of processor cores can be composed of either physical CPUs or similar combination of multi-core CPUs, but the sizing should not be based on hyper-threaded logical cores.

In most cases, the SQL Server instance should manage the CPU resources. Do not set the CPU affinity mask unless absolutely necessary, as this can significantly impact the performance. When running multiple SQL Server instances, the most common reason for setting the CPU affinity mask is to prevent an instance being starved of resources.

Memory considerations

The recommended memory should be available at each SQL Server instance to ensure the data manipulation does not cause excessive paging to disk both in the Archive Shuttle databases and tempdb, which will quickly degrade performance.

Install the appropriate edition of Windows Server and SQL Server to support the capacity of memory that is installed. See the Archive Shuttle compatibility charts for supported versions of SQL Server.

Under normal circumstances, SQL Server should be allowed to manage memory dynamically. It may be necessary to change the SQL Server minimum and maximum memory to ensure the memory is used appropriately between SQL Server instances, Reporting services or other co-located services.

Network considerations

Archive Shuttle recommends that you connect the Archive Shuttle SQL servers and Archive Shuttle servers via gigabit network technology. The SQL servers may require multiple network interface cards to support the anticipated loads.

Archive Shuttle also recommends that you disable the TCP Chimney Offload, TCP/IP Offload Engine (TOE) or TCP Segmentation Offload (TSO) to prevent network issues. For guidance in disabling these, see this Veritas technical article.

Minimum Requirements

Recommended Requirements

Storage considerations