An Introduction to Fast Track Data Warehouse Architectures

SQL Server Technical Article

Writer: Erik Veerman, Solid Quality Mentors

Technical Reviewer: Mark Theissen, Scotty Moran, Val Fontama

Published: February 2009

Applies to: SQL Server 2008

Summary: This paper provides an overview and guide to SQL Server® Fast Track Data Warehouse, a new set of reference architectures created for scale-up (SMP) SQL Server based data warehouse solutions. It includes a summary of the resources available in the reference configuration, the distinguishing features of the approach, and the steps necessary to take full advantage of the new architectures.

<span id="_Toc222293502"><span id="_Toc221356403">

The performance and stability of any application solution—whether line of business, transactional, or business intelligence (BI)—hinges on the integration between solution design and hardware platform. Choosing the appropriate solution architecture—especially for BI solutions—requires balancing the application’s intended purpose and expected use with the hardware platform’s components. Poor planning, bad design, and misconfigured or improperly sized hardware often lead to ongoing, unnecessary spending and, even worse, unsuccessful projects.

This paper is a companion resource for Microsoft’s new SQL Server Fast Track Data Warehouse reference architectures , which provide tested, pre-configured architectures and architectural guidance for a BI solution’s database components and hardware systems. The Fast Track reference configuration resources provide planning tools, architectural decision guidance, best practices for design and tuning, specific hardware configurations from Dell and HP, and the tools needed to create a new reference configuration on any hardware platform.

The ultimate goal of the Fast Track reference configurations is to take the guesswork out of hardware and architectural decisions for the database layer of Microsoft SQL Server-based BI solutions. To help you get started with the Fast Track reference architectures, understand their approach and value, and use them to implement the best possible solution, this paper includes:

  • An overview of the new Fast Track reference architectures
  • A review of BI fundamentals and applicable hardware considerations
  • The tested Fast Track reference architecture components and options
  • Prescriptive guidance for designing and optimizing a solution
  • Resources available to help you choose or create a new hardware configuration

The Fast Track Data Warehouse reference configurations focus on the central database component of a BI solution, commonly called the data warehouse or data mart. These Fast Track reference configurations target solutions on a single server estimated at up to 32TB of data.

The intended audience for this paper includes IT executives and managers, solution architects, IT infrastructure planners, and project managers. If you are responsible for the ownership, management, planning, or design of a BI solution, this paper will give you the skills and resources you need to make the best decisions for your solution.

<span id="_Toc222293503">

If you’ve faced a performance or scalability problem with an existing application or during the testing phase of a new solution, you’ve probably heard the question, “Why don’t we just buy a bigger server…or get faster hard drives…or add more memory?”

Throwing hardware at a problem rarely leads to cost savings or long-term success. Instead of addressing the core problem, reflexively going with “bigger iron” without comprehensive investigation and planning is merely putting a Band-Aid on a deeper issue.

So when planning for performance in a BI solution or responding to a performance problem, what is the right answer? Of course, that depends on the type of solution, its intended use, and the actual or anticipated usage load. Although each application has its unique footprint of activity, most applications fall into different general categories of use. Common types of applications have patterns of activity that you can quantify; you can then architect and configure specifically for that type of activity. 

For example, a BI solution’s activity is very different from that for a transactional solution, such as an ERP system. A BI system focuses on responding to queries that summarize, filter, and pivot large sets of data. Transactional systems, on the other hand, manage the detail records of activity or events and, therefore, deal with mostly smaller sets of data—typically individual records.

The most common mistake in designing a BI solution is configuring the hardware and architecture as if it were a transactional system. The Fast Track reference configurations seek to help you avoid that mistake by providing the architectural guidance and hardware design patterns that enable you to focus on the real purpose of the BI system: adding value to the business.

The Fast Track reference configurations come with three main resources:

  • Information background and planning tools. This paper, Introduction to Fast Track Data Warehouse Architectures, provides the overall background and value to take advantage of the Fast Track reference architectures. In addition, a second, more detailed document, Implementing a SQL Server Fast Track Data Warehouse,clarifies and details the approach as well as documenting the pre-configured hardware architectures.
  • Hardware reference configuration choices. The five detailed reference configurations are suited for data warehouse workloads (sequential data access). Built around the Dell and HP platforms, these Fast Track reference configurations balance all the system components—server and CPU configurations, storage designs, I/O channel configurations—optimized for BI workloads.  You can also use the provided Microsoft Excel configuration spreadsheet, called the SMP Reference Calculator, to help you create a new hardware reference architecture based configuration using different hardware components.
  • Prescriptive guidance for optimization and maintenance. The detailed reference architecture paper, Implementing a SQL Server Fast Track Data Warehouse, provides detailed guidance about optimizing a data warehouse through indexing, partitioning, and data-loading best practices as well as through ongoing maintenance.

The rest of this paper outlines the value of the Fast Track Data Warehouse reference architectures, their focus, available tools, and how to take full advantage of the reference configurations to build the best BI solution for your organization.

