Placing of Indexes on seperate data file

Is it good practise to put large nonclustered of frequently queried tables o
n a seperate datafile to the actual table? I.e. a file using a different dis
k/s.
Thanks
www.SQLporn.co.ukThis was the best case, especially in DB2. I would suggest that in SQL Serv
er it is generally not the case, especially if you choose your storage syste
m carefully. (such as raid 1+0)|||It's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk