Dell PowerEdge R720xd Reference Configuration
Contents
1. Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd This whitepaper describes the Dell Microsoft SQL Server Fast Track reference architecture configuration and performance details Database Solutions Engineering Anthony Fernandez Mayura Deshmukh May 2012 Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd This document is for informational purposes only and may contain typographical errors and technical inaccuracies The content is provided as is without express or implied warranties of any kind 2012 Delt inc All rights reserved Dell and its affiliates cannot be responsible for errors or omissions in typography or photography Dell the Dell logo and PowerEdge are trademarks of Dell Inc Intel and Xeon are registered trademarks of Intel Corporation in the U S and other countries Microsoft Windows and Windows Server are either trademarks or registered trademarks of Microsoft Corporation in the United States and or other countries Other trademarks and trade names may be used in this document to refer to either the entities claiming the marks and names or their products Dell disclaims proprietary interest in the marks and names of others May 2012 Rev 1 0 Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Contents Introduction 4 Microso2. 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 nat support the se 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 SQL Server Performance Tuning amp Trace Flags 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 Govemor provides a maximum of 25 of SQL Server memary 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 Using the Resource Governor
3. TAT State Optimal Physical Disks Disk 1D strip Etenent size emm perat ion Ho Operation Progress nen Time Lert en ead rottey ndantive n cite roricy trite tock 1 Parce Wil with wo battery za Fi cip F12 Ctir RAID configuration One of the most critical decisions to make when deploying a new storage solution is which RAID type s to use because that choice heavily impacts the performance of the application We have configured the proposed Fast Track configuration using RAID 1 disk groups for database data files and RAID 10 for database log files Nine RAID 1 data disk groups and one RAID 10 log disk group were created each with a single Virtual Disk Figure 5 shows the proposed RAID configuration Figure 5 RAID Configuration Rear Bay Drives Gear L2aase7res Logs Spares Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Drive Slot configuration Slot 0 17 Nine RAID 1 disk groups were created each configured with a single Virtual Disk dedicated for the primary user data Slot 18 21 One RAID 10 disk group was created of four disks and a single Virtual Disk dedicated to host the database log files Slot 22 23 The remaining two disks were assigned as global hot spares Slot 24 25 Rear Bay Drives One RAID 1 disk group for operating system and stage space For
4. a m pg pae Fast Track Database Validation The performance of a Fast Track database configuration is measured using two core metrics Maximum CPU Consumption Rate MCR and Benchmark Consumption Rate BCR Calculating MCR MCR indicates the per core I O throughput in MB or GB per second This is measured by executing a pre defined query against the data in the buffer cache and measuring the time taken to execute the query against the amount of data processed in MB or GB MCR value provides a baseline peak rate for performance comparison and design purposes For the validated configuration with one Intel E5 2643 four core processors the system aggregate MCR Was 1244 MB s The realized MCR value per core was 311 MB s Calculating BCR BCR is calculated in terms of total read bandwidth from the storage hard drives and not from the buffered cache as in the MCR calculation This is measured by running a set of standard queries specific to the data warehouse workload The queries range from 1 0 intensive to CPU and memory intensive and provide a reference to compare various configurations a Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd For the validated Fast Track configuration the aggregate BCR was 950 75 MB s During the evaluation cycle the system configuration was analyzed for multiple query variants simple average and complex with multiple sessions a
5. Fast Track architectures Dell recommends that you use mount point rather than drive letters for storage access It is also very important to assign the appropriate virtual disk and mount point names to the configuration in order to simplify troubleshooting and performance analysis Mount point names should be assigned in such a way that the logical file system reflects the underlying physical storage enclosure mapping Table 3 shows the virtual disk and mount point names used for the specific reference configuration and the appropriate storage layer mapping Al the logical volumes were mounted to the C FT folder Table 3 Mount Point Naming and the Storage Enclosure Mapping ri Datat CAFTPRNCaget Cardt vDatat 2 2 caget cardt vbataz Dataz CAFTPRNCaget Card1 vData2 a s caget Cardt vbata3 datas CAFTPRNCaget Cardt vData3 a a Caget Cardt vDatas Datas CAFTPRNCaget Cardt vData4 s s caget Cardt vDatas Datas CAFTPRNCaget Card1 vData5 6 e Caget cardt vbata6 Datas CAFTPRNCaget Cardt vDatab 7 7 caget Carat vbata7 Data7 CAFTPRNCaget Card1 vData7 s fa Datag CAFTPRNCaget Card1 vData8 9 9 Caget Cardt vData9 Data9 CAFTPRNCaget Cardt vData9 mo io caget cardt viog tog CAFTLOG Caget Cardt vLog Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Figure 6 represents the storage system configuration for the proposed
6. Fast Track reference Figure 6 Storage System Components oma e1 omatt 1 n Tan o oo amata erer E amates e AAD Dance o ABE cote mater a aA Gatien sostew e AAD Tana roe Ba The production staging and system temp databases were deployed per the recommendations provided in the Microsoft Fast Track Data Warehouse 4 0 Reference Guide Application Configuration The sections below explain the settings applied for the operating system and database layers Windows Server 2008 R2 SP1 Settings used for Windows 2008 R2 SP1 operating system Enable Lock Pages In Memory This Policy setting is enabled to prevent the system from paging memory to disk For more information refer to How to Enable the Lock Pages in Memory Option in the Reference Section 10 Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd 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 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
7. PD hamt ceri amt Properties 1 3 PERC H719P Mint Chus oxe2 Dev 0x00 a 1 1 Disk Group MAID 1 cr Virtual Disks 1 1b os 558 Physical Disks te Dedicated HS 1 te Free Cap 8 09 6B td Tree Areas 1 1 Disk Group 1 RAID 1 Figure 3 Detez 270 07 6B te 1 1 Disk Group er 3 t tel tl 1 1 Disk Group 3 RAID 1 Her H Pi help Fe Uperations Ta hetresh Ciri N Next rage Ctri f trev Paye Fl2 ctir Stripe element size By default the PERC H710P Mini creates virtual disks with a segment size of 64KB During the Fast Track validation testing Dell tested stripe element sizes of 64KB and 256KB to compare and contrast any performance improvements For most workloads the 64KB default size will provide an adequate stripe element size Dell recommends testing various stripe sizes depending on the workload characteristics of your configuration Read policy The default setting for the read policy on the PERC H710P Mini is adaptive read ahead Configuration was tested with adaptive read ahead No read ahead and Read Ahead settings During testing we observed that the default setting adaptive read ahead gave the best performance Dell SMB Referenc PowerEdge R720xd Figure4 Internal Storage Controller Settings PERC H710 Mini DIOS up nmt j Dasic Settings Microsoft SQL Server 2012 Fast Track Data Warehouse on RAID Levet namm a un Si2e 278 07 0 am oo rouc cr
8. 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 Maximum degree of parallelism configuration option 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 1 0 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 i
9. system the large scale queries involving Hash joins and sorting operations will benefit from SQL Server offloading operations from the Tempdb to Memory Selection of memory DIMMS will also play a critical role in the performance of the entire stack In our test configuration we have configured the database server with 96GB of RAM running at 1333 MHz speed To achieve 96GB of RAM on the PowerEdge R720xd server four BGB RDIMMS are placed on slots A1 A4 white connectors and four 16GB RDIMNS are placed on slots AS A8 black connectors See the figure below for memory slot Locations Figure 2 Memory Slot Locations cpu White Connector T7 DIMM population Black Connector 2 DIMM population Green Connecter 3 DIN population Socket Not Populated nnen Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Refer to the Microsoft Fast track 4 0 Reference Guide for detailed recommendations on system memory configuration Internal Storage Controller PERC H710P Mini Settings 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 af up to 6GB s throughput Figure 3 shows the management console accessible through the BIOS utility Virtual Disk Settings TENC H710r Mini BIUS Configuration Utility 4 000014 Tr
10. which B s includes reads from RAM Buffer cache Benchmark Scan Rate 745 Reflects physical 10 read from disk during Physical MB s benchmark FTDW Peak 1 0 MB s 1512 Maximum observed 10 rate FTDW Rated CSI MB s 1662 5 Represents potential throughput using Columnstore Index 4 Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Conclusion The Dell Microsoft Fast Track Data Warehouse architecture provides a uniquely well balanced data warehouse implementation solution By following the best practices at all the layers of the stack a balanced data warehouse environment can be achieved with a greater performance benefit than the traditional data warehouse systems The Dell Microsoft Fast Track Architecture provides the following benefits to customers Tested and validated configuration with proven methodology and performance behavior A balanced and optimized system at all levels of the stack by following the best practices of hardware and software components Avoidance of over provisioning of hardware resources High availability at all the levels of setup host switches and storage Help to avoid the pitfalls of improperly designed and configured systems Reduced future support costs by limiting solution re architect efforts because of scalability challenges 15 Dell SMB Reference Configuration for Microsoft SQL
11. Server 2012 Fast Track Data Warehouse on PowerEdge R720xd References Dell SQL Server Solutions www dell comsql Dell Services wor dell com services Dell Support worw dell com support OLTP and OLAP http datawarehouseduinfo OLTP vs OLAP html Microsoft Fast Track Data Warehouse and Configuration Guide Information wor microsoft com fasttrack nttp download microsoft com download B E 1 BE1AABB3 6 D8 4C3C AF91 44BABT3B1AF Fast Track Configuration Guide docx An Introduction to Fast Track Data Warehouse Architectures http msdn microsoft com en us library dd459146 aspx How to Enable the Lock Pages in Memory Option http go microsoft com fwlink Linkld 141863 SQL Server Performance Tuning amp Trace Flags hnttp support microsoft com kb 920093 Using the Resource Governor http msdn microsoft com en us library ee151608 aspx Maximum degree of parallelism configuration option support microsoft com kb 2023536 Power Edge R720xd Technical Guide http www support dell com support edocs systems per720 en index htm 16
12. X BGB DDR3 DINS 1333MHz Internal Hard Drives 22x 300GB 10K 2 5 SAS 18 Data 4 Logs 2x 600GB 10K 2 5 SAS 2 Hot Spares 2x 600GB 10K 2 5 SAS 2 drives OS Rear Bay Operating System Microsoft Windows 2008 R2 SP1 Enterprise Edition Database Software Microsoft SQL Server 2012 Enterprise Edition Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Dell PowerEdge R720xd Server The Dell PowerEdge R720xd server is a 2 socket 2U high capacity multi purpose rack server offering an excellent balance of intemal storage redundancy and value in a compact chassis The PowerEdge R720xd server was developed with a purposeful design energy optimized options and enterprise class manageability For more technical specifications of the R720xd Server refer to the Power Edge R720xd Technical Guide a link to which is provided in the References section of this document Processors The Microsoft Fast Track 4 0 Reference Guide describes how to achieve a balance between components such as storage memory and processors In order to balance the available internal storage and memory for the Dell PowerEdge R720xd a single Intel Xeon E5 2643 four core processor operating at 3 3GHz speed was used Memory For SQL 2012 reference architectures Microsoft recommends using 64GB to 128GB of memory for ane socket configuration With enough memory installed on the
13. a warehouse solutions differently As most data warehouse queries scan large volumes of data FTDW designs are optimized for sequential scans and reads These methodologies yield performance much better than that of traditional data warehousing systems Based on this Dell has developed a reference guide that helps customers implement FTDW on Delt hardware Dell Fast Track Data Warehouse Reference Architecture In order to optimize data warehouse stack component performance each layer must be properly tuned Table 1 lists the Proposed Reference architecture along with the assigned Solution IDs The following sections explain the tuning of selected hardware and software Table 1 Dell Fast Track Reference Architecture Solution Details Dell Fast Track 4 0 Configuration PowerEdge asentar 2209618 Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Hardware Component Architecture Redundant and robust tests have been conducted on Dell s PowerEdge servers to determine best practices and guidelines for building a balanced FTOW system Figure 1 Proposed Dell Fast Track Reference Architecture Table 2 Tested Dell Fast Track Reference Architecture Component Details Server PowerEdge R720xd cpu 1 Intel Xeon 5 2643 CPU 3 3GHz HT Enabled Number of sockets used 1 Total Number of CPU Cores 4 Memory 96GB RAM 4 X 16GB DDR3 DIMMs 1333NHZ 4
14. e analysis With today s never ending data growth and complexity it is becoming a tedious job for customers to balance capacity and performance within the data warehouse system Growing data volumes and loading challenges OLAP query complexity and number of users are causing response times to increase IT executives are looking for solutions that offer lower cost easier management and better performance There are many challenges in designing a database configuration for OLAP workloads One is ensuring an optimal balance of 1 0 storage memory and processing power Dell and Microsoft jointly developed guidelines and design principles to assist customers in designing and implementing a balanced configuration specifically for Microsoft SQL Server data warehouse workloads to achieve out of box scalable performance This white paper describes the architecture design principles needed to achieve a balanced configuration for the Dell PowerEdge R720xd server using the Microsoft Fast Track Data Warehouse 4 0 guidelines Microsoft SQL Server Fast Track Data Warehouse In order to overcome the limitations of traditional data warehouse systems Microsoft has developed a cost effective solution that optimally balances the hardware and software capabilities of the system It provides an easy to deploy data warehouse infrastructure by mainly focusing on storage tuning and database layout Fast Track Data Warehouse FTDW implements dat
15. ft SQL Server Fast Track Data Warehouse 4 Delt Fast Track Data Warehouse Reference Architecture aA Hardware Component Architecture 5 intemal Storage Controller PERC H710P Mini Settings iil Application Configuration Performance Benchmarking Baseline Hardware Characterization using Synthetic 1 0 Fast Track Database Validation Fast Track Database Validation with Column Store Index CSI Reference Architecture Performance Details Conclusion 15 References 16 Tables Table 1 Dell Fast Track Reference Architecture Solution Details Table 2 Tested Dell Fast Track Reference Architecture Component Details Table 3 Mount Point Naming and the Storage Enclosure Mapping i Table 4 Performance Metrics 4 Figures Figure 1 Proposed Dell Fast Track Reference Architecture Figure 2 Memory Slot Locations Figure 3 Virtual Disk Settings Figure 4 Internal Storage Controller Settings Figure 5 RAID Configuration Figure 6 Storage System Components ite Figure 7 SQLIO Line Rate Test from Cache Small File 2 Figure 8 SQLIO Real Rate Test from Disk Large File Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Introduction A data warehouse is a large store of data accumulated from a wide range of sources The stored data is analyzed for trend analysis business intelligence reporting and various types of predictiv
16. n Figure 7 shaw 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 Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd SQLUO This test verifies the maximum bandwidth available in the system to ensure no bottlenecks are within the data path Figure 7 SQLIO Line Rate Test from Cache Small File SAL server 2012 Enterprise Single Socket Intel four core Asgregate Synthetic I O rate 2700 M s H710P PERC Contraer Synthetic 1 0 rate 2700 MB s POWER EDGE R720 XD SQLServer 2012 Windows Server 2008 R2 SP1 Single RAID 1 Disk Group thetic 1 O rate 2669 MB s The second synthetic 1 0 test with SQLIO was performed with a large file to ensure reads are serviced rom the storage system hard drives instead of from cache Figure 8 shows the maximum real rate that the system is able to provide with sequential reads 2 Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Figure 8 SQLIO Real Rate Test from Disk Large File POWER EDGE R720 XD Windows Server 2008 R2 SP1 Sal server 2012 Enterprise Single Socket Intel four core Agregate Synthetic I O rate 2014 MB s Single RAID 1 Dsk Group paer Synthetic I O rate 379 8 m 710P PERC Controller F Synthetic YO rate 2014 MB s
17. nd different degrees of parallelism MAXDOP options to arrive at the optimal configuration Fast Track Database Validation with Column Store Index CSI SQL Server 2012 implements CSI technology as a nonclustered indexing option for pre existing tables FTDW for SQL Server 2012 system design and validation is based on non CSI benchmarks FTDW systems are designed to run effectively in the case that no columnar optimization is achieved for any given period of time Significant performance gains are often achieved when CSI query plans are active and this performance can be viewed as incremental to the basic system design After the test configuration was validated CSI was added and the 1 0 and the CPU saturation packages were run The configuration was analyzed for multiple query variants simple average and complex with multiple sessions degrees of parallelism MAXDOP set at 12 and Resource Governor memory grant set at 19 Reference Architecture Performance Details Table 4 shows the performance numbers reported for the recommended reference configuration Table 4 Performance Metrics FTOW Rated Data Warehouse 5 This capacity rating is based on upto capacity Capacity TB but adjusted to account for Fast Track Rated vo FTOW Rated 1 0 MB s 950 75 Core performance metric for validation Is the average of Physical and Logical 1 0 Benchmark Scan Rate Logical 1156 5 Reflects actual user query throughput
Download Pdf Manuals
Related Search