The purpose of the Fast Track reference configurations is to provide guidance and resources to help you identify the right architecture and configuration for your BI solution. These best practices and direction will yield a return on investment (ROI) that has tangible savings and reduced frustration, including:

  • Accelerated data warehouse projects with pre-tested hardware configurations
  • Reduced hardware and maintenance costs as a result of purchasing the right balanced hardware solution and optimizing it for a BI workload.
  • Reduced planning and setup costs when either leveraging the Fast Track reference architecture vendor hardware lists or using the tools to identify a new hardware configuration.
  • Increased success factor as a result of performance gains through choosing the right scale model, configuring the system correctly, and taking advantage of the tuning guidance.
  • Reduced future support costs by limiting solution re-architect efforts because of scalability challenges.

Perhaps the most important benefit of using the Fast Track reference architectures is avoiding the pitfalls of an improperly designed and configured system. Misconfigured hardware will limit the capability and throughput of a system. And users will experience delays or timeouts when trying to gather the analytic output from the system, which will directly impact their adoption of the solution.

The Fast Track reference configurations address the need to design solution architectures correctly for BI systems. More specifically, these Fast Track reference configurations target the database tier of a BI solution, providing the tools and architectural direction customers need when using SQL Server for a data warehouse that will support BI-focused queries.

Figure 1: Fast Track reference architectures focus on the database tier of a BI solution

Figure 1 illustrates an overall BI architecture, which begins with the extraction process from source systems. The data is staged and then consolidated into a data warehouse environment.  BI solutions often use cubes for aggregations and analytics which is sourced from the data warehouse or data mart. The presentation layer exposes the data warehouse and cube data through reporting, analytic, and dashboard tools that sometimes are hosted in a Web portal. As highlighted in Figure 1, the Fast Track reference configurations focus on the data warehouse or data mart tier of a BI solution. The next section of this white paper, “BI Architecture and Data Warehousing Design,” elaborates on the design of the data warehouse.

In addition to focusing on the data warehouse tier of a BI solution, the Fast Track reference configurations are also intended for a scale-up hardware model of the warehouse. These scale-up systems are sometimes called Shared-memory or Symmetric Multi Processing (SMP) servers. These systems use processors, often multi-core, and memory contained in the same server hardware.

In a Massive Parallel Processing (MPP) system, multiple servers participate in a scale-out architecture in which each server can participate in resolving the same query.

With the latest advances in 64-bit and multi-core processors, a single SMP system with a correctly configured storage solution can now scale to handle large applications such as data warehouses with up to 32TB of data. Future advances in CPU improvements, faster and denser memory, increased drive performance and capacity will increase this capability even more.

Whether an SMP-based BI solution is 500GB or 32TB, the system still needs to be optimized to handle the concurrency, volume, and complexity of a data warehouse workload. The Fast Track reference configurations provide that guidance for choosing the right system and then configuring it for the right workload.

The seven main Fast Track reference architecture hardware profiles demonstrate one of the outputs of this architectural-guidance effort. But in and of themselves, the hardware profiles offer little value without the guidance that goes along with them. You still need to determine which system to choose and how to configure it.

In fact, you can use the main hardware configurations and provided resources as a framework for developing a custom reference architecture based on your particular needs. You might need a configuration for a different hardware platform, for example, or your data warehouse could have a unique requirement that you must consider as you choose the right hardware configuration. The resources and process included with the Fast Track reference configurations allow that flexibility, providing the background, guidance, and steps to build your own. (See the section “Building Your Own Hardware Configuration,” later in this paper, for details about creating a custom reference architecture.)

In general, the process of selecting the appropriate Fast Track reference architecture begins with understanding the expected query use of your system. Figure 2 shows the workflow process you would go through to take advantage of the Fast Track reference architecture resources and recommendations.

Figure 2: The Fast Track reference architecture process walks you through the evaluation, purchasing, configuration, and optimization of your solution.

The overall process that Figure 2 illustrates requires some inputs, the calculation of system throughput, and then the selection, configuration and optimization of the hardware and solution. The list below provides an overview of the process, refer to the “Choosing and Implementing the Right Fast Track Architecture” portion of this documentation for the details.

  1. The determination process for the Fast Track reference configurations involves primarily estimating the query activity of the data warehouse to help determine throughput needs. This includes estimate the types of queries, the estimated amount of data that is scanned per type of query, and the expected concurrency.
  2. If you plan to use different hardware components than the tested Fast Track reference configurations provided, then you will need to perform this step.  It involves testing the CPU core throughput of the server and testing the Storage Systems capabilities.  The Fast Track resources include a Microsoft Excel spreadsheet, called the SMP Reference Calculator, which is the building block to creating a new reference architecture platform.
  3. The identification of the right hardware reference configuration is primarily based on estimating the number of CPU cores that data warehouse needs to meet the query objectives.  Although you might think that the estimating the data storage volume is a primary factor, because of the balanced nature of the Fast Track architecture, the configurations begin with the CPU core requirements.
  4. You can either choose one of the existing hardware configurations or use a new platform that you have tested.  The tested Fast Track reference configurations vary on the number CPU cores, achievable throughput, and storage array types.
  5. The Fast Track reference architecture recommendations include very specific configurations for the storage array, including how the storage is carved up for user and system databases in SQL Server.  This configuration is a crucial component of the Fast Track reference configurations because it focuses on data warehouse sequential data access workloads.
  6. The final step is to optimize the data warehouse solution for partitioning, indexing, and data staging. The types of queries typically executed against a data warehouse require tuning the tables to handle query requests that target large ranges of data for the purpose of summarization.

