I often get asked about performance issues with SQL Server. I often find with a quick check that the data and log files for the database are being stored in terrible inefficient ways. I am not going to argue local disks versus SAN storage in this post, as more often then not my clients are moving to virtualized environments so the storage is on a SAN or on a centralized disk array of some sort.
Lets start with one big drive is not a good way to run an Enterprise Database. I appreciate that SQL Server is easy to setup and get working, but making it work efficiently and effectively takes a little more understanding then accepting the default prompts during the install.
1. Have a small drive setup to run the OS layer of the machine, I have found 40gb is usually more then sufficient for this.
2. Storing Data files: these should be stored on a drive that is within a RAID 5, 6 or 10 configuration.
3. Storing Log Files: these should be stored on a drive that is within a RAID 1 or 10 (however, 5 or 6 will work too).
4. Storing TEMP DB Files: these should be stored on a drive that is RAID 1 (however, 5,6 or 10 will work too).
In a lot of cases, items 2,3,4 are all stored on the same drive. This maybe fine for non-critical stuff, but you will want your Production environments split up. The most common drive configuration I see is RAID 5 or 6, but in bigger SAN’s you will see RAID 10 often. Now that drives are relatively cheap I find disk arrays to be very cost effective for organizations and offer up great flexibility. This is one area that I recommend buying good equipment. Additionally, take speed over capacity and quantity over capacity. The faster the data can be read and/or written makes a difference, and the more drives you have the more heads you have for reading and/or writing.
Lastly, know what is sharing your storage areas. If you have a SAN that is carved up and server multiple databases or environments, you will end up spreading the read/write ability of the equipment across those environments. This is a common problem I see where people will say there must be something wrong with PeopleSoft because it isn’t doing anything and is still slow, but if you dig into the available resources you will find another application is hammering the resources which are shared with PeopleSoft, and therefore PeopleSoft is working fine it just doesn’t have any available resources to run effectively.