I currently have a SQL server with 2 hard drives. One drive has the OS, the SQL installation, logs and some databases. The other has additional databases. We added the 2nd drive when the first one started to fill up.
I was basically given the green light to add more drives to this if we believe it will help with performance. The problem comes up every morning when we do our data loads.
The database serves mostly as a data warehouse, we we use it all day for analysis. Every morning we load about 60mm records into the database. As part of our daily analysis we make frequent use of temp tables.
I am trying to figure out what the optimal setup would be in terms of spreading the current setup across multiple drives. I was thinking:
Drive 1 = OS/SQL Installation/logs
Drive 2 = TEMP DB
Drive 3 + 4 = Databases
I guess my questions are:
1. Does it matter that the SQL logs go on the same drive as the OS and the SQL installation?
2. Does it help to have temp DB on its own drive?
Any other general pointers/wisdom about setting this kind of stuff up would be appreciated.