There is always some disconnect between the network admins and the DBA’s regarding hardware set up of a SQL Server. If you ask a Network admin to build a SQL Server without specifications, they will install the server just like they install any application server. To be honest, fifty percent of the DBA’s do not understand the importance of hardware for a SQL Server.Here is an example of how server set up can impact the performance of the Database.
Two years back I had to make a trip to India due to family emergency. At that time we had only one DBA working in our company. The day after I left for India, the existing DB server had a problem with one of the physical RAM chip set. Since there were no DBAs the Management team took some bad decision. One was to stop taking Log back ups. The logic behind their decision was they were afraid that the log back ups might be corrupted. When I read the email regarding that decision the next day, I had to call them. I do not think it was a nice call I made since I was going through the loss of someone I loved and respected.
They took a DBA on contract for a month since I was not available to take care of things. The first thing the new DBA did was ask the Network admins to build a new server to replace the old one. The server was of newer generation than the existing one. It was a more powerful server. It had more RAM, CPU was much more powerful and the number of CPUs was also higher than comapred to the previous server. The DBA asked the Admins to build this server with two drives. One C-Drive and a D-Drive. Once the server was ready, he migrated to the new server. The C-Drive was left for OS and the D-Drive had the data and log files for both TempDB and the user DB.The performance unfortunately was awful compared to the old server. The main bottleneck was the IO.
On the old server we were doing 5000 Batch request per second and in this new server the batch request per second dropped to 3000. When I came back to work the first thing I did was to replace the RAM (Actually upgraded the RAM as well) in the old server and migrated our DB back. Once that was done, I asked the DBA why he asked for this particular configuration. He had no clue that SQL Server could be IO intensive. He did not know that the log files are written sequentially and Data files randomly. He just wanted to have enough space to put everything in. I had to expalin to him why it was a bad idea to have this configuration.
Since the old server was working as usual, I decided to take time to set up the new server with the right configuration. First thing I did was to hire another DBA and get the management to assign one network admin solely for the DBA team. We took nearly 4 months to find the right configuration for our new server.
I will be writing about how we set up our new server as part two.