I'm currently running a msde 2.8 server on a pentium 4 2.4 with 512 megs of memory and it is slow for client computers. What would you suggest for a server Memory, processor, mirror hard drives?
The software I am running is a Point of sale software package. The server is currently running on one of the registers and I'm plan on installing a back office server. Any advise would help greatly.All you can get?
Without knowing a LOT more about what you are doing, what problems you've had, what you want to add, etc, we're pretty hard pressed to give you simple answers!
-PatP|||The current problems that I am having is. When a item is scanned it takes 30 seconds to come back with a product. I am wanting to run a windows 2003 server running msde and in the future switch it to sql 2005. My database is around 130 megs and growing. In 2 years it will be around 1gig or larger. I have 2 clients currently but may grow up to 5 clients.|||Typically the fetch of an SKU (roughly the same thing as what you are doing) takes about 275 ms using a full blown copy of SQL Server. It shouldn't be a bunch slower for MSDE unless you are running into connection throttling somehow (MSDE only permits at most 5 running threads at one time).
Are you certain that the delay is really happening inside MSDE, and not somewhere else in your system?
-PatP|||All you can get?
Without knowing a LOT more about what you are doing, what problems you've had, what you want to add, etc, we're pretty hard pressed to give you simple answers!
-PatP
That never stopped us before...
Let's see...the database is 130mb...you have 512mb...
I would say the whole damn thing is in memory...
SO...if you eliminate the fact that there's probably no I/O, AND I'm assuming that the thing is indexed properly....
I'd go with the thought that the code sucks...
oh...hold on...what else is running on the box?|||There is nothing else running on the system. The point of sale software is also running on the machine. I would like to adventually have that machine act as a virus server.|||Poke around in the Task Manager a bit. See if anything there attracts your attention. Be sure to check to see how much memory various processes are using, how much memory the machine has used, and how busy the CPU is.
Are you familiar with Performance Monitor? It can help a bunch trying to diagnose this kind of problem.
-PatP|||SQLserver uses 150 - 200 megs of memory. Not much cpu usage.|||If that is the case, it looks like SQL Server isn't your culprit. If it was, the CPU would be higher (for CPU bound tasks), or both the CPU and the RAM would be higher (for disk bound tasks).
My guess is that the problem lies either in the application itself, the network (especially likely if modems or wireless is involved), or on the client machine.
We still haven't nailed down the culprit, but I don't think that MSDE is your weak link in this case.
-PatP|||Well I just tested it by scanning some items. It uses about 30%-70% of the processor. This is running on a windows 2003 with sql server 2000. The hardware is a xeon 2.4 512 megs of memory. This machine is very effecient and works like a wal-mart. It comes up as fast as you can scan. But uses a lot of the processor. My other sites like I said our pentium 4 with 512 running xp pro and msde 2.8.
Thanks for all the help so far|||So the question is what should I roll out for a server to replace the desktop p4 machines? Also is there something I should be looking at in sql itself? The % of processor was with only 2 machines scanning items which is not very uncommon.|||The biggest problem is that MSDE is intended to be a run-time package for applications developed with Microsoft Visual Studio. You are sorely hampered trying to figure out what your performance problem is due to an almost total lack of tools.
The official gospel from Microsoft is that you need to report the problem to your application vendor, and have them either re-tune your database for you or sell/re-sell you the tools to allow you to tune it yourself. Once you get a full copy of SQL Server (even the BackOffice version which is somewhat limited compared to the full-featured versions), you'll be much better equipped to deal with this kind of problem.
Right now you are kind of like the merchant that buys a vending machine that is serviced by the seller... You have no keys, no tools, no access to the inside of the machine, and all you can do is report malfunctions to your vendor... A nice, simple, black-box arrangement where you can't monkey with things even should you want to!
-PatP|||REally?
With that said, I wonder how many developers deploy this solution...
I was just starting to head down this path as well...
What about MS Access and MSDE?|||MSDE gives the end user OSQL.EXE and BCP.EXE, but not much more... No Enterprise Mangler, no Query Analyzer, no Profiler. This is plenty for the folks who want a "data appliance", but doesn't give you much in the way of tuning tools.
I'm pretty sure that the Developer Edition of MS-Access allows run-time distribution of MSDE, but I haven't checked the licensing on it lately.
-PatP|||a 130MB database.. is much of it in only a few tables? I'm wondering if it really is indexed properly.. A product lookup is basically just a 'select' query right? maybe a few joins or such..
Also, there are some free management utilities for people without enterprise manager.
Check out http://www.aspfaq.com/show.asp?id=2442
Some cost money, but not nearly as much as any real version of sql server.|||It is only a simple query. SKU, Product description, and In store upc. I just don't understand why my processor on my servers are hit so hard
Showing posts with label suggest. Show all posts
Showing posts with label suggest. Show all posts
Monday, March 12, 2012
Wednesday, March 7, 2012
Hard drives
Can anybody suggest a hdd configuration that offers the best performance and
reliability for a SQL server?
I just finished deploying SQL 2000 Cluster.
I spent a solid week researching RAID configurations along with Microsoft
recommended cofigurations.
The settings that I came up with for the Drives are as follows.
Database is written across a RAID 1+0 across six drives. Each pair is being
written to by 2 different controllers.
Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
pair is written to by 2 different controllers.
The RAID 10 provides the highest Availability for the database, since
multiple drive failures across mirrored sets will not effect the Virtual Disk
as much as a RAID 0+1.
The RAID 0+1 will provide a slightly faster write to disk then the RAID10
due to striping of the spanned disks.
Depending on the hardware of course this should be a pretty optimal setting
for your cluster...again I am running in a clustered SQL environment
therefore I am looking for MAX reliability first, then Good performance.
Cheers
John
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>
|||Hi Downes -
Please find the following link :-
http://www.sql-server-performance.co...are_tuning.asp
Hope this will help you to get the solution you looking for
Regards,
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>
|||John E Davis wrote:
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with
> Microsoft recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is
> being written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again
> each pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the
> Virtual Disk as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the
> RAID10 due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal
> setting for your cluster...again I am running in a clustered SQL
> environment therefore I am looking for MAX reliability first, then
> Good performance.
> Cheers
> John
John,
Can you explain the difference between RAID 1+0 and RAID 10. I thought
they were one and the same. That is, a mirrored, striped set.
David
|||Ouch - expensive!
It's all a trade-off, the better the performance the more it'll cost.
If you're using a SAN (as opposed to direct attached storage), chances
are the RAID group will be used by more than just the SQL box (that's
what usually happens in most companies anyway), even by multiple SQL
boxes, so getting the best disk config is usually less of a concern than
getting LUNs on RAID groups that aren't used by other busy servers (busy
in terms of IO).
So unless you can afford RAID groups dedicated just to your SQL box then
I wouldn't worry too much about it. But as John say, yes, RAID 0+1 is
sweet but RAID 5 usually will suffice as the read performance is pretty
good. The write performance on RAID 5 is not so good but usually
there's much more read activity than write activity on SQL DBs - you've
got to see what's more common/important (read vs write) and it's a trade
off between performance, cost & available space (RAID 5 allows much more
usable disk space than RAID 0+1 for the money you spend). Typically,
RAID 5 for data & RAID 0+1 for transaction logs works well.
If you're after the best performance, check out the Microsoft TPC-C
benchmark reports on their high-end configurations. The ones I've read
all use RAID 0 (striping) to get the best read & write performance for
the number of disks (and therefore raw $ price) in their arrays but, of
course, RAID 0 offer exactly zero fault tolerance.
Cheers,
Mike.
John E Davis wrote:[vbcol=seagreen]
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with Microsoft
> recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is being
> written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
> pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the Virtual Disk
> as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the RAID10
> due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal setting
> for your cluster...again I am running in a clustered SQL environment
> therefore I am looking for MAX reliability first, then Good performance.
> Cheers
> John
> "Peter Downes" wrote:
>
|||David Gugick wrote:
> John E Davis wrote:
>
[snip]
[vbcol=seagreen]
[snip]
>
> John,
> Can you explain the difference between RAID 1+0 and RAID 10. I thought
> they were one and the same. That is, a mirrored, striped set.
>
Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
reasonable summary of RAID levels (although I notice they don't have
RAID 50). There are probably better resources, but that was a 10 second
google search. Essentially, it's the difference between a stripe of
mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
same in my book but there are probably subtle differences. As I
mentioned in my other post in this thread, I think getting to that level
of detail for this subject is really splitting straws since other things
like server contention is going to play a bigger role in the IO
performance. That's my 2c worth.
Cheers,
Mike.
|||Another (more graphical) description of RAID levels is:
RAID 10: http://www.acnc.com/04_01_10.html
RAID 0+1: http://www.acnc.com/04_01_0_1.html
It's pretty nice actually.
Cheers,
Mike.
Mike Hodgson wrote:
> David Gugick wrote:
>
> [snip]
>
> [snip]
>
> Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
> reasonable summary of RAID levels (although I notice they don't have
> RAID 50). There are probably better resources, but that was a 10 second
> google search. Essentially, it's the difference between a stripe of
> mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
> same in my book but there are probably subtle differences. As I
> mentioned in my other post in this thread, I think getting to that level
> of detail for this subject is really splitting straws since other things
> like server contention is going to play a bigger role in the IO
> performance. That's my 2c worth.
> Cheers,
> Mike.
|||Peter
Were I work we have very strict standards on how the disk are used. We set
up the following disks for the following uses
'C' drive this is used by the windows operating system
'D' drive this is where we install SQL Server and have the system databases
'E' drive is where we have the user databases
'F' drive is where we have the transaction logs
'G' drive is where we keep our backups
'H' drive is available for application use
The way those logical drives are mapped to phyisical drives varies depending
on how many drives are available, what size databases you are going to have
etc, but we always try to have at least three seperate arrays. that way you
can keep the user databases, transaction logs and backups seperate from each
other.
We insist that all disks be 1+ 0 RAID unless write activity is less than 10%
of disk I/O in which case RAID 5 can be used.
Hope this helps
John
"surajits" wrote:
[vbcol=seagreen]
> Hi Downes -
> Please find the following link :-
> http://www.sql-server-performance.co...are_tuning.asp
> Hope this will help you to get the solution you looking for
> Regards,
> "Peter Downes" wrote:
reliability for a SQL server?
I just finished deploying SQL 2000 Cluster.
I spent a solid week researching RAID configurations along with Microsoft
recommended cofigurations.
The settings that I came up with for the Drives are as follows.
Database is written across a RAID 1+0 across six drives. Each pair is being
written to by 2 different controllers.
Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
pair is written to by 2 different controllers.
The RAID 10 provides the highest Availability for the database, since
multiple drive failures across mirrored sets will not effect the Virtual Disk
as much as a RAID 0+1.
The RAID 0+1 will provide a slightly faster write to disk then the RAID10
due to striping of the spanned disks.
Depending on the hardware of course this should be a pretty optimal setting
for your cluster...again I am running in a clustered SQL environment
therefore I am looking for MAX reliability first, then Good performance.
Cheers
John
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>
|||Hi Downes -
Please find the following link :-
http://www.sql-server-performance.co...are_tuning.asp
Hope this will help you to get the solution you looking for
Regards,
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>
|||John E Davis wrote:
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with
> Microsoft recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is
> being written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again
> each pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the
> Virtual Disk as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the
> RAID10 due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal
> setting for your cluster...again I am running in a clustered SQL
> environment therefore I am looking for MAX reliability first, then
> Good performance.
> Cheers
> John
John,
Can you explain the difference between RAID 1+0 and RAID 10. I thought
they were one and the same. That is, a mirrored, striped set.
David
|||Ouch - expensive!
It's all a trade-off, the better the performance the more it'll cost.
If you're using a SAN (as opposed to direct attached storage), chances
are the RAID group will be used by more than just the SQL box (that's
what usually happens in most companies anyway), even by multiple SQL
boxes, so getting the best disk config is usually less of a concern than
getting LUNs on RAID groups that aren't used by other busy servers (busy
in terms of IO).
So unless you can afford RAID groups dedicated just to your SQL box then
I wouldn't worry too much about it. But as John say, yes, RAID 0+1 is
sweet but RAID 5 usually will suffice as the read performance is pretty
good. The write performance on RAID 5 is not so good but usually
there's much more read activity than write activity on SQL DBs - you've
got to see what's more common/important (read vs write) and it's a trade
off between performance, cost & available space (RAID 5 allows much more
usable disk space than RAID 0+1 for the money you spend). Typically,
RAID 5 for data & RAID 0+1 for transaction logs works well.
If you're after the best performance, check out the Microsoft TPC-C
benchmark reports on their high-end configurations. The ones I've read
all use RAID 0 (striping) to get the best read & write performance for
the number of disks (and therefore raw $ price) in their arrays but, of
course, RAID 0 offer exactly zero fault tolerance.
Cheers,
Mike.
John E Davis wrote:[vbcol=seagreen]
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with Microsoft
> recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is being
> written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
> pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the Virtual Disk
> as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the RAID10
> due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal setting
> for your cluster...again I am running in a clustered SQL environment
> therefore I am looking for MAX reliability first, then Good performance.
> Cheers
> John
> "Peter Downes" wrote:
>
|||David Gugick wrote:
> John E Davis wrote:
>
[snip]
[vbcol=seagreen]
[snip]
>
> John,
> Can you explain the difference between RAID 1+0 and RAID 10. I thought
> they were one and the same. That is, a mirrored, striped set.
>
Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
reasonable summary of RAID levels (although I notice they don't have
RAID 50). There are probably better resources, but that was a 10 second
google search. Essentially, it's the difference between a stripe of
mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
same in my book but there are probably subtle differences. As I
mentioned in my other post in this thread, I think getting to that level
of detail for this subject is really splitting straws since other things
like server contention is going to play a bigger role in the IO
performance. That's my 2c worth.
Cheers,
Mike.
|||Another (more graphical) description of RAID levels is:
RAID 10: http://www.acnc.com/04_01_10.html
RAID 0+1: http://www.acnc.com/04_01_0_1.html
It's pretty nice actually.
Cheers,
Mike.
Mike Hodgson wrote:
> David Gugick wrote:
>
> [snip]
>
> [snip]
>
> Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
> reasonable summary of RAID levels (although I notice they don't have
> RAID 50). There are probably better resources, but that was a 10 second
> google search. Essentially, it's the difference between a stripe of
> mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
> same in my book but there are probably subtle differences. As I
> mentioned in my other post in this thread, I think getting to that level
> of detail for this subject is really splitting straws since other things
> like server contention is going to play a bigger role in the IO
> performance. That's my 2c worth.
> Cheers,
> Mike.
|||Peter
Were I work we have very strict standards on how the disk are used. We set
up the following disks for the following uses
'C' drive this is used by the windows operating system
'D' drive this is where we install SQL Server and have the system databases
'E' drive is where we have the user databases
'F' drive is where we have the transaction logs
'G' drive is where we keep our backups
'H' drive is available for application use
The way those logical drives are mapped to phyisical drives varies depending
on how many drives are available, what size databases you are going to have
etc, but we always try to have at least three seperate arrays. that way you
can keep the user databases, transaction logs and backups seperate from each
other.
We insist that all disks be 1+ 0 RAID unless write activity is less than 10%
of disk I/O in which case RAID 5 can be used.
Hope this helps
John
"surajits" wrote:
[vbcol=seagreen]
> Hi Downes -
> Please find the following link :-
> http://www.sql-server-performance.co...are_tuning.asp
> Hope this will help you to get the solution you looking for
> Regards,
> "Peter Downes" wrote:
Hard drives
Can anybody suggest a hdd configuration that offers the best performance and
reliability for a SQL server?I just finished deploying SQL 2000 Cluster.
I spent a solid week researching RAID configurations along with Microsoft
recommended cofigurations.
The settings that I came up with for the Drives are as follows.
Database is written across a RAID 1+0 across six drives. Each pair is being
written to by 2 different controllers.
Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
pair is written to by 2 different controllers.
The RAID 10 provides the highest Availability for the database, since
multiple drive failures across mirrored sets will not effect the Virtual Disk
as much as a RAID 0+1.
The RAID 0+1 will provide a slightly faster write to disk then the RAID10
due to striping of the spanned disks.
Depending on the hardware of course this should be a pretty optimal setting
for your cluster...again I am running in a clustered SQL environment
therefore I am looking for MAX reliability first, then Good performance.
Cheers
John
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>|||Hi Downes -
Please find the following link :-
http://www.sql-server-performance.com/hardware_tuning.asp
Hope this will help you to get the solution you looking for
Regards,
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>|||John E Davis wrote:
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with
> Microsoft recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is
> being written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again
> each pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the
> Virtual Disk as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the
> RAID10 due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal
> setting for your cluster...again I am running in a clustered SQL
> environment therefore I am looking for MAX reliability first, then
> Good performance.
> Cheers
> John
John,
Can you explain the difference between RAID 1+0 and RAID 10. I thought
they were one and the same. That is, a mirrored, striped set.
David|||Ouch - expensive!
It's all a trade-off, the better the performance the more it'll cost.
If you're using a SAN (as opposed to direct attached storage), chances
are the RAID group will be used by more than just the SQL box (that's
what usually happens in most companies anyway), even by multiple SQL
boxes, so getting the best disk config is usually less of a concern than
getting LUNs on RAID groups that aren't used by other busy servers (busy
in terms of IO).
So unless you can afford RAID groups dedicated just to your SQL box then
I wouldn't worry too much about it. But as John say, yes, RAID 0+1 is
sweet but RAID 5 usually will suffice as the read performance is pretty
good. The write performance on RAID 5 is not so good but usually
there's much more read activity than write activity on SQL DBs - you've
got to see what's more common/important (read vs write) and it's a trade
off between performance, cost & available space (RAID 5 allows much more
usable disk space than RAID 0+1 for the money you spend). Typically,
RAID 5 for data & RAID 0+1 for transaction logs works well.
If you're after the best performance, check out the Microsoft TPC-C
benchmark reports on their high-end configurations. The ones I've read
all use RAID 0 (striping) to get the best read & write performance for
the number of disks (and therefore raw $ price) in their arrays but, of
course, RAID 0 offer exactly zero fault tolerance.
Cheers,
Mike.
John E Davis wrote:
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with Microsoft
> recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is being
> written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
> pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the Virtual Disk
> as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the RAID10
> due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal setting
> for your cluster...again I am running in a clustered SQL environment
> therefore I am looking for MAX reliability first, then Good performance.
> Cheers
> John
> "Peter Downes" wrote:
>
>>Can anybody suggest a hdd configuration that offers the best performance and
>>reliability for a SQL server?
>>|||David Gugick wrote:
> John E Davis wrote:
>
[snip]
>> The RAID 10 provides the highest Availability for the database, since
>> multiple drive failures across mirrored sets will not effect the
>> Virtual Disk as much as a RAID 0+1.
>> The RAID 0+1 will provide a slightly faster write to disk then the
>> RAID10 due to striping of the spanned disks.
[snip]
>
> John,
> Can you explain the difference between RAID 1+0 and RAID 10. I thought
> they were one and the same. That is, a mirrored, striped set.
>
Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
reasonable summary of RAID levels (although I notice they don't have
RAID 50). There are probably better resources, but that was a 10 second
google search. Essentially, it's the difference between a stripe of
mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
same in my book but there are probably subtle differences. As I
mentioned in my other post in this thread, I think getting to that level
of detail for this subject is really splitting straws since other things
like server contention is going to play a bigger role in the IO
performance. That's my 2c worth.
Cheers,
Mike.|||Another (more graphical) description of RAID levels is:
RAID 10: http://www.acnc.com/04_01_10.html
RAID 0+1: http://www.acnc.com/04_01_0_1.html
It's pretty nice actually. :)
Cheers,
Mike.
Mike Hodgson wrote:
> David Gugick wrote:
>> John E Davis wrote:
> [snip]
>> The RAID 10 provides the highest Availability for the database, since
>> multiple drive failures across mirrored sets will not effect the
>> Virtual Disk as much as a RAID 0+1.
>> The RAID 0+1 will provide a slightly faster write to disk then the
>> RAID10 due to striping of the spanned disks.
>
> [snip]
>>
>> John,
>> Can you explain the difference between RAID 1+0 and RAID 10. I thought
>> they were one and the same. That is, a mirrored, striped set.
> Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
> reasonable summary of RAID levels (although I notice they don't have
> RAID 50). There are probably better resources, but that was a 10 second
> google search. Essentially, it's the difference between a stripe of
> mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
> same in my book but there are probably subtle differences. As I
> mentioned in my other post in this thread, I think getting to that level
> of detail for this subject is really splitting straws since other things
> like server contention is going to play a bigger role in the IO
> performance. That's my 2c worth.
> Cheers,
> Mike.|||Peter
Were I work we have very strict standards on how the disk are used. We set
up the following disks for the following uses
'C' drive this is used by the windows operating system
'D' drive this is where we install SQL Server and have the system databases
'E' drive is where we have the user databases
'F' drive is where we have the transaction logs
'G' drive is where we keep our backups
'H' drive is available for application use
The way those logical drives are mapped to phyisical drives varies depending
on how many drives are available, what size databases you are going to have
etc, but we always try to have at least three seperate arrays. that way you
can keep the user databases, transaction logs and backups seperate from each
other.
We insist that all disks be 1+ 0 RAID unless write activity is less than 10%
of disk I/O in which case RAID 5 can be used.
Hope this helps
John
"surajits" wrote:
> Hi Downes -
> Please find the following link :-
> http://www.sql-server-performance.com/hardware_tuning.asp
> Hope this will help you to get the solution you looking for
> Regards,
> "Peter Downes" wrote:
> > Can anybody suggest a hdd configuration that offers the best performance and
> > reliability for a SQL server?
> >
> >
> >
reliability for a SQL server?I just finished deploying SQL 2000 Cluster.
I spent a solid week researching RAID configurations along with Microsoft
recommended cofigurations.
The settings that I came up with for the Drives are as follows.
Database is written across a RAID 1+0 across six drives. Each pair is being
written to by 2 different controllers.
Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
pair is written to by 2 different controllers.
The RAID 10 provides the highest Availability for the database, since
multiple drive failures across mirrored sets will not effect the Virtual Disk
as much as a RAID 0+1.
The RAID 0+1 will provide a slightly faster write to disk then the RAID10
due to striping of the spanned disks.
Depending on the hardware of course this should be a pretty optimal setting
for your cluster...again I am running in a clustered SQL environment
therefore I am looking for MAX reliability first, then Good performance.
Cheers
John
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>|||Hi Downes -
Please find the following link :-
http://www.sql-server-performance.com/hardware_tuning.asp
Hope this will help you to get the solution you looking for
Regards,
"Peter Downes" wrote:
> Can anybody suggest a hdd configuration that offers the best performance and
> reliability for a SQL server?
>
>|||John E Davis wrote:
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with
> Microsoft recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is
> being written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again
> each pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the
> Virtual Disk as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the
> RAID10 due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal
> setting for your cluster...again I am running in a clustered SQL
> environment therefore I am looking for MAX reliability first, then
> Good performance.
> Cheers
> John
John,
Can you explain the difference between RAID 1+0 and RAID 10. I thought
they were one and the same. That is, a mirrored, striped set.
David|||Ouch - expensive!
It's all a trade-off, the better the performance the more it'll cost.
If you're using a SAN (as opposed to direct attached storage), chances
are the RAID group will be used by more than just the SQL box (that's
what usually happens in most companies anyway), even by multiple SQL
boxes, so getting the best disk config is usually less of a concern than
getting LUNs on RAID groups that aren't used by other busy servers (busy
in terms of IO).
So unless you can afford RAID groups dedicated just to your SQL box then
I wouldn't worry too much about it. But as John say, yes, RAID 0+1 is
sweet but RAID 5 usually will suffice as the read performance is pretty
good. The write performance on RAID 5 is not so good but usually
there's much more read activity than write activity on SQL DBs - you've
got to see what's more common/important (read vs write) and it's a trade
off between performance, cost & available space (RAID 5 allows much more
usable disk space than RAID 0+1 for the money you spend). Typically,
RAID 5 for data & RAID 0+1 for transaction logs works well.
If you're after the best performance, check out the Microsoft TPC-C
benchmark reports on their high-end configurations. The ones I've read
all use RAID 0 (striping) to get the best read & write performance for
the number of disks (and therefore raw $ price) in their arrays but, of
course, RAID 0 offer exactly zero fault tolerance.
Cheers,
Mike.
John E Davis wrote:
> I just finished deploying SQL 2000 Cluster.
> I spent a solid week researching RAID configurations along with Microsoft
> recommended cofigurations.
> The settings that I came up with for the Drives are as follows.
> Database is written across a RAID 1+0 across six drives. Each pair is being
> written to by 2 different controllers.
> Transaction Logs are written across a RAID 0+1 across 4 drives. Again each
> pair is written to by 2 different controllers.
> The RAID 10 provides the highest Availability for the database, since
> multiple drive failures across mirrored sets will not effect the Virtual Disk
> as much as a RAID 0+1.
> The RAID 0+1 will provide a slightly faster write to disk then the RAID10
> due to striping of the spanned disks.
> Depending on the hardware of course this should be a pretty optimal setting
> for your cluster...again I am running in a clustered SQL environment
> therefore I am looking for MAX reliability first, then Good performance.
> Cheers
> John
> "Peter Downes" wrote:
>
>>Can anybody suggest a hdd configuration that offers the best performance and
>>reliability for a SQL server?
>>|||David Gugick wrote:
> John E Davis wrote:
>
[snip]
>> The RAID 10 provides the highest Availability for the database, since
>> multiple drive failures across mirrored sets will not effect the
>> Virtual Disk as much as a RAID 0+1.
>> The RAID 0+1 will provide a slightly faster write to disk then the
>> RAID10 due to striping of the spanned disks.
[snip]
>
> John,
> Can you explain the difference between RAID 1+0 and RAID 10. I thought
> they were one and the same. That is, a mirrored, striped set.
>
Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
reasonable summary of RAID levels (although I notice they don't have
RAID 50). There are probably better resources, but that was a 10 second
google search. Essentially, it's the difference between a stripe of
mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
same in my book but there are probably subtle differences. As I
mentioned in my other post in this thread, I think getting to that level
of detail for this subject is really splitting straws since other things
like server contention is going to play a bigger role in the IO
performance. That's my 2c worth.
Cheers,
Mike.|||Another (more graphical) description of RAID levels is:
RAID 10: http://www.acnc.com/04_01_10.html
RAID 0+1: http://www.acnc.com/04_01_0_1.html
It's pretty nice actually. :)
Cheers,
Mike.
Mike Hodgson wrote:
> David Gugick wrote:
>> John E Davis wrote:
> [snip]
>> The RAID 10 provides the highest Availability for the database, since
>> multiple drive failures across mirrored sets will not effect the
>> Virtual Disk as much as a RAID 0+1.
>> The RAID 0+1 will provide a slightly faster write to disk then the
>> RAID10 due to striping of the spanned disks.
>
> [snip]
>>
>> John,
>> Can you explain the difference between RAID 1+0 and RAID 10. I thought
>> they were one and the same. That is, a mirrored, striped set.
> Webopedia (http://www.webopedia.com/TERM/R/RAID.html) provides a
> reasonable summary of RAID levels (although I notice they don't have
> RAID 50). There are probably better resources, but that was a 10 second
> google search. Essentially, it's the difference between a stripe of
> mirrors (RAID 10) or a mirror of stripes (RAID 0+1). Pretty much the
> same in my book but there are probably subtle differences. As I
> mentioned in my other post in this thread, I think getting to that level
> of detail for this subject is really splitting straws since other things
> like server contention is going to play a bigger role in the IO
> performance. That's my 2c worth.
> Cheers,
> Mike.|||Peter
Were I work we have very strict standards on how the disk are used. We set
up the following disks for the following uses
'C' drive this is used by the windows operating system
'D' drive this is where we install SQL Server and have the system databases
'E' drive is where we have the user databases
'F' drive is where we have the transaction logs
'G' drive is where we keep our backups
'H' drive is available for application use
The way those logical drives are mapped to phyisical drives varies depending
on how many drives are available, what size databases you are going to have
etc, but we always try to have at least three seperate arrays. that way you
can keep the user databases, transaction logs and backups seperate from each
other.
We insist that all disks be 1+ 0 RAID unless write activity is less than 10%
of disk I/O in which case RAID 5 can be used.
Hope this helps
John
"surajits" wrote:
> Hi Downes -
> Please find the following link :-
> http://www.sql-server-performance.com/hardware_tuning.asp
> Hope this will help you to get the solution you looking for
> Regards,
> "Peter Downes" wrote:
> > Can anybody suggest a hdd configuration that offers the best performance and
> > reliability for a SQL server?
> >
> >
> >
Subscribe to:
Posts (Atom)