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 Dell 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.
Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Contents Introduction ............................................................................................................. 4 Microsoft SQL Server Fast Track Data Warehouse ................................................................ 4 Dell Fast Track Data Warehouse Reference Architecture ....................................................... 4 Hardware Component Architecture ..........
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 predictive 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.
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 FTDW system. Figure 1. Table 2.
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 internal 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.
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 of up to 6GB/s throughput.
Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Figure 4. Internal Storage Controller Settings 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.
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.
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 Fast Track reference. Figure 6.
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. o -T1117: This trace flag ensures the even growth of all files in a file group when auto growth is enabled.
Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd SQLIO. This test verifies the maximum bandwidth available in the system to ensure no bottlenecks are within the data path. Figure 7.
Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd Figure 8.
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 and different degrees of parallelism (MAXDOP) options to arrive at the optimal configuration.
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.
Dell SMB Reference Configuration for Microsoft SQL Server 2012 Fast Track Data Warehouse on PowerEdge R720xd References Dell SQL Server Solutions www.dell.com\sql Dell Services www.dell.com\services Dell Support www.dell.com\support OLTP and OLAP http://datawarehouse4u.info/OLTP-vs-OLAP.html Microsoft Fast Track Data Warehouse and Configuration Guide Information www.microsoft.com/fasttrack http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91448AB733B1AF/Fast_Track_Configuration_Guide.