As noted earlier, the Fast Track reference architectures include the hardware configuration, the tools to identify the right system, and best practices for configuring the software and database.

Before you can selecta sample Fast Track reference architecture or build your own, you first need to identify estimates for the solution, including the expected user count, the complexity of the queries, the estimated data scanned per query type, the concurrency, and the data volume. With these estimates, you can leverage provided calculations to determine the number of CPU cores and the system throughput needed.

If you decide to build your own hardware reference configuration, you’ll need to determine the CPU Core Consumption on the hardware that you choose. The consumption rate is the driving metric for determining the maximum I/O saturation point of the system and is used to identify the correct storage array and how to best configure it.  You will also need to determine how much throughput the Storage Array and I/O channel can handle.  See the “Building Your Own Hardware Configuration” section for the detailed steps to build your own Fast Track reference architecture.

Pre-configured and Tested SMP Hardware

For the hardware architecture, you can use any server hardware and storage system. The reference configurations include all the calculations necessary to model the SMP architectures on any hardware platform. However, as examples and models, Fast Track reference configurations are provided based on two hardware vendors: Dell and HP with other vendors coming soon.

Along with the general system summaries, each sample Fast Track reference architecture includes the following details:

  • A configuration workbook that contains all the system specifications for each version of the Fast Track reference architecture. This includes SKUs, quantities, descriptions, and pricing to simplify purchasing steps.
  • System diagrams that include the server, storage switch, storage processors, storage arrays, and wiring between the components. The diagrams even provide the system footprint for server room planning.

See the section “Available SQL Server Fast Track Architectures” for more details about the reference configurations.

The final aspect of the configuration involves prescriptive guidance for optimizing the solution through system and server settings and database tuning. This guidance applies to any hardware platform using SQL Server as the database engine for a data warehouse.

The guidance includes:

  • Windows OS memory settings and SQL Server startup and data-recovery settings
  • Database file and log initialization and physical layout of user and system databases on the storage array
  • Database table partitioning, indexing, and data loading best practices
  • Database table fragmentation and statistics maintenance

These best practices relate to multiple system tiers, including the initial setup of files and tables, the extraction, transformation, and loading (ETL) of data, and ongoing maintenance of the system over time to ensure performance. The “Choosing and Implementing the Right Fast Track Architecture” section further clarifies the overall optimization approach and points to the resources available in the Fast Track reference architecture to assist in the process.

Now, let’s look at a summary of the role of the data warehouse in an overall BI solution and best practices for its design.

<span id="_Toc222293511"><span id="_Toc221356412">

A data warehouse solution is most often associated with a larger BI strategy. As you plan to leverage the Fast Track architectures, it’s important to understand the big-picture architecture related to systems that participate in a BI strategy. This section reviews the overall role of the data warehouse in a corporate environment and the database design best practices for the environment.

Overview

Figure 1 above highlighted the typical solution architecture of a BI environment, which includes ETL processes, the temporary storage of data in a staging environment, the data warehouse or data mart, the cubes, and the presentation layer reports, dashboards, and analytic views.

For smaller solutions, some of these components can be co-located on the same physical machine. However, a better architecture choice is to distribute the components across separate servers.

Figure 3 highlights the separate physical servers that participate in a BI environment.

Figure 3: The hardware infrastructure of a corporate BI environment includes several systems that perform various roles.

This environment represents a scale-up SMP model for the data warehouse in which the data warehouse is centralized on a single server. The Fast Track reference configurations target the data warehouse server and storage components of an SMP-based architecture.

The difference between a data warehouse and a data mart is that of data scope. A data warehouse centralizes data across a corporation. In contrast, a data mart handles data from a single department or a single subject area. Data marts are often built off of the centralized data warehouse or sourced from a corporate Operational Data Store (ODS). Many enterprise environments have multiple data marts. You can also have a data mart without a data warehouse, and data warehouses often grow out of data marts.

You can apply the Fast Track reference configurations to either a data warehouse or a data mart. Although data marts are often smaller than a data warehouse, they can be multiple terabytes in size themselves and need the same tuning and architectural attention that you would give a full data warehouse.

Design

