Performance benchmarking – Dell POWEREDGE R720XD User Manual
Page 11
Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on
PowerEdge R720xd
11
SQL Server Configuration
The following startup options were added to the SQL Server Startup options:
o -E: This parameter increases the number of contiguous extends that are allocated to a
database table in each file as it grows. This improves sequential access.
o -T1117: This trace flag ensures the even growth of all files in a file group when auto
growth is enabled. It should be noted that the Fast Track reference guidelines
recommend that you pre-allocate the data file space rather than allow auto grow.
o -T834: This should be evaluated on a case-by-case basis. This can improve throughput
rates for many DW workloads. This flag enables large page allocations in memory for
the SQL Server buffer pool. However, at this time SQL Server 2012 does not support the
use of –T834 in the case that Column Store Indexes (CSI) are in use on the database. If
CSI use is planned, do not use this trace flag. For more information about this and
other trace flags, refer to
in the
Reference Section.
SQL Server Maximum Memory: For SQL Server 2012, FTDW 4.0 guidelines suggest allocating no
more than 92% of total server RAM to SQL Server. If additional applications will share the
server, the amount of RAM left available to the operating system should be adjusted
accordingly. For this reference architecture, the maximum server memory was set at 90112
MB, i.e., 88GB.
Resource Governor:
For SQL Server 2012, Resource Governor provides a maximum of 25% of
SQL Server memory resources to each session. The Resource Governor setting can be used to
reduce the maximum memory consumed per query. While it can be beneficial for many data
warehouse workloads to limit the amount of system resources available to an individual
session, this is best measured through analysis of concurrent query workloads. For the test
configuration, Resource Governor memory grant was set at 19%. For more information, refer to
in the Reference Section.
Max Degree of Parallelism: The SQL Server configuration option 'max degree of parallelism'
controls the number of processors used for the parallel execution of a query. For the test
configuration, the 'max degree of parallelism' was set at 12. For more information, refer to
in the Reference Section.
Performance Benchmarking
Microsoft Fast Track guidelines help to achieve optimized database architecture with balanced CPU and
storage bandwidth. The following sections describe the performance characterization activities carried
out for the validated Dell Microsoft Fast Track reference architecture.
Baseline Hardware Characterization using Synthetic I/O
The goal of hardware validation is to determine actual baseline performance characteristics of key
hardware components in the database stack. You must thoroughly analyze the storage hardware to
make sure that the backend storage is capable of delivering the maximum possible throughput. This
will ensure that the performance of the system is not bottlenecked in any of the intermediate layers.
The disk characterization tool, SQLIO, was used to validate the configuration. Please refer to the Fast
Track Reference Guide (link provided in the reference section) for detailed guidelines. Figure 7 and
Figure 8 show the baseline performance numbers achieved for the validated reference architecture.
The results in Figure 7 show the maximum baseline that the system can achieve from a cache called
Line Rate. A small file is placed on the storage, and large sequential reads are issued against it with