Fellow SQL MVP Paul Randal of SQLSkills, (who is also Kimberly Tripp’s husband, and a former long-time Microsoft employee) has been working hard on a great benchmarking series with some actual Dell server hardware that he has setup in his house (and I thought I was a geek). His test hardware (shown below) includes two Dell PowerEdge 1950 servers and three Dell PowerVault MD3000i iSCSI arrays (although he is only using one MD3000i for the initial tests).
His initial baseline test is measuring how long it takes to populate a 1TB clustered index, doing just simple T-SQL inserts, with varying numbers of connections. He purposely placed the data file and the transaction log file on the same RAID 10 array (which is not a good thing to do, but something I see people doing very frequently) for the initial two rounds of tests. As expected, he saw very major IO bottlenecks because of this.
What is nice about this series is the amount of measurement and analysis that Paul does to explain the results. Its very clear that he has a deep understanding of the internals of the Engine, which he uses to great advantage in his posts. Paul has a nice shout-out to SQL MVP Denny Cherry and to me in the part 2 post, which I very much appreciate. The posts in the series that have been published so far are listed below:
Benchmarking hardware setup
Benchmarking: 1-TB table population (part 1: the baseline)
Benchmarking: 1-TB table population (part 2: optimizing log block IO size and how log IO works)
Paul Randal’s Home Server Rack