We have a database on SQL Server 2008 R2 which contains two Full text catalogs. DB Files exist on separate drive as per below information.
Drive D has 1) Main Data File - .mdf (size 80 GB) 2) Secondary Data file - Catalog1.ndf (size 370 MB) 2) another Secondary Data file - Catalog2.ndf (size 5 MB)
Drive E has 1) Log file - .ldf (Size 30 GB)
Question - 1) Is it worth to put Index catelog files (two .ndf files) to separate drives let's say Drive F? Can I get performance improvement by doing this? 2) Generally, should we put index files on separate drive while designing and allocating SQL drive specification?
1) Yes, for our production servers, we have seprate drive for Index Cataloge files. 2) Putting index on seprate files from data files, is not a common practice as it doesn't help to improve performance. 3) You must care about TempDB, it should not be on C drive
(Tara Kizer has much better in hand experience and could answer in more better way. Wait for her answer) -------------------------- http://connectsql.blogspot.com/