Quick Reference Guide

Dell Quickstart Data Warehouse Appliance 2000
Page 5
Windows Server 2008 R2 SP1
• Enable Lock Pages In Memory: This policy setting is enabled to prevent the system from
paging memory to disk. See Enable the Lock Pages in Memory Option (Windows) for more
information.
SQL Server 2012
• 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 to improve 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 pre-allocating data file space rather than allowing auto grow.
o -T834: This optional setting can improve throughput rates for many data warehouse
workloads by enabling 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 when
ColumnStore Indexes (CSI) are used on the database. For this reason, you should
evaluate your architecture before implementing this option. For more information
about this and other trace flags, see SQL Server Performance Tuning & Trace Flags.
• SQL Server Maximum Memory: For SQL Server 2012, Fast Track Reference 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. This appliance has the maximum server memory set at 119,808 MB.
• Resource Governor: The Resource Governor allocates a maximum of 25% of SQL Server
memory resources to each session to reduce the maximum memory consumed per query. While
it can be beneficial to limit the amount of system resources available to an individual session,
this is best measured through analysis of concurrent query workloads. For this appliance, the
Resource Governor memory grant was set at 25%. For more information, see Using the Resource
Governor.
Internal Storage Controller (PERC H710P Mini)
The Dell PERC H710P Mini is an enterprise-level RAID controller that provides disk management
capabilities, high-availability, and security features, in addition to improved performance of up to
6Gb/s throughput. Figure 2 shows the management console accessible through the BIOS utility.
By default, the PERC H710P Mini creates virtual disks with a segment size of 64KB. Dell tested stripe
element sizes of 64KB and 256KB to compare and contrast performance improvements, and found that
for most workloads, the 64KB default provides an adequate stripe element size. It is recommended to
test various stripe sizes depending on the workload characteristics of your configuration.
The default setting for the read policy is adaptive read ahead. Dell tested adaptive read ahead, No
read ahead, and Read Ahead settings, and observed that the default setting provides the best
performance.