The term data warehouse is often used generically to refer to a data repository that houses historical business data. Although this is true, it doesn’t capture the design practices that should be used within a data warehouse environment. The most common mistake is to categorize the following solutions as data warehouses:

  • A replicated or otherwise offline copy of a transactional system is not a data warehouse. These database copies do not support the need for historical tracking or efficient reporting. Reporting off of a system designed for transactional processing runs into complexities and performance challenges, and often don’t store data history.
  • A database that contains all flattened, denormalized tables with lots of text descriptors and numeric columns mixed together is not a data warehouse. Such a system runs into performance issues immediately when the tables are queried and the volume of records combined with the amount of space that the tables take up become overwhelming.  Of course, there is a range of design patterns for data warehouses, which include some variations on data normalization practices. But the goal is the same: To optimize data reporting and analytics while tracking the history of data over time.

Data warehouses are frequently designed using a modeling technique called dimensional modeling, which effectively handles the performance and historical goal. Dimensional modeling focuses on optimizing the structures for reporting by creating some redundancy in the text columns (called attributes) and then separating those from the numeric metrics analyzed.

Dimensional modeling uses two primary types of tables: dimension tables and fact tables.

  • A dimension table is a grouping of related attributes that focus on a business entity, such as products, stores, invoices, or dates. Changes in the dimension data are sometimes handled by persisting the history and including a new primary key column called a surrogate key. Dimensions tables often have many columns, but the record count is usually much smaller relative to the second table type, the fact table.
  • A fact table contains the metrics being analyzed, which are called measures or facts. To relate to the attributes, the fact tables also contain the foreign keys of the related dimension tables (which are the surrogate keys). Fact tables are organized by the type of measurement or transaction, such as a sale, account balance, or event. Fact tables often have millions or billions of rows but are optimal for reporting because they do not contain inefficient text columns.

Figure 4 shows an example of two fact tables: Store Sales and Store Inventory. These fact tables, centered in the diagram, share common dimension relationships such as store, date, and buyer. Because of the conformity of the dimensions, this design allows the sales transactions and inventory quantities to be analyzed and queried together.

Figure 4: Dimensional model example of two fact tables and their related dimension tables

Like dimension tables, fact tables also track history, such as the history of sales, inventory quantities, account balances, or events. For example, a store inventory fact table could track the weekly history of products quantities in stock at every store. With this information, users can perform trending and analysis of inventory levels over time—a capability rarely available in a source transactional system.

The Fast Track Data Warehouse reference architecture recommendations for indexing and partitioning, outlined in the “Choosing and Implementing the Right Fast Track Architecture” section, focus on tables that are representative of a data warehouse design.

<span id="_Toc222293514"><span id="_Toc221356418">

The approach Microsoft used for creating its SMP based Fast Track reference configurations for data warehouses can be applied to any hardware platform. “Building Your Own Hardware Configuration,” later in this paper, outlines the steps for creating your own Fast Track reference architecture if you have existing hardware you need to repurpose or your preferred hardware platform isn’t part of this first phase of tested Fast Track reference architectures.

However, if you’re building a new data warehouse or data mart BI solution or have latitude in selecting a new system, the existing Fast Track reference configurations give you the important advantage of extensive testing to balance the hardware components.

Let’s explore the distinguishing features of these new Fast Track Data Warehouse architectures, and then review the details of each preconfigured solution so that you can select the right hardware configuration for your solution.

SQL Server Fast Track Data Warehouse Architectures

A quick Web search of “reference architectures” reveals thousands of hits representing hundreds of system design patterns that target various domains. It’s easy to get lost. If you’re unsure of where to look, what to look for, or even who to trust, it’s also easy to be overwhelmed.

What makes the Fast Track Data Warehouse Architectures unique? There are two main distinguishing features.

 Tuned for Data Warehouse Query Workloads

The first distinguishing factor of these Fast Track reference configurations is their specific focus on the data warehouse footprint of activity, sequential I/O.

Just because a system is built with SQL Server as the engine doesn’t mean that the system should always be configured the same way. For example, if you have a large ERP system such as Microsoft Dynamics or SAP that runs on SQL Server, the purpose of the system is to perform the transactional tasks of record handling. This type of system manages small transactions and performs discrete inserts, updates, or deletes—often hundreds or thousands at a time. Thus, you need to optimize the indexing and storage configurations to handle these random I/O reads and writes.

Data warehouse activity is vastly different. First of all, during user query time, the activity is almost exclusively read activity, with limited writes. You can further classify data warehouse activity as sequential I/O with large bulk operations reading lots of data from the database all at once in order to respond to a reporting or analytic query that summarizes thousands or millions of data rows.

Here are some implementation details that the SQL Server Fast Track Data Warehouse reference configurations follow:

  • Given the sequential nature of the I/O requests, the Fast Track reference configurations configure the storage array to optimize reading of data off the underlying drives, with the assumption that the data is not randomly pulled from the drives but read from in contiguous chunks of data. If the data is not fragmented and the queries are scanning ranges, then throughput is a lot higher with this configuration.
  • The sequential I/O pattern also means that correctly configured drives can greatly reduce the latency of the reads. This leads to less drive striping and fewer dedicated hardware volumes, called Logical Unit Numbers (LUNs). In other words, when a drive head needle has to bounce around on the disk to answer the requests, the time it takes to read a block of I/O (latency) is much higher. If the drive needle can be constantly reading without having to move around and wait for a disk rotation (which sharing LUNs across volumes and some data striping will cause), the drives can be focused and optimized.
  • Because of the read nature of typical data warehouse queries, these Fast Track reference configurations also take advantage of the storage arrays’ dual reading capabilities. The EMC CX4-240 and the HP MSA-2000 can read from both drives in a mirror separately, which means the Fast Track reference architecture solutions rely on mirrored drives where the data is stored on two drives in case of a failure. One advantage these technologies provide is that they can answer I/O requests from both drives in a mirror without overlap. This capability almost doubles the I/O throughput of the LUNs.

