SQL Performance Tuning Test Results for Data Warehouse Architectures
Many clients ask us what the most optimal server environment is for data warehouse workloads. This post shares a simple test result from SQL Server performance tuning and optimization tests conducted by BlueGranite's team on various data warehouse server architectures.
To conduct these tests, BlueGranite used Microsoft's free SQLIO Disk Subsystem Benchmark Tool to benchmark the hardware performance capabilities of each data warehouse server environment. Many other categories of tests and analyses that are performed as part of this weeklong program but this post will focus on hardware performance (MB/second) as tested by SQLIO.
For these tests, BlueGranite used a variety of hardware configurations that we typically find with client development, test, and production environments.
- QSDW: Dell QuickStart Data Warehouse appliance built to Microsoft FastTrack Reference Architecture Best Practices such as parallelism
- DAS – SSD: Direct Attach Storage with only Solid State Drives
- SAN – SSD/HDD: Tier 1 SAN that uses a combination of Solid State Drives (SSD) and Hard Disk Drives (HDD)
- SAN – HDD: Tier 2 SAN that uses only Hard Disks Drives (HDD)
- Laptop: Laptop with one 7.2K Hard Disks Drives (HDD)
As indicated by the graph, the Dell QuickStart Data Warehouse (QSDW) appliance outperforms even Solid State Devices and both types of SANs. This is due primarily to the fact that the QSDW is specifically tuned to data warehouse workloads which are typically characterized by sequential read/writes in block sizes greater than 64KB. In fact, subsequent tests of the QSDW revealed that performance actually increased as the IO block sizes increased. This is ideal for SQL Server Data Warehouse workloads as they have a tendency to have large block sizes.
Ensuring that the Data Warehouse server is specifically tuned for SQL Server Data Warehouse workloads can have a dramatic impact on performance. In fact, this tuning can even be more import than the type of storage technology selected e.g. SAN, SSD, or HDD. A purpose-built appliance that is pre-tuned for SQL Server Data Warehouse workloads can provide a significant performance boost to Business Intelligence solutions.
These test results were gathered as part of a series of Data Warehouse Health Check program conducted by BlueGranite. These weeklong engagements are designed to gather detailed SQL optimization and performance metrics in order to make recommendations for performance tuning tasks.
Please visit our article on SQL Server OLTP vs. Data Warehouse Performance Tuning. Or for more information on our Data Warehouse Health Check program please Contact Us.