Separate Data and Indexes to different disks?

[caimoss]

I'm looking for some resources which talk about pro's and con's of separating non-clustered indexes from data files onto separate filegroups and/or physical disks. I've found numerous resources which stress the importance of separating log files from data files, but when you start to look for data files vs. indexes, the information gets sparse.

We are trying to isolate a performance anomaly on a server which has this type of separation (it's showing that the SQL Indexes drive is under extreme duress). For the purpose of my question, please assume that all hardware (CPU, disks, RAID controllers, etc...) have more than enough bandwidth, and that we have enough physical disks in each RAID array. I know that is a lot of variables, but I just need help focusing on the location of indexes vs. datafiles:

Is there a problem with the following config (you may assume multiple physical disks supporting each controller channel)?

Disk -- Role -- RAID Controller -- Controller Channel -- RAID Level
C -- Operating System -- 0 -- 0 -- RAID 1
D -- SQL Server application -- 0 -- 0 -- RAID 1
E -- SQL Logs -- 0 -- 1 -- RAID 1
F -- SQL Data -- 1 -- 1 -- RAID 5
G -- SQL Indexes -- 1 -- 0 -- RAID 1

By way of comparison, we have a configuration where the "G" drive does not exist, and the F drive contains both the SQL Data and Indexes. So, in that case, the SQL Indexes live on a RAID 5 disk.

I haven't found anything here in the forums or FAQ's after several searches, so if I missed something or there is an external site you can recommend, that would be greatly appreciated.

Thanks!

-caimoss

 

[yelworcm]

I do not see anything wrong with the configuration you have listed, but I am not an expert in RAID.
I am surprised that you have extreme duress on the index drive, though. If you have duress on the index drive, in this confiiguration, you should have duress on the data drives, as well. Is there anything else on the G:\ drive? Perhaps image/text data (index 255), or something on the filesystem (log backups, maybe)?

 

[caimoss]

Nothing else on the G drive--it's strictly the non-clustered indexes.

I'm not an expert in RAID either, but I am currently looking at the hardware breakdown within each drive (controller cards, channels, # of physical disks, etc.). There is a TON of read activity on the SQL Indexes drive, and I'm wondering if the internal makeup of the array is bad (not enough disks, wrong disk speed, etc.).

Just wanted to know if separating Indexes from Data is a bad thing in theory! Thanks for the input, and any others coming this way!

 

[mrdenny]

Your drive config looks fine. When is the last time you updated stats and defragged your indexes?

How many transactions do you have hitting your indexes?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[caimoss]

Hi mrdenny,

-We have Autostats set to ON.
-Defrag currently occurs weekly, although we have tried various schedules for that.

I can't answer your third question--but that's because I don't know how to just yet!

Unfortunately, I am not a DBA, just a very detail-oriented developer. This is one detail the DBA has not been able to provide me: how much our indexes are being used. If you can point me in the right direction, I'll educate those with the power to execute it here. Maybe I'm just not asking them the right questions.

As I dig into this I'm wondering if the Indexes drive can't keep up with the disk I/O. Because it's RAID 1 that's at most 2 drives handling the requests, and if the drive doesn't have "Split Seek" enabled (still investigating that), then we've got one drive handling all the index requests.

 

[mrdenny]

The DBA will want to use Profiler to see what's happening within the database.

Moving the indexes to a RAID 5 array would probably speed things up. Most RAID 1 arrays that I've seen only use one drive for reading.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[caimoss]

Thanks for all the help everyone.

For future reference, I finally found a specific reference about this in SQL Server 2000 Performance Tuning by MS Press: "You can improve performance in some cases by locating nonclustered indexes on separate physical disks from the data tables themselves--especially for heavily used indexes on large tables. This is because the index data can be accessed in parallel with the table data pages, so that while the index is accessed by one transaction, another transaction can be accessing the data pages simultaneously".