Dell PowerEdge External Media System 1434 Configuration

Also see for PowerEdge R720xd: Technical guide Technical guide Technical guide Getting started guide Owner's manual

Contents

Dell PowerEdge External Media System 1434 Configuration Manual pdf 11 page image

Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse onPowerEdge R720xd11SQL 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 adatabase 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 autogrowth is enabled. It should be noted that the Fast Track reference guidelinesrecommend 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 throughputrates for many DW workloads. This flag enables large page allocations in memory forthe SQL Server buffer pool. However, at this time SQL Server 2012 does not support theuse of –T834 in the case that Column Store Indexes (CSI) are in use on the database. IfCSI use is planned, do not use this trace flag. For more information about this andother trace flags, refer to SQL Server Performance Tuning & Trace Flags in theReference Section. SQL Server Maximum Memory: For SQL Server 2012, FTDW 4.0 guidelines suggest allocating nomore than 92% of total server RAM to SQL Server. If additional applications will share theserver, the amount of RAM left available to the operating system should be adjustedaccordingly. For this reference architecture, the maximum server memory was set at 90112MB, i.e., 88GB. Resource Governor: For SQL Server 2012, Resource Governor provides a maximum of 25% ofSQL Server memory resources to each session. The Resource Governor setting can be used toreduce the maximum memory consumed per query. While it can be beneficial for many datawarehouse workloads to limit the amount of system resources available to an individualsession, this is best measured through analysis of concurrent query workloads. For the testconfiguration, Resource Governor memory grant was set at 19%. For more information, refer toUsing the Resource Governor 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 testconfiguration, the 'max degree of parallelism' was set at 12. For more information, refer toMaximum degree of parallelism configuration option in the Reference Section.Performance BenchmarkingMicrosoft Fast Track guidelines help to achieve optimized database architecture with balanced CPU andstorage bandwidth. The following sections describe the performance characterization activities carriedout for the validated Dell Microsoft Fast Track reference architecture.Baseline Hardware Characterization using Synthetic I/OThe goal of hardware validation is to determine actual baseline performance characteristics of keyhardware components in the database stack. You must thoroughly analyze the storage hardware tomake sure that the backend storage is capable of delivering the maximum possible throughput. Thiswill 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 FastTrack Reference Guide (link provided in the reference section) for detailed guidelines. Figure 7 andFigure 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 calledLine Rate. A small file is placed on the storage, and large sequential reads are issued against it with