Should we separate tables and indexes on a separate drive?

SQL Server Administration
1

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?

2

yes, and separate log files and tempdb as well. Oh, and backup loacations

3

My batting order would be:

Data / Log files on separate drives. If backups are on the same drives then Log backup on Data file drive, and Full / Diff backups on TLog file drive.

Backups on separate drive

TEMPDB on separate drive

Beyond that - moving Indexes / Tables to separate files / drives - my guess is that quite a lot of effort is required to work out which objects would benefit from moving around, and what else might share the same file / disk with them.

1 Like
4

In general, no, I don't believe this old advice holds up any more. There are just too many files on modern drives for this to matter all that much.

But overall, some general rules still apply:

For recoverability, the data and log files must be on separate drive sets. However, data and log files can share the same drive, as long as they are for different dbs. That is, dbA's data files could be on drive set 1 with dbB's log file, while dbA's log file and dbB's data files were on drive set 2. Avoid RAID5 for log files and other files with lots of writes if possible.

If possible, put tempdb either on a separate drive(s) or across all drives.

For recoverability, backups should not be on data file drives. They can go on the log drive or a separate drive. Again, this is db by db, not necessarily whole drive by whole drive.

Anyone has tried separating tables and indexes onto separate drives?

Not much recently.

2 Likes
5

I hadn't thought to do that. The drives we have tend to be the same size, so we wind up filling the Data ones and barely filling the Log ones ...

... although our drive configurations are chosen / tuned for Data and Log (and Backup) specifically, but I expect that probably doesn't matter - we aren't a huge-data-shop, nor a massive-number-of-users one either :slight_smile: