Hello Professionals.
We have implemented the RAID 5 so what would be best performance tips for
separating the log and datafiles ? and any other tips for performance would
be highly appreciated.
Thanks
Hi,
How many Disk controllers you have in your server?. If you have multiple
controllers then you can keep MDF and LDF files
seperately in each controller. based on the access you can also create
seperate filegroups to keep the indexes. This will share the I/O
between controllers.
See the below site for Hardware tuning tips (See I/O part):-
http://www.sql-server-performance.co...are_tuning.asp
Thanks
Hari
SQL Server MVP
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:D477C1B8-8B65-4184-897B-D16B338A0EA7@.microsoft.com...
> Hello Professionals.
> We have implemented the RAID 5 so what would be best performance tips for
> separating the log and datafiles ? and any other tips for performance
> would
> be highly appreciated.
> Thanks
sql
Showing posts with label raid. Show all posts
Showing posts with label raid. Show all posts
Wednesday, March 21, 2012
Filegroups, CPU, & RAID
Not sure if this is the right forum...
I have a table that has its own filegroup on a dedicated RAID 10 array.
Somewhere in Books Online I read that you should create a file in the
filegroup for each CPU in the server. My understanding is that in SQL 2000,
this triggered the server to spawn multiple threads to handle the I/O. My
questions are:
1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
2 - If not, being on a RAID 10 array, does it help in any way to have
multiple files in a group?
Thanks,
-- Brian"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
>
It doesn't matter. You don't want multiple threads for IO hitting a single
volume. One thread is quite enough to saturate your IO channel. A second
thread issuing IO requests over the channel isn't going to improve
performance.
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
>
Multiple files per filegroup and RAID striping both accomplish the same
thing, and there is no need to use both. Both are mechanisms to spread IO
operations targeting a single table or index over a number of spindles.
David|||The "filegroup/file per CPU" rule went out with SQL 7.0. SQL 2000 will spin
up more IO threads as necessary until the controller is saturated. Multiple
files may make it easier to manage moving files due to later growth but will
give zero performance increase if the files are on the same underlying RAID
container. I personally prefer to keep files sizes doen to 100GB or smaller
on all but the largest systems.
There are some "File/CPU" considerations for tempdb for both SQL 2000 and
SQL 2005, but those apply on only the highest volume systems.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
> Thanks,
> -- Brian
>sql
I have a table that has its own filegroup on a dedicated RAID 10 array.
Somewhere in Books Online I read that you should create a file in the
filegroup for each CPU in the server. My understanding is that in SQL 2000,
this triggered the server to spawn multiple threads to handle the I/O. My
questions are:
1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
2 - If not, being on a RAID 10 array, does it help in any way to have
multiple files in a group?
Thanks,
-- Brian"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
>
It doesn't matter. You don't want multiple threads for IO hitting a single
volume. One thread is quite enough to saturate your IO channel. A second
thread issuing IO requests over the channel isn't going to improve
performance.
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
>
Multiple files per filegroup and RAID striping both accomplish the same
thing, and there is no need to use both. Both are mechanisms to spread IO
operations targeting a single table or index over a number of spindles.
David|||The "filegroup/file per CPU" rule went out with SQL 7.0. SQL 2000 will spin
up more IO threads as necessary until the controller is saturated. Multiple
files may make it easier to manage moving files due to later growth but will
give zero performance increase if the files are on the same underlying RAID
container. I personally prefer to keep files sizes doen to 100GB or smaller
on all but the largest systems.
There are some "File/CPU" considerations for tempdb for both SQL 2000 and
SQL 2005, but those apply on only the highest volume systems.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
> Thanks,
> -- Brian
>sql
Filegroups, CPU, & RAID
Not sure if this is the right forum...
I have a table that has its own filegroup on a dedicated RAID 10 array.
Somewhere in Books Online I read that you should create a file in the
filegroup for each CPU in the server. My understanding is that in SQL 2000,
this triggered the server to spawn multiple threads to handle the I/O. My
questions are:
1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
2 - If not, being on a RAID 10 array, does it help in any way to have
multiple files in a group?
Thanks,
-- Brian"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
>
It doesn't matter. You don't want multiple threads for IO hitting a single
volume. One thread is quite enough to saturate your IO channel. A second
thread issuing IO requests over the channel isn't going to improve
performance.
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
>
Multiple files per filegroup and RAID striping both accomplish the same
thing, and there is no need to use both. Both are mechanisms to spread IO
operations targeting a single table or index over a number of spindles.
David|||The "filegroup/file per CPU" rule went out with SQL 7.0. SQL 2000 will spin
up more IO threads as necessary until the controller is saturated. Multiple
files may make it easier to manage moving files due to later growth but will
give zero performance increase if the files are on the same underlying RAID
container. I personally prefer to keep files sizes doen to 100GB or smaller
on all but the largest systems.
There are some "File/CPU" considerations for tempdb for both SQL 2000 and
SQL 2005, but those apply on only the highest volume systems.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
> Thanks,
> -- Brian
>
I have a table that has its own filegroup on a dedicated RAID 10 array.
Somewhere in Books Online I read that you should create a file in the
filegroup for each CPU in the server. My understanding is that in SQL 2000,
this triggered the server to spawn multiple threads to handle the I/O. My
questions are:
1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
2 - If not, being on a RAID 10 array, does it help in any way to have
multiple files in a group?
Thanks,
-- Brian"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
>
It doesn't matter. You don't want multiple threads for IO hitting a single
volume. One thread is quite enough to saturate your IO channel. A second
thread issuing IO requests over the channel isn't going to improve
performance.
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
>
Multiple files per filegroup and RAID striping both accomplish the same
thing, and there is no need to use both. Both are mechanisms to spread IO
operations targeting a single table or index over a number of spindles.
David|||The "filegroup/file per CPU" rule went out with SQL 7.0. SQL 2000 will spin
up more IO threads as necessary until the controller is saturated. Multiple
files may make it easier to manage moving files due to later growth but will
give zero performance increase if the files are on the same underlying RAID
container. I personally prefer to keep files sizes doen to 100GB or smaller
on all but the largest systems.
There are some "File/CPU" considerations for tempdb for both SQL 2000 and
SQL 2005, but those apply on only the highest volume systems.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
> Thanks,
> -- Brian
>
Monday, March 19, 2012
Filegroups and RAID 1+0
Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#
http://msdn.microsoft.com/library/en...es_02_2ak3.asp
David Portas
SQL Server MVP
|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegr oups.com...
> http://msdn.microsoft.com/library/en...es_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#
http://msdn.microsoft.com/library/en...es_02_2ak3.asp
David Portas
SQL Server MVP
|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegr oups.com...
> http://msdn.microsoft.com/library/en...es_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>
Filegroups and RAID 1+0
Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#http://msdn.microsoft.com/library/e...des_02_2ak3.asp
--
David Portas
SQL Server MVP
--|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegroups.com...
> http://msdn.microsoft.com/library/e...des_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#http://msdn.microsoft.com/library/e...des_02_2ak3.asp
--
David Portas
SQL Server MVP
--|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegroups.com...
> http://msdn.microsoft.com/library/e...des_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>
Filegroups and RAID 1+0
Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2ak3.asp
--
David Portas
SQL Server MVP
--|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegroups.com...
> http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2ak3.asp
--
David Portas
SQL Server MVP
--|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegroups.com...
> http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>
Filegroups and multi-processor threading improvements
I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin HammondHi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin HammondHi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>
Filegroups and multi-processor threading improvements
I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin HammondHi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin HammondHi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>
Filegroups and multi-processor threading improvements
I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin Hammond
Hi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>
parallel I/O is a mute issue if you setup a proper disk sub-system, at least
for small to medium sized databases.
But, it seems like everyone is skimming over the SMP benefits of filegroups,
if they exist. I came across one other posting referring to the number of
threads that SQL 2000 will use per filegroup.
Let me see if I understand this correctly? SQL 2000 will issue one thread
per filegroup for reads and writes. Thus if you have one large database on
a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
should thread across both CPU's at the RAID controller level. If you create
two PRIMARY filegroups for the database, then you SQL 2000 will dish out two
threads for disk I/O thus using two of your CPU's. These will both be
issued to the RAID controller which should thread out your disk I/O once
again. So, if my understanding is correct, will there be a performance gain
by creating one filegroup per CPU?
Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
RAID 0 for logs, but this is a read-only SQL server so logs are irrelevant.
This also limits us from doing any filegroup optimizations such as placing
the indexes on another disk sub-system. So the only performance benefit we
are looking at is potential better SMP handling of disk I/O.
Thank You,
Kevin Hammond
Hi Kevin,
I'm afraid your understanding is completely incorrect.
You do not have to have more files to get better IO performance, except when
using backup/restore. Multiple files are generally used for easier
management.
The access methods code is totally oblivious to the number of files in all
respects. Likewise, lazy-writing and checkpointing are both indifferent to
the number of files. SQL Server can have hundreds of outstanding read and/or
writes per file, not just one. More file does not equal more IO.
Please let me know if you have any further questions.
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Hammond" <kghammond@.nrscorp.com> wrote in message
news:c8ak86$6m9$1@.grandcanyon.binc.net...
> I have read extensively on filegroups vs. RAID 5, 10 etc. Seems like
> parallel I/O is a mute issue if you setup a proper disk sub-system, at
least
> for small to medium sized databases.
> But, it seems like everyone is skimming over the SMP benefits of
filegroups,
> if they exist. I came across one other posting referring to the number of
> threads that SQL 2000 will use per filegroup.
> Let me see if I understand this correctly? SQL 2000 will issue one thread
> per filegroup for reads and writes. Thus if you have one large database
on
> a RAID 10 setup, I/O will be limited to one CPU thread. Granted disk I/O
> should thread across both CPU's at the RAID controller level. If you
create
> two PRIMARY filegroups for the database, then you SQL 2000 will dish out
two
> threads for disk I/O thus using two of your CPU's. These will both be
> issued to the RAID controller which should thread out your disk I/O once
> again. So, if my understanding is correct, will there be a performance
gain
> by creating one filegroup per CPU?
> Since in my case, we have two sets of disks RAID 10 and RAID 0, we already
> our optimizing our disk I/O with tempbd and logs on the RAID 0. I know,
> RAID 0 for logs, but this is a read-only SQL server so logs are
irrelevant.
> This also limits us from doing any filegroup optimizations such as placing
> the indexes on another disk sub-system. So the only performance benefit
we
> are looking at is potential better SMP handling of disk I/O.
> Thank You,
> Kevin Hammond
>
Filegroups
Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second filegroup than the "default" one and put here one of the 2 most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table that cause most problems and the Raid 1 i would put the other table. Any opinions? Any suggestions?
Thanxs
Cl=E1udiaYou should always keep the log on a different drive than the data...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Cláudia" <Alphaseusa@.hotmail.com> wrote in message
news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise
performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second
filegroup than the "default" one and put here one of the 2
most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table
that cause most problems and the Raid 1 i would put the
other table. Any opinions? Any suggestions?
Thanxs
Cláudia|||Claudia,
I'd recommend keeping all your data files off of the log files drive.
Putting data files on the same drive as your log files will greatly slow
down access to the log files.
--
Denny Cherry
DBA
GameSpy Industries
"Cláudia" <Alphaseusa@.hotmail.com> wrote in message
news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise
performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second
filegroup than the "default" one and put here one of the 2
most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table
that cause most problems and the Raid 1 i would put the
other table. Any opinions? Any suggestions?
Thanxs
Cláudia|||Claudia
The guys are right about log files, for OLTP, but as you said your's is OLAP which can be different. If you are not updating the database or you are doing it at a time when no-users are on the system and there is a large time window, the impact of the transaction logs being on the same drives as the data is reduced to a level where if you can increase performance, during the time users are on the system by spreading the data more, then don't worry about the log location.
You say you have 3 hard drives, I assume that is at the logical level, bit hard to have a one drive raid 5. The main thing that worries me about your proposed use is you say one drive is raid 1 and one is raid 5 that you propose to put the data on. It is not a good idea to mix the raid levels your data files are on.
If you resolve your raid levels, I see no harm in trying to spread the load.
Regards
John
>--Original Message--
>Claudia,
>I'd recommend keeping all your data files off of the log files drive.
>Putting data files on the same drive as your log files will greatly slow
>down access to the log files.
>-- >Denny Cherry
>DBA
>GameSpy Industries
>"Cl=E1udia" <Alphaseusa@.hotmail.com> wrote in message
>news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
>Question:
>I have 3 hard disks: C, D and E
>C contains OS information (Raid 1).
>I was thinking of doing this ( in order to optimise
>performace of my SQL Server):
>P.S Its an OLAP server
>D: (Raid 1) put all the log files here + create a second
>filegroup than the "default" one and put here one of the 2
>most heavy queried tables
>E: put all the data files in one filegroup (Raid 5)
>I was thinking that in the Raid 5 i would put the table
>that cause most problems and the Raid 1 i would put the
>other table. Any opinions? Any suggestions?
>Thanxs
>Cl=E1udia
>
>.
>|||Claudia
Would be easier if you had 8, but lets see what we can do. I don't know the size of your disks or your database, so this might not work.
I am assuming you are a 'classic' olap data warehousing system and not some kind of hybrid.
Leave the operating system as it is, two disks RAID 1. Configure 4 disks as RAID 0 + 1 and put the databases on there. Despite saying you can put the transaction logs on the same disk, put it on the remaining disk with no RAID. (If you had an extra disk, I would have said 6 disk RAID 0+1 and put the lot on it.
Before anyone says, what no RAID for the logs, I was assuming 'classic' olap where the logs are not used, usually the database is set to simple recovery (or trauncate log on checkpoint for 6.5 or 7). For what I see as 'classic' olap it usually quicker and easier to rebuild the database from source than have a backup/DR plan, so no backups and no logs.
Not enough info to know if this would be suitable, what do you think?
Regards
John
>--Original Message--
>I have 7 hard disks:
>2 -- raid 1
>2 -- raid 1
>3 -- raid 5
>any ideas on how to do it best to optimize performance?
>>--Original Message--
>>Claudia
>>The guys are right about log files, for OLTP, but as you >>said your's is OLAP which can be different. If you are >not >>updating the database or you are doing it at a time when >>no-users are on the system and there is a large time >>window, the impact of the transaction logs being on the >>same drives as the data is reduced to a level where if >you >>can increase performance, during the time users are on >the >>system by spreading the data more, then don't worry about >>the log location.
>>You say you have 3 hard drives, I assume that is at the >>logical level, bit hard to have a one drive raid 5. The >>main thing that worries me about your proposed use is you >>say one drive is raid 1 and one is raid 5 that you >propose >>to put the data on. It is not a good idea to mix the raid >>levels your data files are on.
>>If you resolve your raid levels, I see no harm in trying >>to spread the load.
>>Regards
>>John
>>
>>--Original Message--
>>Claudia,
>>I'd recommend keeping all your data files off of the log >>files drive.
>>Putting data files on the same drive as your log files >>will greatly slow
>>down access to the log files.
>>-- >>Denny Cherry
>>DBA
>>GameSpy Industries
>>"Cl=E1udia" <Alphaseusa@.hotmail.com> wrote in message
>>news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
>>Question:
>>I have 3 hard disks: C, D and E
>>C contains OS information (Raid 1).
>>I was thinking of doing this ( in order to optimise
>>performace of my SQL Server):
>>P.S Its an OLAP server
>>D: (Raid 1) put all the log files here + create a second
>>filegroup than the "default" one and put here one of the >2
>>most heavy queried tables
>>E: put all the data files in one filegroup (Raid 5)
>>I was thinking that in the Raid 5 i would put the table
>>that cause most problems and the Raid 1 i would put the
>>other table. Any opinions? Any suggestions?
>>Thanxs
>>Cl=E1udia
>>
>>.
>>.
>.
>
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second filegroup than the "default" one and put here one of the 2 most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table that cause most problems and the Raid 1 i would put the other table. Any opinions? Any suggestions?
Thanxs
Cl=E1udiaYou should always keep the log on a different drive than the data...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Cláudia" <Alphaseusa@.hotmail.com> wrote in message
news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise
performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second
filegroup than the "default" one and put here one of the 2
most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table
that cause most problems and the Raid 1 i would put the
other table. Any opinions? Any suggestions?
Thanxs
Cláudia|||Claudia,
I'd recommend keeping all your data files off of the log files drive.
Putting data files on the same drive as your log files will greatly slow
down access to the log files.
--
Denny Cherry
DBA
GameSpy Industries
"Cláudia" <Alphaseusa@.hotmail.com> wrote in message
news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise
performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second
filegroup than the "default" one and put here one of the 2
most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table
that cause most problems and the Raid 1 i would put the
other table. Any opinions? Any suggestions?
Thanxs
Cláudia|||Claudia
The guys are right about log files, for OLTP, but as you said your's is OLAP which can be different. If you are not updating the database or you are doing it at a time when no-users are on the system and there is a large time window, the impact of the transaction logs being on the same drives as the data is reduced to a level where if you can increase performance, during the time users are on the system by spreading the data more, then don't worry about the log location.
You say you have 3 hard drives, I assume that is at the logical level, bit hard to have a one drive raid 5. The main thing that worries me about your proposed use is you say one drive is raid 1 and one is raid 5 that you propose to put the data on. It is not a good idea to mix the raid levels your data files are on.
If you resolve your raid levels, I see no harm in trying to spread the load.
Regards
John
>--Original Message--
>Claudia,
>I'd recommend keeping all your data files off of the log files drive.
>Putting data files on the same drive as your log files will greatly slow
>down access to the log files.
>-- >Denny Cherry
>DBA
>GameSpy Industries
>"Cl=E1udia" <Alphaseusa@.hotmail.com> wrote in message
>news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
>Question:
>I have 3 hard disks: C, D and E
>C contains OS information (Raid 1).
>I was thinking of doing this ( in order to optimise
>performace of my SQL Server):
>P.S Its an OLAP server
>D: (Raid 1) put all the log files here + create a second
>filegroup than the "default" one and put here one of the 2
>most heavy queried tables
>E: put all the data files in one filegroup (Raid 5)
>I was thinking that in the Raid 5 i would put the table
>that cause most problems and the Raid 1 i would put the
>other table. Any opinions? Any suggestions?
>Thanxs
>Cl=E1udia
>
>.
>|||Claudia
Would be easier if you had 8, but lets see what we can do. I don't know the size of your disks or your database, so this might not work.
I am assuming you are a 'classic' olap data warehousing system and not some kind of hybrid.
Leave the operating system as it is, two disks RAID 1. Configure 4 disks as RAID 0 + 1 and put the databases on there. Despite saying you can put the transaction logs on the same disk, put it on the remaining disk with no RAID. (If you had an extra disk, I would have said 6 disk RAID 0+1 and put the lot on it.
Before anyone says, what no RAID for the logs, I was assuming 'classic' olap where the logs are not used, usually the database is set to simple recovery (or trauncate log on checkpoint for 6.5 or 7). For what I see as 'classic' olap it usually quicker and easier to rebuild the database from source than have a backup/DR plan, so no backups and no logs.
Not enough info to know if this would be suitable, what do you think?
Regards
John
>--Original Message--
>I have 7 hard disks:
>2 -- raid 1
>2 -- raid 1
>3 -- raid 5
>any ideas on how to do it best to optimize performance?
>>--Original Message--
>>Claudia
>>The guys are right about log files, for OLTP, but as you >>said your's is OLAP which can be different. If you are >not >>updating the database or you are doing it at a time when >>no-users are on the system and there is a large time >>window, the impact of the transaction logs being on the >>same drives as the data is reduced to a level where if >you >>can increase performance, during the time users are on >the >>system by spreading the data more, then don't worry about >>the log location.
>>You say you have 3 hard drives, I assume that is at the >>logical level, bit hard to have a one drive raid 5. The >>main thing that worries me about your proposed use is you >>say one drive is raid 1 and one is raid 5 that you >propose >>to put the data on. It is not a good idea to mix the raid >>levels your data files are on.
>>If you resolve your raid levels, I see no harm in trying >>to spread the load.
>>Regards
>>John
>>
>>--Original Message--
>>Claudia,
>>I'd recommend keeping all your data files off of the log >>files drive.
>>Putting data files on the same drive as your log files >>will greatly slow
>>down access to the log files.
>>-- >>Denny Cherry
>>DBA
>>GameSpy Industries
>>"Cl=E1udia" <Alphaseusa@.hotmail.com> wrote in message
>>news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
>>Question:
>>I have 3 hard disks: C, D and E
>>C contains OS information (Raid 1).
>>I was thinking of doing this ( in order to optimise
>>performace of my SQL Server):
>>P.S Its an OLAP server
>>D: (Raid 1) put all the log files here + create a second
>>filegroup than the "default" one and put here one of the >2
>>most heavy queried tables
>>E: put all the data files in one filegroup (Raid 5)
>>I was thinking that in the Raid 5 i would put the table
>>that cause most problems and the Raid 1 i would put the
>>other table. Any opinions? Any suggestions?
>>Thanxs
>>Cl=E1udia
>>
>>.
>>.
>.
>
Friday, March 9, 2012
Filegroup - data file question
We just recieved a RAID setup and i would like to know the best setup for my
data files.
We have a 60GB database. What would be the setup on a RAID with 5 disks?My question wasn't on which RAID to select. I understand that portion. My
question was filegroups and data files for my database?
I want to know the purpose of file groups when creating a database.
thanks for the help though.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OpxNjNrfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> You still didn't say anything as to how you will use it, how many
> transaction etc. 50% writes is nothing to sneeze at but a lot depends on
> how large the updates are and how many per second. If your only doing a
> handful a second then it won't matter as much as if your doing 1000 a sec.
> A Raid 5 might get by on the lower end but if you are going to heavily use
> the system then you might want to consider a RAID 0+1 or 1+0.
Unfortunately
> you only have 5 disks and that would leave just a 4 disk Raid with only 2
> usable as far as size goes. You didn't say how large your drives are but
if
> they are only 36GB or so then this would not be enough space for a Raid
0+1
> or 1+0. Your only option may be to use Raid 5.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Richard King (BSM)" <google@.walkersca.com> wrote in message
> news:OcausAnfDHA.956@.TK2MSFTNGP09.phx.gbl...
> > 70%-50% Reads advantage
> >
> > OS will be on own drive as well as logs.
> >
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OpatO9lfDHA.2152@.tk2msftngp13.phx.gbl...
> > > How are you going to use it? How many transactions, writes vs reads,
> > large
> > > or small selects etc. Will the logs be on a separate drive array? Is
> the
> > > OS on a separate array as well?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Richard King (SCA)" <google@.walkersca.com> wrote in message
> > > news:OyaVo9kfDHA.3284@.tk2msftngp13.phx.gbl...
> > > > We just recieved a RAID setup and i would like to know the best
setup
> > for
> > > my
> > > > data files.
> > > >
> > > > We have a 60GB database. What would be the setup on a RAID with 5
> > disks?
> > > >
> > > >
> > >
> > >
> >
> >
>|||OK. Filegroups are mainly used to segregate data for one of two reasons.
One is for backup reasons. If you have a very large database you can do
backups at the filegroup level so you don't have to do it all at once.
Usually for terabyte range dbs. Another is that it allows you to place
database objects (tables and indexes) on different drives or drive array's.
In your case you will only have 1 array for the data and the db won't be
that large. You probably won't gain much by having multiple filegroups.
Again on larger db's and very heavy usage one it may be beneficial to have
multiple files to allow lots of parallel threads to read them at once. But
you generally need the higher level hardware to take advantage of that.
BooksOnline has some more details on Files and Filegroups that you may want
to take a look at.
--
Andrew J. Kelly
SQL Server MVP
"Richard King (BSM)" <google@.walkersca.com> wrote in message
news:OYe6MrwfDHA.616@.TK2MSFTNGP11.phx.gbl...
> My question wasn't on which RAID to select. I understand that portion.
My
> question was filegroups and data files for my database?
> I want to know the purpose of file groups when creating a database.
> thanks for the help though.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OpxNjNrfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> > You still didn't say anything as to how you will use it, how many
> > transaction etc. 50% writes is nothing to sneeze at but a lot depends
on
> > how large the updates are and how many per second. If your only doing a
> > handful a second then it won't matter as much as if your doing 1000 a
sec.
> > A Raid 5 might get by on the lower end but if you are going to heavily
use
> > the system then you might want to consider a RAID 0+1 or 1+0.
> Unfortunately
> > you only have 5 disks and that would leave just a 4 disk Raid with only
2
> > usable as far as size goes. You didn't say how large your drives are
but
> if
> > they are only 36GB or so then this would not be enough space for a Raid
> 0+1
> > or 1+0. Your only option may be to use Raid 5.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Richard King (BSM)" <google@.walkersca.com> wrote in message
> > news:OcausAnfDHA.956@.TK2MSFTNGP09.phx.gbl...
> > > 70%-50% Reads advantage
> > >
> > > OS will be on own drive as well as logs.
> > >
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OpatO9lfDHA.2152@.tk2msftngp13.phx.gbl...
> > > > How are you going to use it? How many transactions, writes vs
reads,
> > > large
> > > > or small selects etc. Will the logs be on a separate drive array?
Is
> > the
> > > > OS on a separate array as well?
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Richard King (SCA)" <google@.walkersca.com> wrote in message
> > > > news:OyaVo9kfDHA.3284@.tk2msftngp13.phx.gbl...
> > > > > We just recieved a RAID setup and i would like to know the best
> setup
> > > for
> > > > my
> > > > > data files.
> > > > >
> > > > > We have a 60GB database. What would be the setup on a RAID with 5
> > > disks?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
data files.
We have a 60GB database. What would be the setup on a RAID with 5 disks?My question wasn't on which RAID to select. I understand that portion. My
question was filegroups and data files for my database?
I want to know the purpose of file groups when creating a database.
thanks for the help though.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OpxNjNrfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> You still didn't say anything as to how you will use it, how many
> transaction etc. 50% writes is nothing to sneeze at but a lot depends on
> how large the updates are and how many per second. If your only doing a
> handful a second then it won't matter as much as if your doing 1000 a sec.
> A Raid 5 might get by on the lower end but if you are going to heavily use
> the system then you might want to consider a RAID 0+1 or 1+0.
Unfortunately
> you only have 5 disks and that would leave just a 4 disk Raid with only 2
> usable as far as size goes. You didn't say how large your drives are but
if
> they are only 36GB or so then this would not be enough space for a Raid
0+1
> or 1+0. Your only option may be to use Raid 5.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Richard King (BSM)" <google@.walkersca.com> wrote in message
> news:OcausAnfDHA.956@.TK2MSFTNGP09.phx.gbl...
> > 70%-50% Reads advantage
> >
> > OS will be on own drive as well as logs.
> >
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OpatO9lfDHA.2152@.tk2msftngp13.phx.gbl...
> > > How are you going to use it? How many transactions, writes vs reads,
> > large
> > > or small selects etc. Will the logs be on a separate drive array? Is
> the
> > > OS on a separate array as well?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Richard King (SCA)" <google@.walkersca.com> wrote in message
> > > news:OyaVo9kfDHA.3284@.tk2msftngp13.phx.gbl...
> > > > We just recieved a RAID setup and i would like to know the best
setup
> > for
> > > my
> > > > data files.
> > > >
> > > > We have a 60GB database. What would be the setup on a RAID with 5
> > disks?
> > > >
> > > >
> > >
> > >
> >
> >
>|||OK. Filegroups are mainly used to segregate data for one of two reasons.
One is for backup reasons. If you have a very large database you can do
backups at the filegroup level so you don't have to do it all at once.
Usually for terabyte range dbs. Another is that it allows you to place
database objects (tables and indexes) on different drives or drive array's.
In your case you will only have 1 array for the data and the db won't be
that large. You probably won't gain much by having multiple filegroups.
Again on larger db's and very heavy usage one it may be beneficial to have
multiple files to allow lots of parallel threads to read them at once. But
you generally need the higher level hardware to take advantage of that.
BooksOnline has some more details on Files and Filegroups that you may want
to take a look at.
--
Andrew J. Kelly
SQL Server MVP
"Richard King (BSM)" <google@.walkersca.com> wrote in message
news:OYe6MrwfDHA.616@.TK2MSFTNGP11.phx.gbl...
> My question wasn't on which RAID to select. I understand that portion.
My
> question was filegroups and data files for my database?
> I want to know the purpose of file groups when creating a database.
> thanks for the help though.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OpxNjNrfDHA.2352@.TK2MSFTNGP12.phx.gbl...
> > You still didn't say anything as to how you will use it, how many
> > transaction etc. 50% writes is nothing to sneeze at but a lot depends
on
> > how large the updates are and how many per second. If your only doing a
> > handful a second then it won't matter as much as if your doing 1000 a
sec.
> > A Raid 5 might get by on the lower end but if you are going to heavily
use
> > the system then you might want to consider a RAID 0+1 or 1+0.
> Unfortunately
> > you only have 5 disks and that would leave just a 4 disk Raid with only
2
> > usable as far as size goes. You didn't say how large your drives are
but
> if
> > they are only 36GB or so then this would not be enough space for a Raid
> 0+1
> > or 1+0. Your only option may be to use Raid 5.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Richard King (BSM)" <google@.walkersca.com> wrote in message
> > news:OcausAnfDHA.956@.TK2MSFTNGP09.phx.gbl...
> > > 70%-50% Reads advantage
> > >
> > > OS will be on own drive as well as logs.
> > >
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OpatO9lfDHA.2152@.tk2msftngp13.phx.gbl...
> > > > How are you going to use it? How many transactions, writes vs
reads,
> > > large
> > > > or small selects etc. Will the logs be on a separate drive array?
Is
> > the
> > > > OS on a separate array as well?
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Richard King (SCA)" <google@.walkersca.com> wrote in message
> > > > news:OyaVo9kfDHA.3284@.tk2msftngp13.phx.gbl...
> > > > > We just recieved a RAID setup and i would like to know the best
> setup
> > > for
> > > > my
> > > > > data files.
> > > > >
> > > > > We have a 60GB database. What would be the setup on a RAID with 5
> > > disks?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
file will not attach to SQL reason file is not primary file
I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?
mdf file size 5,738,944 KB
ldf file size 10,176 KB
You need the primary file to attach this. I guess your last resort will be your backups|||There's another file out there somewhere - probably another mdf. Make sure you've got them all and the attach should work.|||Thanks the file looks to be corupted. the customer did not run any backup so I guess the information is lost. thanks again for your help.
file will not attach to SQL reason file is not primary file
I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?
mdf file size 5,738,944 KB
ldf file size 10,176 KB
You need the primary file to attach this. I guess your last resort will be your backups|||There's another file out there somewhere - probably another mdf. Make sure you've got them all and the attach should work.|||Thanks the file looks to be corupted. the customer did not run any backup so I guess the information is lost. thanks again for your help.
Sunday, February 26, 2012
File Size Best Pratice
Hi,
There may be a FAQ covering this. If so, please point that to me.
I have data about the size of 40GB to be loaded onto a RAID configuration
for SQL Server 2000 on Windows Server 2003. Should I use one database
file of the size 40GB, 10 database files (each about 4GB), or 20 database
files (each about 2GB)?
What is the best practice for this? What are the pros and cons?
Thanks,
Wenbin ZhangThere is no official best practice that I know of. But with a DB of only
40GB a single file should be fine. SQL Server 2000 can use multiple threads
to read the same file and the Raid will also split the data between multiple
physical drives as well. You can certainly add multiple files if you like
but the more you add the harder it gets to maintain them. And with a DB that
small you probably don't have much to gain by adding more files unless you
want to isolate tables or indexes onto separate filegroups.
--
Andrew J. Kelly SQL MVP
"Wenbin Zhang" <wzhang@.onesource.com> wrote in message
news:uHk3xKh3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There may be a FAQ covering this. If so, please point that to me.
> I have data about the size of 40GB to be loaded onto a RAID configuration
> for SQL Server 2000 on Windows Server 2003. Should I use one database
> file of the size 40GB, 10 database files (each about 4GB), or 20 database
> files (each about 2GB)?
> What is the best practice for this? What are the pros and cons?
> Thanks,
> Wenbin Zhang
>
There may be a FAQ covering this. If so, please point that to me.
I have data about the size of 40GB to be loaded onto a RAID configuration
for SQL Server 2000 on Windows Server 2003. Should I use one database
file of the size 40GB, 10 database files (each about 4GB), or 20 database
files (each about 2GB)?
What is the best practice for this? What are the pros and cons?
Thanks,
Wenbin ZhangThere is no official best practice that I know of. But with a DB of only
40GB a single file should be fine. SQL Server 2000 can use multiple threads
to read the same file and the Raid will also split the data between multiple
physical drives as well. You can certainly add multiple files if you like
but the more you add the harder it gets to maintain them. And with a DB that
small you probably don't have much to gain by adding more files unless you
want to isolate tables or indexes onto separate filegroups.
--
Andrew J. Kelly SQL MVP
"Wenbin Zhang" <wzhang@.onesource.com> wrote in message
news:uHk3xKh3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There may be a FAQ covering this. If so, please point that to me.
> I have data about the size of 40GB to be loaded onto a RAID configuration
> for SQL Server 2000 on Windows Server 2003. Should I use one database
> file of the size 40GB, 10 database files (each about 4GB), or 20 database
> files (each about 2GB)?
> What is the best practice for this? What are the pros and cons?
> Thanks,
> Wenbin Zhang
>
File Size Best Pratice
Hi,
There may be a FAQ covering this. If so, please point that to me.
I have data about the size of 40GB to be loaded onto a RAID configuration
for SQL Server 2000 on Windows Server 2003. Should I use one database
file of the size 40GB, 10 database files (each about 4GB), or 20 database
files (each about 2GB)?
What is the best practice for this? What are the pros and cons?
Thanks,
Wenbin Zhang
There is no official best practice that I know of. But with a DB of only
40GB a single file should be fine. SQL Server 2000 can use multiple threads
to read the same file and the Raid will also split the data between multiple
physical drives as well. You can certainly add multiple files if you like
but the more you add the harder it gets to maintain them. And with a DB that
small you probably don't have much to gain by adding more files unless you
want to isolate tables or indexes onto separate filegroups.
Andrew J. Kelly SQL MVP
"Wenbin Zhang" <wzhang@.onesource.com> wrote in message
news:uHk3xKh3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There may be a FAQ covering this. If so, please point that to me.
> I have data about the size of 40GB to be loaded onto a RAID configuration
> for SQL Server 2000 on Windows Server 2003. Should I use one database
> file of the size 40GB, 10 database files (each about 4GB), or 20 database
> files (each about 2GB)?
> What is the best practice for this? What are the pros and cons?
> Thanks,
> Wenbin Zhang
>
There may be a FAQ covering this. If so, please point that to me.
I have data about the size of 40GB to be loaded onto a RAID configuration
for SQL Server 2000 on Windows Server 2003. Should I use one database
file of the size 40GB, 10 database files (each about 4GB), or 20 database
files (each about 2GB)?
What is the best practice for this? What are the pros and cons?
Thanks,
Wenbin Zhang
There is no official best practice that I know of. But with a DB of only
40GB a single file should be fine. SQL Server 2000 can use multiple threads
to read the same file and the Raid will also split the data between multiple
physical drives as well. You can certainly add multiple files if you like
but the more you add the harder it gets to maintain them. And with a DB that
small you probably don't have much to gain by adding more files unless you
want to isolate tables or indexes onto separate filegroups.
Andrew J. Kelly SQL MVP
"Wenbin Zhang" <wzhang@.onesource.com> wrote in message
news:uHk3xKh3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There may be a FAQ covering this. If so, please point that to me.
> I have data about the size of 40GB to be loaded onto a RAID configuration
> for SQL Server 2000 on Windows Server 2003. Should I use one database
> file of the size 40GB, 10 database files (each about 4GB), or 20 database
> files (each about 2GB)?
> What is the best practice for this? What are the pros and cons?
> Thanks,
> Wenbin Zhang
>
File placement for optimal performance?
What is the best performance for this configuration:
Files:
Data
Log
Indexes
tempdb
Disk:
A - RAID 10
B - RAID 10 (or should this be RAID 1?)
Whats best?:
A - Data and Indexes
B - tempdb and Log
? Thanks.
check these links
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
http://sql-server-performance.com/Community/forums/t/2337.aspx
Madhu
Subscribe to:
Posts (Atom)