Configuring the storage system correctly is not the only factor involved in setting up a data warehouse system. However, it’s the area most often misconfigured and where you can achieve dramatic cost savings and performance.

Designed with a Balanced Hardware Approach

The second distinguishing factor in the new Fast Track reference configurations is the balanced hardware approach. Most system architectures consider the memory, data storage capacity, and processors separately when planning a system.

The Fast Track reference configurations instead consider that the overall system needs to be balanced so that throughput is matched across the tiers of the hardware and software components. Between the CPU and the drives themselves, there are several tiers of throughput:

  1. The CPU and server throughput, which includes the CPU cores, OS capabilities, and SQL Server Read Ahead Cache.
  2. The I/O channel throughput between the server and the storage system, which includes the combined throughput of the Host Bus Adapter (HBA) cards and switch capabilities.
  3. The Storage System Throughput, which includes the Storage Processor throughput capacity and the LUN and disk capabilities for sequential I/O reads (given the data warehouse focus).

Any one of these components can become a constraint that keeps the other components from achieving max throughput capacity. Figure 5 illustrates the nature of the throughput capacities.

Figure 5: Examples of misconfigured systems with throughput constraints

The most common bottleneck imposed on a system is the storage array. Example 1 in Figure 5 shows a system where although the server and throughput capabilities of the I/O path can scale, the system is limited by the storage subsystem. This problem occurs when the drives aren’t optimized for data warehouse workloads, the number and speed of drives isn’t sufficient, or the storage processor’s throughput is limited.

Example 2 highlights a system where the capabilities of the HBAs or I/O switch are limited either because there aren’t enough HBA cards or the throughput of the cards or switch (if there is one) is limited. You sometimes see this bottleneck in warehouse solutions that use direct-attached storage. Because LUNs are mapped directly from the drive arrays to the server, the I/O cannot be balanced across multiple cards. Even with multiple HBAs, one HBA can be the bottleneck.

Example 3 has sufficient throughput of the storage system and I/O path to the storage, but the server is a bottleneck. This situation can be a result of software that can’t handle the throughput, or more likely, limited combined I/O consumption capacity of the CPU cores.

In contrast to these examples, the new Fast Track reference configurations balance the hardware from the CPU through to the disk array. This approach allows all system components to achieve their maximum throughput capacities. Figure 6 illustrates the benefit of balancing the system components.

Figure 6: A balanced system achieves more efficient throughput

The uniqueness of the approach that Figure 6 shows is that the balanced nature of the system begins with the CPU cores. Thus, this system architecture is called the Core-Balanced Architecture. This balanced approach begins with what is called the CPU core consumption rate, which is the input capacity that each CPU core can handle as data is fed to it.

The tested Fast Track reference configurations are built on three HP servers and two Dell servers. The Dell configurations use the EMC CX4-240 and the HP configurations use both the EMC CX4-240 and HP MSA-2000 storage arrays.

Table 1 summarizes the server configurations.

Server CPU Cores SAN Drive Count Capacity

HP Proliant

DL 385 G5p

(2) AMD Opteron Shanghai

quad core
2.7 GHz

8

(2) HP

MSA2000

(16) 300GB

15k SAS

4TB (tested)

8TB (max)

(2) EMC CX4-240

(16) 300GB

15k FC

4TB (tested)

10 TB (max)

HP Proliant

DL 585 G5

(4) AMD Opteron Shanghai

quad core
2.7 GHz

16

(4) HP MSA2000

(32) 300GB

15k SAS

8TB (tested)

16TB (max)

(4) EMC CX4-240

(32) 300GB

15k FC

8TB (tested)

16TB (max)

HP Proliant

DL 785 G5

(8) AMD Opteron Shanghai

quad core
2.7 GHz

32

(8) HP MSA2000

(64) 300GB

15k SAS

16TB (tested)

32TB (max)

(8) EMC CX4-240

(64) 300GB

15k FC

16TB (tested)

32TB (max)

Dell Power Edge 2950 MLK

(2) Intel Xeon

Harpertown

quad core 2.66 GHz

8

(2) EMC CX4-240

(16) 300GB

15k FC

4TB (tested)

8TB (max)

Dell Power Edge R900

(4) Intel Xeon Dunnington

six core 2.67GHz

24

(6) EMC

CX4-240

(48) 300GB

15k FC

12TB (tested)

24TB (max)

Table 1: Tested HP and Dell Fast Track Configurations

