I previously wrote a post about some efforts in database performance tuning but recently I have been investigating the impacts of hardware configurations.  To date, the test system I have been using was configured with two 80GB drives in Raid 1  for the operating system and six 500GB drives in  Raid 5 for the database files.

I recently took a stab at measuring a hard disk’s read and write performance in this configuration and found it less than desired.  Between the two logical drives, the system received about 90 Input/Output Operations (IOPs).

In an initial attempt to improve performance I added partitions and file groups to a large data table, but I didn’t see any performance improvement in the execution time of queries.  In some cases I saw a reduction, especially on a cold request (i.e. the requested data was not in SQL Server’s cached memory).  After adding partitions and file groups, cold requests took two or three times as long to retrieve the same data.  While warm requests were relatively the same but varied by 50 to 150 milliseconds longer. I was definitely not  expecting this outcome and while discussing the results with my team we decided to modify the hardware configuration.

I reconfigured the two 80GB hard drives to be single volume drives.  One would be used for the operating system while the other for the log files.  Then I configured the remaining six 500GB hard drives in Raid 0.  The objective of this configurations is to maximize the IOPs and isolate the database files (primary, secondary, and tempdb files) from the operating system and log files.

Once the system was set up and configured with SQL Server I built a database with roughly 120 million records in the main table.  I also set up a Performance Monitor Data Collector Set to help analyze the performance of the hardware.  I configured the Data Collector Set to gather data on a 15 second interval with the following performance counters:

  • MemoryAvailable MBytes
  • PhysicalDisk% Disk Time
  • PhysicalDiskAvg. Disk Queue Length
  • PhysicalDiskAvg. Disk sec/Read
  • PhysicalDiskAvg. Disk sec/Write
  • PhysicalDiskDisk Reads/sec
  • PhysicalDiskDisk Writes/sec
  • Processor% Processor Time
  • SQLServer:Buffer ManagerBuffer cache hit ratio
  • SQLServer:Buffer ManagerPage life expectancy
  • SQLServer:Buffer ManagerPage writes/sec
  • SQLServer:Buffer ManagerPage reads/sec
  • SQLServer:General StatisticsUser Connections
  • SQLServer:Memory ManagerSQL Cache Memory (KB)
  • SystemProcessor Queue Length
  • Paging File(??C:pagefile.sys)% Usage

My main focus was on the execution time of four queries, requesting data for two week, two month, two quarter, and two year periods.

Time (ms) Cold Warm
2 Weeks 11,749.70 11,571.40
2 Months 60,185.30 59,572.70
2 Quarters 136,380.10 135,149.60
2 Years 504,535.70 504,821.20

As you can see above, the execution times to retrieve data from the primary file of a database can leave something to be desired.  I also analyzed the hardware performance information from the Data Collector Set.  I was happy to see that the average disk IOPs were around 150 with the Raid 0 configuration, as opposed to around 90 with Raid 5. But I was a little worried about the average disk queue length and the processor queue length.

 Performance Counter Average Max
Avg. Disk Queue Length 19.29 245.61
Disk IO/sec 151.16 905.51
System Processor Queue Length 1.41 12

I wanted to see how adding partitions and indexes to the database would impact the performance.  As I mentioned earlier, when I added partitions in the previous configuration  I didn’t notice a difference in the execution times of queries.

Time (ms) Cold Warm
2 Weeks 2,151.30 1,702.00
2 Months 10,954.60 8,710.90
2 Quarters 24,629.20 20,595.70
2 Years 93,927.70 73,248.30

But as you can see, the execution times of the same queries improved quite a bit.  Additionally, the disk drives appear to be under much less stress in this configuration.

 Performance Counter Average Max
Avg. Disk Queue Length 0.29 1.92
Disk IO/sec 54.87 570.95
System Processor Queue Length 0.72 5

The bottom line, at least for me, is that multiple hard drives configured in Raid 0 can significantly improve the performance of throughput and data retrieval in a large database.  And when combined with an implementation of data partitions, secondary files, and indexes, further performance gains can be achieved.

I found a great deal of information at SQLServerpedia and this post on Performance Monitor was essential to taking on the problem of database performance tuning.