Part 1 of this blog talked about the bad experience we had with a badly configured SQL Server. The second part of this blog will be talking about all the steps we took to configure a high performance SQL server. It has been more than two years since we did this, so I do not remember all the things that happened. One thing I know for sure was that everyone was wondering why it took us 4 months to build a server. We had to explain to them what we were doing and why were doing it. Quite a few of them were skeptical but they changed the tune once we got our servers performing on our production environment.

As soon as we realized that we had a bit more time in our hand to upgrade our server, we decided to sit with the top management and discuss what we wanted to do and how we want to do it. At that time, we were running on Windows 2003 Enterprise Edition with SQL Server 2000 SP2. We advised the management that it would be wiser to move to a better technology. We were able to convince the management that the path to take is to move towards the latest technologies. We pushed for Windows 2008 Enterprise edition (64 bit) and SQL Server 2008 enterprise edition (x64 SP1).

We then purchased two identical servers. The servers were HP DL580 G5 with 4 CPU(6 core) with 128 GB physical memory and one external drive (MSA70). HP Server we ordered came with a P400 and P400i internal IO controller. We got a P800 IO controller additional for each server. This gave us a total of 4 separate IO channels (P800 has 2).

One of these servers would be our production server and the other would act as the warm stand by.  We got our Admin to build the Server with different disk configurations (RAID 10 was used for all configuration) and used SQLIO to test the IO performance. After doing some more homework, we found out about SQLSim. We stopped using SQLIO and started using SQL Sim.

We used different scenarios for finding the optimal set up. We first started with a simple configuration of having the Data and log files in different drive. All of them were 300GB 15K RPM disks. We saw that the average queue length was on the high side. We changed the disk size from 300 GB to 146 GB and saw that we gained a bit of performance. But it was still not good enough.

We changed the scenario slightly by moving TempDB data files and User DB data files to different drives with its own IO Path. The Log files were on the same drive sharing the same IO path. This gave us a better performance. We tried couple of other scenarios where the IO controllers were changed to give dedicated IO path for different drives. The last configuration we tried was to use one more MSA70 external drive. This was dedicated for the TempDB data and log file. It used one of the IO channels from the P800 controller. The other IO channel of the P800 controller was used for the User DB’s data file. This data file was configured to use the other MSA70 external storage device. The P400i controller was used for the User DB log file and the P400 was used for the OS. This gave us the best IO configuration. We decided to keep this configuration.

This meant that the company had to make additional purchase (2 MSA70 and Disks) On the hindsight, I think we could have improved the performance a bit more by using 72 Gb drives. This would have given us more spindles. I am not able to provide the test details since my company will not allow it.

Part 3 of this series will show the SQL Server configuration and our upgrade plan for this project.