Because the reference configurations use a core-balanced approach, the actual storage estimate for the system isn’t the central focus of the configurations. The total number of cores has a correlation to the system capacity, but only because of the required storage system throughput based on the combined CPU consumption rate of all the cores. The optimal storage throughput was achieved with the 300GB 15K RPM drives.

Note the following clarifications on the Capacity values that Table 1 specifies:

  • The tested Capacity value represents a balanced number of drives to match the consumption rate of the CPU cores. The system’s throughput rate is optimized with this configuration, so you should not reduce the number of drives even if you have a smaller storage volume need. For example, if your data warehouse is only 1TB and the tested capacity of the system is 4TB, there should be 3TB of storage left for other purposes such as staging or for future growth. It is important to maintain the number of drives and their ratio/affinity to the CPU cores in order to maintain the core-balanced nature of the overall system to maintain the throughput.
  • The maximum Capacity value represents the maximum number of drives that that the storage array can hold given the different storage array capacities of the array cabinets used by HP or EMC. Adding more drives beyond the tested capacity will increase stored data capacity but will not increase system throughput. This is because the system is already optimized with the core-balanced approach.
  • The Capacity numbers do not include the hot spare and log drives and are not the raw space before the drive mirroring is applied.  The numbers therefore represent the available space for user databases.  Furthermore, the capacity numbers shown reflect the use of compression on user data at 2.5x compression factor based on SQL Server 2008 compression averages.

As you can see in Table 1, each Fast Track reference architecture includes the specific server hardware, a specified processor type and core count, the precise SAN hardware, and the number and type of drives. You can make modifications to this hardware configuration recommendation, but you would need to test the modified system to confirm that balanced throughput is still achieved. For example, changing the type of drive to a 400GB 10K RPM drive will reduce the throughput capacity, the system will no longer be balanced, and an I/O bottleneck will occur in the storage system.

The assumption on server memory is that the system contains a minimum of 4GB per CPU core or 64GB of RAM for smaller systems but, more adequately, either 128GB or 265GB for mid-sized or larger systems.  The data warehouse solutions that these configurations target are between 1TB and 32TB, so the more memory the better—SQL Server will take advantage of the server’s memory for data caching.

<span id="_Toc222293519">

The next step is to determine the right Fast Track reference architecture configuration for your solution. You’ll need to evaluate your solution needs and either choose one of the tested Fast Track reference configurations or build a new Fast Track reference architecture based on different hardware.

This section begins by walking through the steps to create a new Fast Track reference architecture. If you’re using one of the tested Fast Track reference architectures, you can skip to the subsection called “Calculating the Number of CPU Cores and Choosing the Hardware Solution.”

All the steps involved in creating a new SMP hardware configuration focus on testing the throughput capacity of the system and components, starting with the CPU core consumption rate. 

If you’re using one of the pretested HP or Dell servers with the same processors but are changing the storage system or drives, you don’t need to calculate the core consumption rate. That has already been calculated at about 200 MB/s. But you do need to determine the throughput capabilities of the storage array.

Conversely, if you’re using one of the tested Fast Track storage array and drives, you need to calculate the core consumption rate of your server and cores. The core consumption rate of a specific CPU might be different from one server to the next, so be sure to calculate it on the system you plan to use. Before you begin, contact the server vendor for assistance or ask if a new Fast Track reference architecture has been tested and published.

Calculating the Core Consumption Rate

Because the CPU core consumption rate is specific to the server and independent of the storage system, the goal is to use a test that loads as much data as possible into one of the CPU cores without causing disk activity.

The “How to Determine the CPU Consumption Rate” section of the Implementing a SQL Server Fast Track Data Warehouse paper provides a sample set of steps for calculating the rate, which include:

1. Create a database that has two tables, one with a clustered index and the other without a clustered index.

2. Run a set of SELECT * queries against the tables (targeted to execute on a single CPU core with the MAXDOP = 1 connection setting) to determine the time it takes to scan the tables.

3. The CPU max consumption rate is calculated as the size of the table in MB divided by the number of seconds it took to scan the table.

You can test the calculation by increasing the CPU cores used and the MAXDOP setting. The consumption rate should see linear growth as you add CPU cores. In addition, you can join and aggregate the two tables to simulate more intensive queries and to gather the consumption rate of different types of activity. This helps map the expected solution complexity to an average consumption rate.

Calculating the Storage Array and Drive Throughput Rates

The second set of throughput rates you need relate to the storage array system. Many of these values are documented by the storage systems, but it’s always wise to test the system to verify the throughput. Again, check with the storage vendor to see whether it has already tested the system using the new Fast Track reference architecture guide for data warehouse systems.

Table 2 lists the numbers and calculations you need to determine system throughput; for reference purposes, the table includes the tested numbers for the EMC CX4-240 and the HP MSA-2000.

SAN Calculation Rates EMC CX4-240 HP MSA-2000

Max number of Storage Processors per Storage System

2 each

2 each

Max number of Drive Arrays per Storage System

2 each

