How to speed up the database.
What methods are available to do this?
Microsoft have a large number of good documents on this subject if you can find them, but basically to maximise disk throughput you should put your MDB (data) file on a raid 5 array, and your LDF (log) file on a mirror array. Also, your operating system should be on it’s own drive and the page file should be on it’s own disk or mirrored array (depends on how much server space and spare hard drives you have). Most servers these days though are quite limited in terms of the number of local disks you can stick into them, so if you can’t afford a SAN then your solution might be limited by the number of physical drives that you can stick into the machine. Also, make sure that you take the physical size of the drives into account in your raid because the larger the drive the more work the disk heads have to do to find the data. i.e. 500GB drives are not a great idea. 72GB drives are a nice size, but if you have to use larger try not to go over 320GB drives.
Depending on your power consumption issues, you could also try 10k rpm disks instead of 7200rpm disks.
Also, put in the maximum amount of RAM as it’s cheap. The only problem with that is the Microsofts OS’s are not great at memory addressing so you might be limitted to 2 or 4GB ram regardless of how much you can physically stick into the machine.
Example:
C (no raid) OS and Apps
D (no raid) page file
E (mirror) Data
F (mirror) Logs
The reason not to raid the OS is because you don’t give a monkeys about any of that data and the files don’t get used that often anyway, just make sure you have a good restore plan. The no raid C and D would give you better dedicated through put, but if you feel edgy about it then you could mirror them. If you do mirror them then partition them off so that the page file can go on a separate partition. Possibly try to move the system databases onto E and F too i.e. master, temp (ignore model it’s only used as a template when you create a new db).
