Prev: Case statement strangeness
Next: 0xC0207015 error in SSIS but not DTS
From: Gary Johnson on 26 Feb 2010 14:06
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 27 Feb 2010 06:42
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 27 Feb 2010 13:39
> 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/


 | 
Pages: 1
Prev: Case statement strangeness
Next: 0xC0207015 error in SSIS but not DTS