Working with Real Data
Happy with our raw-disk throughput and capacity, we were ready to restore our 2TB customer database.We used 64 drives for the two central databases that comprised the 2TB customer data warehouse, which came from one SQL Server 2000 instance. We used Windows 2003 to mirror pairs of drives.This made our useable database disk space just over 12TB. Given the cost and the throughput of this server, and having the fault tolerance of mirrored drives, we were confident we had a high-performing, reliable server with a lot of disk capacity. We were still very content that we had 12TB of useable storage for our prototype server.
We restored the two customer databases. The source and target databases each contained 16 SQL Server data files, and each data file was on a separate mirrored volume. We overlapped eight files from each database on the same set of eight mirrored disks so that we had eight free mirrored disks for TempDB and the SQL Server log file.
Once we restored our databases, we set out to run several stored procedures, queries, and index builds on our prototype server. By doing so, we were able to compare exact times for our prototype to the SAN-based customer server, a 16-CPU server with more than 64GB of main memory connected to a high-end SAN that has six fiber-channel controllers that deliver about 170MB per second of sequential data throughput. Table 2 shows a comparison of some notable results from both servers.
Although we don't have room in this article to include more results, you can see from some of the test results in Table 2 that our prototype server holds its own against a much larger and more expensive server. In addition, for operations such as SELECT INTO and index builds, we saw significantly better performance compared to the customer server. Only the range query was slightly faster on the customer server. We plan many more tests on concurrent query workloads and OLTP-type workloads, instead of the sequential I/O-based workloads we've tested so far. It's important to realize that up to this point in our testing, we've used only SATA drives, which offer data throughput comparable to SCSI or SAS drives for workloads that are sequential in nature. However, SATA drives are significantly slower than SCSI or SAS drives for random I/O operations. A typical SATA drive at 7200 RPM delivers about 100 I/Os per second, whereas SCSI or SAS drives can deliver more than 150 I/Os per second. Because our server used SAS HBAs and expanders, our next round of tests will use SAS disk drives that we simply plug into our Chenbro drive chassis after removing the SATA drives. We also anticipate some exciting results for OLTP-type workloads.
The combination of hardware,Windows Server 2003 x64, and SQL Server 2005 x64 in our prototype delivers a formidable combination of database-processing power, disk capacity, and pure throughput. I hope the results I've presented here will spur customers to evaluate the opportunities for using SQL Server 2005 x64 for development and QA servers in addition to production server applications.
Author's Note: Special thanks to Jim Gray of Microsoft Research for his vision and research,which inspired our prototype and this article.
End of Article
Prev. page
1
[2]
next page -->