1 each

Max number of 3.5 drives per Drive Array (DAE)

15 drives

12 drives

Max rate per Storage Processor

500 MB/s

550 MB/s

Max rate per LUN RAID 1 on Storage System

240 MB/s

150 MB/s

Max rate per 1/2 DAE on Storage System

370 MB/s

600 MB/s

Estimated Drive Capacity

272GB

272GB

Estimated Drive Throughput

250 MB/s

250 MB/s

Table 2: Storage numbers and rates for calculating storage system throughput

As you can see, in addition to the throughput rates of the LUNs and drive arrays, you also need the number of storage processors (SP) and DAEs per storage system to see how many storage systems your architecture requires. For example, a single HP MSA-2000 system uses only two drive arrays with 12 drives in each, so larger data warehouses using that SAN platform would need to use multiple HP MSA-2000 systems.

After you gather the CPU core consumption rate and the storage system statistics, you’re ready to create a core-balanced server matrix of CPU cores to storage capacity for your new Fast Track reference architecture.

Updating the SMP Reference Calculator Spreadsheet for a new Hardware Configuration

The final step in building a custom reference architecture is to map CPU cores to storage throughput by using the core-balanced approach. The SMP Reference Calculator spreadsheet helps you create this map.

To create a new CPU core-to-storage matrix, first copy one of the worksheets in the Excel document (either for the EMC CX4 or the HP MSA system) to a new worksheet and rename it to the new storage platform you’re using. Then, modify all the inputs, including the CPU Core Consumption rate and all the Storage System numbers.

Figure 7 shows summarized output from the spreadsheet calculator.

Figure 7: The spreadsheet calculator maps the required number of cores to system throughput and capacity.

The result gives you a set of balanced systems, where the number of cores matches the number of storage systems, SPs, DAEs, drives, and optimal storage volume.

Now, let’s walk through an example of calculating the number of cores for specific data warehouse requirements.

Whether you’re using a preconfigured and tested Fast Track reference architecture or you’ve created a new architecture, to select the correct system, you first must determine how many CPU cores you need to meet system requirements. You can then determine which hardware platform is right for the data warehouse solution

Calculating the Number of CPU Cores

Use four inputs to calculate the number of cores you need:

1. The CPU Core Consumption rate. This input, which is already calculated for the tested Fast Track configurations, is 200 MB/s.

2. The amount of data scanned in an average query. For example, if the average query scans 1 month of data from a fact table that contains an average of 10 million rows a day and each fact table row takes up 60 bytes, then the amount of data scanned is about 18,000MB.

3. Target response time in seconds per average query, such as 60 seconds.

4. The expected concurrency. This rate, on average, is 10% of the number of users. So for a 100-user system, the concurrency would be in the 10-concurrent-user range.

The formula for calculating the number of needed cores is:

 (Amount of data scanned in an average query/CPU Core Consumption Rate)

 * Number of Active Concurrent Sessions/Target Response Time

With the example values described above, the calculation would be:

((18000MB/ 200 MB/s) * 10)/60s = 15 CPU Cores

Of course, a 15-core system doesn’t exist, and because the results are estimates, the number of cores should be rounded up to the next core increment count, such as 16, 24, or 32.

For more details and an expanded example of the calculation, see the “Implementing a SQL Server Fast Track Data Warehouse” paper.

Choosing the Right Hardware

Based on the CPU cores example calculation we just worked through, the estimate points to a system with 16 CPU cores, which can either be a 4-CPU socket system with four quad-core processors, or an 8-CPU socket system with eight dual-core processors, assuming the core consumption rate is 200 MB/s.

However, to select the correct system for the Fast Track reference architecture you’ve selected, you also need to have an estimate of the data warehouse volume. Although the Fast Track reference configurations focus on CPU core count, to select the right storage system and drive count, you need to know how much storage you need.

If you already have a data warehouse implemented, you can easily get this data from your existing system (be sure to estimate future growth). Otherwise, you need to run some estimates, which can be as simple as multiplying the number of expected fact rows of the solution times the average row width, and adding 30-40% overhead for indexing and dimension tables.  You can also apply a 2.5x average compression ratio with SQL Server 2008.

After you have the number of CPU cores and a rough estimate of your storage space, you can review the hardware selection options.

Table 1, shown earlier, summarizes the tested Fast Track reference configurations, with the capacity and throughput tested with the 300GB 15K RPM drives. The tested capacity is the usable storage amount, so as long as your storage requirements are within range of the tested capacity based on the number of needed cores, the hardware selection is straightforward.

The final Data Warehouse Fast Track step is implementation. This phase includes the required steps for implementing the hardware and tuning the system, covering both system and SQL Server settings as well as database objects and loading processes.

The steps are detailed in the “Implementing a SQL Server Fast Track Data Warehouse” paper in the Architectural Details section.  Below is a brief summary of the recommendations.

System Configuration

The most important step during system configuration is to create the right redundancy and LUNs in the storage array. The SMP based Fast Track reference architecture approach requires that you adhere to the setup steps for the drives.

