We are redesigning the database and table schemas. For a disk setup we have the hard drive and a Raid 5 array comprising an E drive. We currently have transaction logs on the hard drive, system databases (and subsequently all system tables) on the hard dr
ive, and all our user defined databases (and subsequently all user defined tables) on the E drive.
The server under discussion contains only one processor.
The user defined database itself is rather small, at about 100MB. It contains a clustered index, one date column index, and three columns separately indexed as foreign keys. Additionally, there is an image column. The table has both, almost equally, inser
ts and updates, few deletes. It is also read heavily, the clustered index most, followed by the three foreign key indexed columns, and then the least using the date column.
We have one filegroup, the default Primary. Currently, our user defined tables and indexes are all on the Primary filegroup. As I understand filegroups (as per Books Online) as well as other internet sources, it may be advantageous to create a separate fi
le group for indexes, and even the image column. Given our limited resources, and our current set up of a hard drive, Raid 5 Array [E Drive], and a single processor, do you have any recommendations?
Message posted via http://www.sqlmonster.com
Robert,
We went with placing the system data in the PRIMARY filegroup and created a
DATA filegroup making it the default. This way we separated the user data
from the system data for admin purposes. I know that this not really what
you had asked for but it was something we did.
Chris Wood
Alberta Department of Energy
CANADA
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:38366e87041d4bc2a888439965f48bee@.SQLMonster.c om...
>I am trying to understand filegroups under our environment and how it might
>best be leveraged. We are running SQL 2K.
> We are redesigning the database and table schemas. For a disk setup we
> have the hard drive and a Raid 5 array comprising an E drive. We currently
> have transaction logs on the hard drive, system databases (and
> subsequently all system tables) on the hard drive, and all our user
> defined databases (and subsequently all user defined tables) on the E
> drive.
> The server under discussion contains only one processor.
> The user defined database itself is rather small, at about 100MB. It
> contains a clustered index, one date column index, and three columns
> separately indexed as foreign keys. Additionally, there is an image
> column. The table has both, almost equally, inserts and updates, few
> deletes. It is also read heavily, the clustered index most, followed by
> the three foreign key indexed columns, and then the least using the date
> column.
> We have one filegroup, the default Primary. Currently, our user defined
> tables and indexes are all on the Primary filegroup. As I understand
> filegroups (as per Books Online) as well as other internet sources, it may
> be advantageous to create a separate file group for indexes, and even the
> image column. Given our limited resources, and our current set up of a
> hard drive, Raid 5 Array [E Drive], and a single processor, do you have
> any recommendations?
> --
> Message posted via http://www.sqlmonster.com
|||Separate filegroups can give you better performance. I would separate out
the data files into a filegroup, non-clustered indexes into their own
filegroup, and the transaction logs into their own filegroup.
The advantage to this comes primarily from a system with multiple processors
and multiple hard drives. In a multiple-processor system, SQL Server can
access tables and associated non-clustered indexes in parallel. Filegroups
on separate hard drives (particularly the transaction log) can help reduce
head thrashing and associated performance degradation.
If you have all your filegroups on the same physical hard drive, the drive
head is going to slow you down a bit since it will have to jump from reading
your index to writing data in your table to updating the transaction log and
so on. This may not be as big an issue with a small database though, since
caching will help.
Depending on the amount of reads to writes you're performing, you might look
at a different RAID configuration. RAID 5 is horrible for transaction logs.
The reason is that RAID 5 takes about twice the number of disk I/Os for
every write.
I would recommend, at the very least, that you get your transaction logs on
their own separate physical hard drive (it could be RAID 1 or RAID 1+0, but
RAID 5 is not recommended). If you have the option, I would also get an
additional hard drive and create a separate filegroup on it for your
non-clustered indexes.
Thanks,
Michael C#, MCDBA
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:38366e87041d4bc2a888439965f48bee@.SQLMonster.c om...
> I am trying to understand filegroups under our environment and how it
might best be leveraged. We are running SQL 2K.
> We are redesigning the database and table schemas. For a disk setup we
have the hard drive and a Raid 5 array comprising an E drive. We currently
have transaction logs on the hard drive, system databases (and subsequently
all system tables) on the hard drive, and all our user defined databases
(and subsequently all user defined tables) on the E drive.
> The server under discussion contains only one processor.
> The user defined database itself is rather small, at about 100MB. It
contains a clustered index, one date column index, and three columns
separately indexed as foreign keys. Additionally, there is an image column.
The table has both, almost equally, inserts and updates, few deletes. It is
also read heavily, the clustered index most, followed by the three foreign
key indexed columns, and then the least using the date column.
> We have one filegroup, the default Primary. Currently, our user defined
tables and indexes are all on the Primary filegroup. As I understand
filegroups (as per Books Online) as well as other internet sources, it may
be advantageous to create a separate file group for indexes, and even the
image column. Given our limited resources, and our current set up of a hard
drive, Raid 5 Array [E Drive], and a single processor, do you have any
recommendations?
> --
> Message posted via http://www.sqlmonster.com
|||Just wanted to clarify some things you mentioned.
> Separate filegroups can give you better performance. I would separate out
> the data files into a filegroup, non-clustered indexes into their own
> filegroup, and the transaction logs into their own filegroup.
> The advantage to this comes primarily from a system with multiple
> processors
> and multiple hard drives. In a multiple-processor system, SQL Server can
> access tables and associated non-clustered indexes in parallel.
Actually with SQL 2000 you do not need separate files in order for SQL
Server to read them with multiple threads. That was true in SQL 7.0 but no
longer so in 2000. Even with 7.0 you don't need multiple filegroups, just
multiple files. With a smaller db and a single drive array there is no
performance gain by creating separate files. It is more of a maintenance
aspect or if you plan on adding another drive array in the future it may
make things easier.
> filegroup, and the transaction logs into their own filegroup.
Log files don't actually have a filegroup, they are simply individual files.
Andrew J. Kelly SQL MVP
"Michael C" <me@.mine.com> wrote in message
news:eWpFzH0$EHA.3256@.TK2MSFTNGP11.phx.gbl...
> Separate filegroups can give you better performance. I would separate out
> the data files into a filegroup, non-clustered indexes into their own
> filegroup, and the transaction logs into their own filegroup.
> The advantage to this comes primarily from a system with multiple
> processors
> and multiple hard drives. In a multiple-processor system, SQL Server can
> access tables and associated non-clustered indexes in parallel.
> Filegroups
> on separate hard drives (particularly the transaction log) can help reduce
> head thrashing and associated performance degradation.
> If you have all your filegroups on the same physical hard drive, the drive
> head is going to slow you down a bit since it will have to jump from
> reading
> your index to writing data in your table to updating the transaction log
> and
> so on. This may not be as big an issue with a small database though,
> since
> caching will help.
> Depending on the amount of reads to writes you're performing, you might
> look
> at a different RAID configuration. RAID 5 is horrible for transaction
> logs.
> The reason is that RAID 5 takes about twice the number of disk I/Os for
> every write.
> I would recommend, at the very least, that you get your transaction logs
> on
> their own separate physical hard drive (it could be RAID 1 or RAID 1+0,
> but
> RAID 5 is not recommended). If you have the option, I would also get an
> additional hard drive and create a separate filegroup on it for your
> non-clustered indexes.
> Thanks,
> Michael C#, MCDBA
> "Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in
> message
> news:38366e87041d4bc2a888439965f48bee@.SQLMonster.c om...
> might best be leveraged. We are running SQL 2K.
> have the hard drive and a Raid 5 array comprising an E drive. We currently
> have transaction logs on the hard drive, system databases (and
> subsequently
> all system tables) on the hard drive, and all our user defined databases
> (and subsequently all user defined tables) on the E drive.
> contains a clustered index, one date column index, and three columns
> separately indexed as foreign keys. Additionally, there is an image
> column.
> The table has both, almost equally, inserts and updates, few deletes. It
> is
> also read heavily, the clustered index most, followed by the three foreign
> key indexed columns, and then the least using the date column.
> tables and indexes are all on the Primary filegroup. As I understand
> filegroups (as per Books Online) as well as other internet sources, it may
> be advantageous to create a separate file group for indexes, and even the
> image column. Given our limited resources, and our current set up of a
> hard
> drive, Raid 5 Array [E Drive], and a single processor, do you have any
> recommendations?
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23BNKsB1$EHA.3472@.TK2MSFTNGP14.phx.gbl...
> Just wanted to clarify some things you mentioned.
>
> Actually with SQL 2000 you do not need separate files in order for SQL
> Server to read them with multiple threads. That was true in SQL 7.0 but
> no longer so in 2000. Even with 7.0 you don't need multiple filegroups,
> just multiple files. With a smaller db and a single drive array there is
> no performance gain by creating separate files. It is more of a
> maintenance aspect or if you plan on adding another drive array in the
> future it may make things easier.
>
You're absolutely right. I was typing faster than I was thinking. Sorry
about that. You're absolutely right about the separate drives; and ideally,
separate controllers for those drives. I tried to bring that point through.
The physical non-linear movement of the hard drive heads is a big
performance killers, especially when you're mixing the non-linear data read
and write operations with constant jumps for the linear log write operations
on one drive.
Having multiple parallel threads on separate processors doesn't help as much
if your single hard drive is thrashing like crazy. Again, for small
databases this might be alleviated somewhat by caching. But I think it
depends primarily on the ratio of read operations vs. write operations in
your particular database (I think I've seen the 'average' quoted at around
7:1 [reads:writes] somewhere).
>
> Log files don't actually have a filegroup, they are simply individual
> files.
>
Again, typing faster than I was thinking. I was actually on my way out to
the local bar with my co-workers when I decided to try to get an answer out
to the OP on this one.
Beer on the brain. The log files should,whenever possible, be on their own hard drive was the point I was trying to
get across.
Thanks for the corrections,
Michael C#, MCDBA
>
> --
> Andrew J. Kelly SQL MVP
|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Michael C#" <xyz@.abcdef.com> wrote in message
news:ly_Hd.8296$mF.6862@.fe08.lga...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23BNKsB1$EHA.3472@.TK2MSFTNGP14.phx.gbl...
> You're absolutely right. I was typing faster than I was thinking. Sorry
> about that. You're absolutely right about the separate drives; and
> ideally, separate controllers for those drives. I tried to bring that
> point through. The physical non-linear movement of the hard drive heads is
> a big performance killers, especially when you're mixing the non-linear
> data read and write operations with constant jumps for the linear log
> write operations on one drive.
> Having multiple parallel threads on separate processors doesn't help as
> much if your single hard drive is thrashing like crazy. Again, for small
> databases this might be alleviated somewhat by caching. But I think it
> depends primarily on the ratio of read operations vs. write operations in
> your particular database (I think I've seen the 'average' quoted at around
> 7:1 [reads:writes] somewhere).
>
> Again, typing faster than I was thinking. I was actually on my way out to
> the local bar with my co-workers when I decided to try to get an answer
> out to the OP on this one.
Beer on the brain. The log files should,> whenever possible, be on their own hard drive was the point I was trying
> to get across.
> Thanks for the corrections,
> Michael C#, MCDBA
>
>
No comments:
Post a Comment