Saturday, 22 November 2008

Using enterprize storage drives to host database files

It can make sence to use network storage devices to host database files. I believe this works for small administration databases. But if the database needs to handle some significant load you can end up in some performance problems. Here is an interesting story demonstrating limitations of network storage

22.10 5:30pm GMT+1
There are serious time out issues in our Eastern US offices that stop a database application from working. CPU is about 70-90% PF Rate is at a stable 80%

23.10 7am GMT+1
CPU is about 0-10% after applying artificial loadPF Rate remains stable 80%
Since it is 3am in Eastern US this is a good indication that cause if from responsiveness of Network storage

23.20 8am GMT+1
I move the database files from Network storage and put them on D:

23.10 1pm GMT+1
The CPU is around 30% a significant improvement.

23.10 5:30pm GMT+1
The performance has improved but still lots of time outs

24.10 7am GMT+1
I move the TempDB to D:
At about 11am EUS time and one of my collegues increased the paging memorySince there where no performance issues that day I believe the TempDB was the main cause of the problem and not the Paging memory. Tests carried out at 1pm GMT+1 and the fact that there where no performance issues between 9-11 EUS time support this

Lessons learned
Putting database files on the netapp share can produce a significant drop performance.The drop in performance is only apparent during the day as the Netap share is being used.