The following summarizes the crucial storage array setup steps:

  1. Configure the drives in RAID 1 mirror sets.
  2. Map the RAID 1 drive sets directly to LUNs—do not combine together.
  3. Map the LUNs to Windows OS volumes or mount points.

The second system configuration practice revolves around one of the Windows Server memory settings. The Lock Pages in Memory setting should be enabled through the group policy.  Overall, this will reduce the operating system disk I/O overhead and ensure that the data in memory is readily available to the processors without waiting for it to be recovered from the LUNs.

By default, the recovery mode setting of new databases is full recovery. This will capture all transaction activity in the log file, including bulk operations.  Since data warehouse applications often are only loaded on a recurring basis and the majority of the activity is read operations, you can set the database recovery mode to simple recovery and perform nightly full or incremental backups.  This will minimize the log management overhead and growth of the log file.

Database files, both log and data, should be initialized and expanded when they are first created.  This will prevent autogrow operations, which degrade performance during ETL operations, and will also speed up restore operations if needed.

Leveraging the Fast Track reference configurations also requires that the database files be configured on the LUNs with a specific file layout

  • Each user database should have two files created per LUN.
  • For the TempDB system and staging databases, create one file per LUN in the primary database filegroup.
  • For each user database, expand the all the data files and turn off the AUTOGROW database setting.
  • The staging and TempDB databases should have AUTOGROW enabled at a 4MB interval.

User Database Partitioning and Indexing

The general indexing practice for data warehouse activity is to optimize for rapid retrieval of queries that return large data scans.  For a transactional system, the goal is to identify isolated records.  Therefore, someone designing the indexes for a transactional table would select columns used in a join or where clause that filters the data as much as possible.  For a data warehouse, the goal is to select the right columns that target a range of data, such as a date (without the time stamp). This often requires a shift in the way of thinking about indexing.

For indexing and partitioning, here are some general practices to follow:

  • Clustered indexes should be used on most tables with high user queries, except when the table doesn’t have a date component or the query pattern doesn’t perform many filters or joins. The clustered index on a fact table should most often use the date column if the most common column used to filter and scan data.
  • Use non-clustered indexes on tables to support more granular query lookups.  Larger dimension tables can benefit from several non clustered indexes.  Consider including the dimension surrogate key as part of the INCLUDE statement in a covering index to optimize the join to the fact table.
  • Large fact tables should most often be partitioned by a date range, which will help in the query optimization as well as the removal of aged data.  It will also reduce fragmentation when data is removed.

Ongoing Database and File Maintenance

Ongoing table maintenance and file maintenance will ensure that the performance of the data warehouse queries stays consistent and that the throughput capabilities of the balanced system remain maximized.

The general goal is to reduce fragmentation since fragmentation will inhibit the underlying drives from performing well to handle sequential I/O patterns of a data warehouse.  Be sure to plan for these ongoing maintenance operations:

  • The database statistics should be updated regularly, most often immediately after a data load has happened. A planned weekly or monthly routine should also include defragmenting or rebuilding the indexes on the tables.
  • Defragmentation should also be considered at the system file level using the Windows defragmentation tools. 

Data Loading Best Practices

Finally, since all data warehouse or data mart solutions require the addition or update of data in a bulk fashion, it is important to consider the practices to efficiently load the tables and update the indexes.  Especially with large data volumes, the ETL operations often will take hours to perform, therefore tuning the loading process will increase the database’s availability for queries.

The Implementing a SQL Server Fast Track Data Warehouse documentation details the approaches to load a staging environment, adding data to a table without a clustered index, loading data into a partitioned table.

<span id="_Toc222293534"><span id="_Toc221356428">

In summary, the SQL Server Fast Track Data Warehouse resources provide the background, tools, and tested hardware configuration to accelerate your selection and configuration of a scale-up SMP data warehouse solution.

In addition to the tested Fast Track configurations, data warehouse solutions can also be implemented on new hardware platforms—using the new Fast Track architecture approach.

Because this new Fast Track reference architecture model is based on the query activity footprint of a data warehouse solution and uses the core-balanced approach to integrating the hardware and solution, it is important to educate all parties involved in the selection and configuration process of the components.  Failure to follow through on all the configuration recommendations will limit the throughput and performance capabilities of the systems.

The performance of a data warehouse goes beyond the hardware configuration to include the design and settings of the software.  The Fast Track reference configurations therefore also provide prescriptive guidance to optimize the database through configuring the files, indexes, loading processes, and the ongoing maintenance.

For more information:

SQL Server Fast Track Data Warehouse home page

Implementing a SQL Server Fast Track Data Warehouse

SQL Server 2008 Data Warehousing web site

Project codename Madison web site

SQL Server TechCenter web site

SQL Server DevCenter web site

SQL Server Customer Advisory Team web site

Using SQL Server To Build A Hub-and-Spoke Enterprise Data Warehouse Architecture

Hub-And-Spoke: Building an EDW with SQL Server and Strategies of Implementation

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

  • Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
  • Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

Send feedback.