Performance Testing of Teradata v12 with SQL Server 2005 Analysis Services

  • Article

Ok...here's part two of the Teradata v12 series. 

The first blog entry was focused on highlighting a sequence of functionality tests that were recently concluded back in February 2008 against Teradata version 12 using SQL Server 2005's Analysis Services (and the latest beta v12 .NET Provider from Teradata.)

This follow-up blog entry will focus on the benchmarking result efforts (up to 1000 simulated users) of queries against Analysis Services.  What was recorded was average response time per SSAS query using a Teradata backend source, comparing response times when Teradata v2R6.2.1.3 is used and v12 is used (both using the v12 .NET Provider).

Background

The testing focused on optimizing the Project REAL Toolkit data warehouse with a goal of identifying the optimal concurrent workload for sub-second response time and indentifying best practices for a ROLAP environment.

Database preparation was the first step, and scope of that effort was as follows:

  • Migration of the Project REAL sample data kit to a Teradata system provided by Teradata.
  • Identify an appropriate database schema for Project REAL analytics based on the POC objectives.
  • Redesign the Project REAL solution into a Snow-Flake schema to more effectively take advantages of Teradata optimization capabilities.
  • Grow the data to 100GB.
  • Tune the Teradata system for the user load.

For conducting the testing effort, we focused on the following:

  • Functionality testing of Analysis Services running against Teradata v12 .NET Provider and v12 Database Engine (aka, the first blog entry in this series).
  • Test script preparation leveraging Visual Studio Test Suite for Testers.
  • Benchmark and optimize the Project REAL solution for user load against TeraData v2R6.2.1.3 using the new Teradata v12 .NET Provider.
  • Database migration from Teradata v2R6.2.1.3  to Teradata v12
  • Benchmark and optimize the Project REAL solution for user load against TeraData v12 using the new Teradata v12 .NET Provider.

Test Results

The successful execution of the engagement resulted in the following benchmark results (which was based on SQL Server 2005 Analysis Services SP2, Update 3175 applied):

  • Ran various user loads up to 1000 users with response times less than 1.2 second for tests against the Teradata v2R6.2.1.3 engine using the v12 .NET Provider.
  • Ran various user loads up to 1000 users with response times less than 0.93 second for tests against the Teradata v12 engine using the v12 .NET Provider.

Test Environment

The hardware configuration used for the testing was as follows:

Analysis Services System:

  • DELL two socket, quad-core system (eight total processor cores) with 1.86 GHz Intel Xeon and 4GB RAM
  • 1 TB drive - The Analysis Services System does not require disk storage space for a ROLAP storage deployment.  In a Teradata scenario, a best practice is to store the dimensions as MOLAP as a result an insignificant amount of disk space was used.
  • Windows 2003 Server X64
  • SQL Server 2005 x64 SP2 (patch level 3175)

Note that besides Analysis Services, SQL Server's RDBMS and the Visual Studio Team Suite Testing tool also ran on this server. On average, only 25% of server resources were available to SQL Server Analysis Services for the tests - which certainly reduced the overall performance of Analysis Services capabilities. However, this benchmarking test was a comparison of TD v2R6.2.1.3 to TD v12...so it didn't matter that resources were consumed by other services...since the benchmarking tests were resource and query "apples to apples" similar.

Teradata System:

4-Node Teradata 5500 system running database v2R6.2.1.3, which was also then upgraded to v12.

  • Disks: RAID1 with 96 x 73GB disks

Each node has:

  • 12 AMPs and 2 PE's
  • 4 CPU's @ 2.66Ghz