From: Gary Johnson on
Our application records transactional events (maybe 300K to 500K rows per
day) on a separate file group called HISTORY. I was wondering if there are
performance considerations when creating indices on this history table.
That is, do I get a performance improvement if the index resides in the
default file group or if it resides in my HISTORY file group.

Thanks in advance,
Gary

From: yssr83 on
Hi ary,

You can create a seprate file for the indexes, that would help as the
index read/writes would be from a seperate file.

Sriram
www.sqllike.com
From: Dan Guzman on
> Our application records transactional events (maybe 300K to 500K rows per
> day) on a separate file group called HISTORY. I was wondering if there
> are
> performance considerations when creating indices on this history table.
> That is, do I get a performance improvement if the index resides in the
> default file group or if it resides in my HISTORY file group.

Separate filegroups for data and index can improve index create performance
if the underlying files reside on different spindles. From an operational
performance perspective, it depends on how data are accessed. Separate
filegroups for data and index can improve performance if you can isolate
sequential and random I/O on separate physical disks. OTOH, separate
filegroups will won't improve performance if the files reside on the same
spindles or your workload is such that sequential and random I/O can't be
segregated.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/