At the moment we are using a single Data File for our Database. We are considering keeping all our tables here - but move all our indexes (i.e. not including the Clustered PK of course) onto a separate file on another disk.
A colleague said it would improve performance since with Read/Writes, this would get multiple disks going. I'm a little sceptical since we have a lot of RAM and SQL Server tries to leave as much Data in RAM as possible. And I believe the disk read/writing happens as a separate process, which should not affect the performance of the CPU's data crunching.
Any thoughts? Anyone has tried separating tables and indexes onto separate drives?