Sample db problem: impact

Showing posts with label impact. Show all posts

Saturday, February 25, 2012

SAN impact on performance

We're going to be hosting a write intensive non-clustered application. With
cost constraints in place, we are down to two options:
Dell 2950 with 8 disks: 2 disks in RAID 1 for OS, 2 disk in RAID 1 for
tempdb/log files, and 4 disks in RAID 10 for the data files.
Dell 2950 with 8 disks and SAN: 2 disks in RAID 1 for OS, 2 disks in RAID 1
for tempdb, 2 disks in RAID 1 for log files, and using a SAN for the data
files. The remaining 2 physical disks would be used for backups. The SAN
is fiber channel with RAID 5.
Questions:
* If we use a SAN for the data files, is the best place for the log files
and temp db still locally on the fixed disks so we can leverage the RAID 1?
* Any general comments on comparing the scenarios above?
Thanks in advance,
Mark> * If we use a SAN for the data files, is the best place for the log files
> and temp db still locally on the fixed disks so we can leverage the RAID
> 1?
Since your application is write-intensive, I suggest you place log files on
RAID 1 (or 10) that are isolated from data files.

> * Any general comments on comparing the scenarios above?
Given the choice between local RAID 10 and SAN RAID 5, I would opt for local
disk. The optimal solution would be SAN RAID 10 for data on dedicated
spindles. Consider placing logs on the SAN only if RAID 1/10. Importantly,
don't share spindles with other servers on the SAN if performance is
important.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <mark@.idonotlikespam.com> wrote in message
news:ee0cgzD2HHA.5740@.TK2MSFTNGP04.phx.gbl...
> We're going to be hosting a write intensive non-clustered application.
> With cost constraints in place, we are down to two options:
> Dell 2950 with 8 disks: 2 disks in RAID 1 for OS, 2 disk in RAID 1 for
> tempdb/log files, and 4 disks in RAID 10 for the data files.
> Dell 2950 with 8 disks and SAN: 2 disks in RAID 1 for OS, 2 disks in RAID
> 1 for tempdb, 2 disks in RAID 1 for log files, and using a SAN for the
> data files. The remaining 2 physical disks would be used for backups.
> The SAN is fiber channel with RAID 5.
> Questions:
> * If we use a SAN for the data files, is the best place for the log files
> and temp db still locally on the fixed disks so we can leverage the RAID
> 1?
> * Any general comments on comparing the scenarios above?
> Thanks in advance,
> Mark
>
>
>|||Performance could be poor in either case with only 1 active spindle for
tempdb and logs. And in the first case you only have 2 spindles serving up
data. In SAN case RAID 5 takes a hit during writes. Biggest determinant I
think is how many disks in the RAID5 set on the SAN?
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <mark@.idonotlikespam.com> wrote in message
news:ee0cgzD2HHA.5740@.TK2MSFTNGP04.phx.gbl...
> We're going to be hosting a write intensive non-clustered application.
> With cost constraints in place, we are down to two options:
> Dell 2950 with 8 disks: 2 disks in RAID 1 for OS, 2 disk in RAID 1 for
> tempdb/log files, and 4 disks in RAID 10 for the data files.
> Dell 2950 with 8 disks and SAN: 2 disks in RAID 1 for OS, 2 disks in RAID
> 1 for tempdb, 2 disks in RAID 1 for log files, and using a SAN for the
> data files. The remaining 2 physical disks would be used for backups.
> The SAN is fiber channel with RAID 5.
> Questions:
> * If we use a SAN for the data files, is the best place for the log files
> and temp db still locally on the fixed disks so we can leverage the RAID
> 1?
> * Any general comments on comparing the scenarios above?
> Thanks in advance,
> Mark
>
>
>

SAN impact on performance

We're going to be hosting a write intensive non-clustered application. With
cost constraints in place, we are down to two options:
Dell 2950 with 8 disks: 2 disks in RAID 1 for OS, 2 disk in RAID 1 for
tempdb/log files, and 4 disks in RAID 10 for the data files.
Dell 2950 with 8 disks and SAN: 2 disks in RAID 1 for OS, 2 disks in RAID 1
for tempdb, 2 disks in RAID 1 for log files, and using a SAN for the data
files. The remaining 2 physical disks would be used for backups. The SAN
is fiber channel with RAID 5.
Questions:
* If we use a SAN for the data files, is the best place for the log files
and temp db still locally on the fixed disks so we can leverage the RAID 1?
* Any general comments on comparing the scenarios above?
Thanks in advance,
Mark> * If we use a SAN for the data files, is the best place for the log files
> and temp db still locally on the fixed disks so we can leverage the RAID
> 1?
Since your application is write-intensive, I suggest you place log files on
RAID 1 (or 10) that are isolated from data files.
> * Any general comments on comparing the scenarios above?
Given the choice between local RAID 10 and SAN RAID 5, I would opt for local
disk. The optimal solution would be SAN RAID 10 for data on dedicated
spindles. Consider placing logs on the SAN only if RAID 1/10. Importantly,
don't share spindles with other servers on the SAN if performance is
important.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <mark@.idonotlikespam.com> wrote in message
news:ee0cgzD2HHA.5740@.TK2MSFTNGP04.phx.gbl...
> We're going to be hosting a write intensive non-clustered application.
> With cost constraints in place, we are down to two options:
> Dell 2950 with 8 disks: 2 disks in RAID 1 for OS, 2 disk in RAID 1 for
> tempdb/log files, and 4 disks in RAID 10 for the data files.
> Dell 2950 with 8 disks and SAN: 2 disks in RAID 1 for OS, 2 disks in RAID
> 1 for tempdb, 2 disks in RAID 1 for log files, and using a SAN for the
> data files. The remaining 2 physical disks would be used for backups.
> The SAN is fiber channel with RAID 5.
> Questions:
> * If we use a SAN for the data files, is the best place for the log files
> and temp db still locally on the fixed disks so we can leverage the RAID
> 1?
> * Any general comments on comparing the scenarios above?
> Thanks in advance,
> Mark
>
>
>|||Performance could be poor in either case with only 1 active spindle for
tempdb and logs. And in the first case you only have 2 spindles serving up
data. In SAN case RAID 5 takes a hit during writes. Biggest determinant I
think is how many disks in the RAID5 set on the SAN?
--
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <mark@.idonotlikespam.com> wrote in message
news:ee0cgzD2HHA.5740@.TK2MSFTNGP04.phx.gbl...
> We're going to be hosting a write intensive non-clustered application.
> With cost constraints in place, we are down to two options:
> Dell 2950 with 8 disks: 2 disks in RAID 1 for OS, 2 disk in RAID 1 for
> tempdb/log files, and 4 disks in RAID 10 for the data files.
> Dell 2950 with 8 disks and SAN: 2 disks in RAID 1 for OS, 2 disks in RAID
> 1 for tempdb, 2 disks in RAID 1 for log files, and using a SAN for the
> data files. The remaining 2 physical disks would be used for backups.
> The SAN is fiber channel with RAID 5.
> Questions:
> * If we use a SAN for the data files, is the best place for the log files
> and temp db still locally on the fixed disks so we can leverage the RAID
> 1?
> * Any general comments on comparing the scenarios above?
> Thanks in advance,
> Mark
>
>
>

SAN impact on performance

We're going to be hosting a write intensive non-clustered application. With
cost constraints in place, we are down to two options:
Dell 2950 with 8 disks: 2 disks in RAID 1 for OS, 2 disk in RAID 1 for
tempdb/log files, and 4 disks in RAID 10 for the data files.
Dell 2950 with 8 disks and SAN: 2 disks in RAID 1 for OS, 2 disks in RAID 1
for tempdb, 2 disks in RAID 1 for log files, and using a SAN for the data
files. The remaining 2 physical disks would be used for backups. The SAN
is fiber channel with RAID 5.
Questions:
* If we use a SAN for the data files, is the best place for the log files
and temp db still locally on the fixed disks so we can leverage the RAID 1?
* Any general comments on comparing the scenarios above?
Thanks in advance,
Mark
> * If we use a SAN for the data files, is the best place for the log files
> and temp db still locally on the fixed disks so we can leverage the RAID
> 1?
Since your application is write-intensive, I suggest you place log files on
RAID 1 (or 10) that are isolated from data files.

> * Any general comments on comparing the scenarios above?
Given the choice between local RAID 10 and SAN RAID 5, I would opt for local
disk. The optimal solution would be SAN RAID 10 for data on dedicated
spindles. Consider placing logs on the SAN only if RAID 1/10. Importantly,
don't share spindles with other servers on the SAN if performance is
important.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark" <mark@.idonotlikespam.com> wrote in message
news:ee0cgzD2HHA.5740@.TK2MSFTNGP04.phx.gbl...
> We're going to be hosting a write intensive non-clustered application.
> With cost constraints in place, we are down to two options:
> Dell 2950 with 8 disks: 2 disks in RAID 1 for OS, 2 disk in RAID 1 for
> tempdb/log files, and 4 disks in RAID 10 for the data files.
> Dell 2950 with 8 disks and SAN: 2 disks in RAID 1 for OS, 2 disks in RAID
> 1 for tempdb, 2 disks in RAID 1 for log files, and using a SAN for the
> data files. The remaining 2 physical disks would be used for backups.
> The SAN is fiber channel with RAID 5.
> Questions:
> * If we use a SAN for the data files, is the best place for the log files
> and temp db still locally on the fixed disks so we can leverage the RAID
> 1?
> * Any general comments on comparing the scenarios above?
> Thanks in advance,
> Mark
>
>
>
|||Performance could be poor in either case with only 1 active spindle for
tempdb and logs. And in the first case you only have 2 spindles serving up
data. In SAN case RAID 5 takes a hit during writes. Biggest determinant I
think is how many disks in the RAID5 set on the SAN?
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <mark@.idonotlikespam.com> wrote in message
news:ee0cgzD2HHA.5740@.TK2MSFTNGP04.phx.gbl...
> We're going to be hosting a write intensive non-clustered application.
> With cost constraints in place, we are down to two options:
> Dell 2950 with 8 disks: 2 disks in RAID 1 for OS, 2 disk in RAID 1 for
> tempdb/log files, and 4 disks in RAID 10 for the data files.
> Dell 2950 with 8 disks and SAN: 2 disks in RAID 1 for OS, 2 disks in RAID
> 1 for tempdb, 2 disks in RAID 1 for log files, and using a SAN for the
> data files. The remaining 2 physical disks would be used for backups.
> The SAN is fiber channel with RAID 5.
> Questions:
> * If we use a SAN for the data files, is the best place for the log files
> and temp db still locally on the fixed disks so we can leverage the RAID
> 1?
> * Any general comments on comparing the scenarios above?
> Thanks in advance,
> Mark
>
>
>

SAN Alignment Impact on SQL Server 2005

What Is the estimated impact on SQL Server 2005 I/O of aligning the
partitions on our SAN with DiskPart?
We are deploying several SQL Server 2005 instances on a new Hitachi Thunder
SAN and have formatted the LUNs but didn't use the DiskPart utility. We're
getting close to implementation, so now that we've heard about this, we are
wondering whether it would be worth the performance benefit to go back and d
o
this.
If so any guidelines for running it would also be appreciated.
Any insight would be appreciated!
thanksThis is a subject we've recently started focusing on at the place I
work. Basically for all new LUNs we're assigning to hosts we align the
partition before formatting. I've heard varying reports of read & write
performance improvements of anywhere from 30-80% depending on the type
of system (email, database, etc.) using the LUN and the kind of access
involved. But I haven't done direct comparison tests myself (my latest
tests have been FC vs ATA disk and are therefore not comparable in terms
of aligned partitions vs non-aligned partitions).
We use EMC SANs so I assume it will be slightly different for you but
EMC wrote a good PDF whitepaper on partition alignment with regard to
EMC SANs (I'm sure the principles would apply equally to Hitachi SANs).
It's too big to attach to an NNTP post (1.5MB), even zipped & split into
multiple parts, so I uploaded it onto dropfiles.net:
http://quick.dropfiles.net/download...rd.blogspot.com
smoss wrote:

>What Is the estimated impact on SQL Server 2005 I/O of aligning the
>partitions on our SAN with DiskPart?
>We are deploying several SQL Server 2005 instances on a new Hitachi Thunder
>SAN and have formatted the LUNs but didn't use the DiskPart utility. We're
>getting close to implementation, so now that we've heard about this, we are
>wondering whether it would be worth the performance benefit to go back and
do
>this.
>If so any guidelines for running it would also be appreciated.
>Any insight would be appreciated!
>thanks
>|||good article.
thanks the reference.
when do we take care about the alignment?
I mean, only on SAN systems or any disk should be aligned?
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:O3f%230GgfGHA.336
4@.TK2MSFTNGP05.phx.gbl...
This is a subject we've recently started focusing on at the place I work. B
asically for all new LUNs we're assigning to hosts we align the partition be
fore formatting. I've heard varying reports of read & write performance imp
rovements of anywhere from 30-80% depending on the type of system (email, da
tabase, etc.) using the LUN and the kind of access involved. But I haven't
done direct comparison tests myself (my latest tests have been FC vs ATA dis
k and are therefore not comparable in terms of aligned partitions vs non-ali
gned partitions).
We use EMC SANs so I assume it will be slightly different for you but EMC wrote a good
PDF whitepaper on partition alignment with regard to EMC SANs (I'm sure the principles
would apply equally to Hitachi SANs). It's too big to attach to an NNTP post (1.5MB),
even zipped & split into multiple parts, so I uploaded it onto dropfiles.net: http
://quick.dropfiles.net/...02d65770cbaaef6
Hope this helps.
mike hodgson
http://sqlnerd.blogspot.com
smoss wrote:
What Is the estimated impact on SQL Server 2005 I/O of aligning the
partitions on our SAN with DiskPart?
We are deploying several SQL Server 2005 instances on a new Hitachi Thunder
SAN and have formatted the LUNs but didn't use the DiskPart utility. We're
getting close to implementation, so now that we've heard about this, we are
wondering whether it would be worth the performance benefit to go back and d
o
this.
If so any guidelines for running it would also be appreciated.
Any insight would be appreciated!
thanks|||I'm not really a disk expert (so I'm happy to be corrected on this) but
from what I gather it's really only relevant for SANs, where another OS
(the SAN OS) is responsible for doing the actual writes to disk. When
Windows itself writes to local disk I believe it writes blocks as
appropriate based on the physical disk configuration it had determined
(which is normally a bit of a guess of 63 sectors/track & 255
heads/cylinder I think), but it can't do that with a SAN disk - it just
passes the write request off to the SAN and lets the SAN worry about it,
so it's likely to get the track boundaries wrong.
*mike hodgson*
http://sqlnerd.blogspot.com
Jeje wrote:
[vbcol=seagreen]
> good article.
> thanks the reference.
> when do we take care about the alignment?
> I mean, only on SAN systems or any disk should be aligned?
>
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:O3f%230GgfGHA.3364@.TK2MSFTNGP05.phx.gbl...
> This is a subject we've recently started focusing on at the place
> I work. Basically for all new LUNs we're assigning to hosts we
> align the partition before formatting. I've heard varying reports
> of read & write performance improvements of anywhere from 30-80%
> depending on the type of system (email, database, etc.) using the
> LUN and the kind of access involved. But I haven't done direct
> comparison tests myself (my latest tests have been FC vs ATA disk
> and are therefore not comparable in terms of aligned partitions vs
> non-aligned partitions).
> We use EMC SANs so I assume it will be slightly different for you
> but EMC wrote a good PDF whitepaper on partition alignment with
> regard to EMC SANs (I'm sure the principles would apply equally to
> Hitachi SANs). It's too big to attach to an NNTP post (1.5MB),
> even zipped & split into multiple parts, so I uploaded it onto
> dropfiles.net:
> http://quick.dropfiles.net/download...cbaaef
6
> Hope this helps.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> smoss wrote:
>|||Thanks, Mike, for the very helpful reply.
The only thing that I could ask in addition is that if anyone has specific
experience with SQL 2005 and/or Hitachi, I'd appreciate hearing about their
experience.
thanks again!
"Mike Hodgson" wrote:

> This is a subject we've recently started focusing on at the place I
> work. Basically for all new LUNs we're assigning to hosts we align the
> partition before formatting. I've heard varying reports of read & write
> performance improvements of anywhere from 30-80% depending on the type
> of system (email, database, etc.) using the LUN and the kind of access
> involved. But I haven't done direct comparison tests myself (my latest
> tests have been FC vs ATA disk and are therefore not comparable in terms
> of aligned partitions vs non-aligned partitions).
> We use EMC SANs so I assume it will be slightly different for you but
> EMC wrote a good PDF whitepaper on partition alignment with regard to
> EMC SANs (I'm sure the principles would apply equally to Hitachi SANs).
> It's too big to attach to an NNTP post (1.5MB), even zipped & split into
> multiple parts, so I uploaded it onto dropfiles.net:
> http://quick.dropfiles.net/download...rd.blogspot.com
>
> smoss wrote:
>
>|||You are correct. When the OS writes to a local disk, alignment is not an is
sue. One of the tricks a SAN uses to improve performance is to use large da
ta blocks to manage internal usage. The disks work very well at these large
block reads and writes. These blocks are internal and not visible to the a
ttached computers. If the OS blocks do not fit evenly into the large intern
al blocks, the SAN will issue two internal I/O requests for every OS block r
equest that crosses an internal boundary. Also, on some platforms, the SAN
must copy each OS block to realign it with its internal block structure duri
ng the read or write process. In short, this is a VERY BAD THING. Given th
at the NTFS MBR block is not aligned with the other block sizes, unless you
manually adjust the MBR record size, you will have alignment problems. And
yes, all NTFS disks have a MBR section, even if they aren't "boot" disks.
As you noted, EMC has an excellent paper on the subject.
I use this information as a "litmus test" for SAN vendors. Unless they can
provide a technical expert who warns about this and will set things up corre
ctly, I keep looking. They will not be able to help troubleshoot SQL and SA
N performance issues.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:epnVBKhfGHA.3652@.
TK2MSFTNGP02.phx.gbl...
I'm not really a disk expert (so I'm happy to be corrected on this) but from
what I gather it's really only relevant for SANs, where another OS (the SAN
OS) is responsible for doing the actual writes to disk. When Windows itsel
f writes to local disk I believe it writes blocks as appropriate based on th
e physical disk configuration it had determined (which is normally a bit of
a guess of 63 sectors/track & 255 heads/cylinder I think), but it can't do t
hat with a SAN disk - it just passes the write request off to the SAN and le
ts the SAN worry about it, so it's likely to get the track boundaries wrong.
mike hodgson
http://sqlnerd.blogspot.com
Jeje wrote:
good article.
thanks the reference.
when do we take care about the alignment?
I mean, only on SAN systems or any disk should be aligned?
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:O3f%230GgfGHA.336
4@.TK2MSFTNGP05.phx.gbl...
This is a subject we've recently started focusing on at the place I work. B
asically for all new LUNs we're assigning to hosts we align the partition be
fore formatting. I've heard varying reports of read & write performance imp
rovements of anywhere from 30-80% depending on the type of system (email, da
tabase, etc.) using the LUN and the kind of access involved. But I haven't
done direct comparison tests myself (my latest tests have been FC vs ATA dis
k and are therefore not comparable in terms of aligned partitions vs non-ali
gned partitions).
We use EMC SANs so I assume it will be slightly different for you but EMC wrote a good
PDF whitepaper on partition alignment with regard to EMC SANs (I'm sure the principles
would apply equally to Hitachi SANs). It's too big to attach to an NNTP post (1.5MB),
even zipped & split into multiple parts, so I uploaded it onto dropfiles.net: http
://quick.dropfiles.net/...02d65770cbaaef6
Hope this helps.
mike hodgson
http://sqlnerd.blogspot.com
smoss wrote:
What Is the estimated impact on SQL Server 2005 I/O of aligning the
partitions on our SAN with DiskPart?
We are deploying several SQL Server 2005 instances on a new Hitachi Thunder
SAN and have formatted the LUNs but didn't use the DiskPart utility. We're
getting close to implementation, so now that we've heard about this, we are
wondering whether it would be worth the performance benefit to go back and d
o
this.
If so any guidelines for running it would also be appreciated.
Any insight would be appreciated!
thanks

SAN Alignment Impact on SQL Server 2005

good article.
thanks the reference.
when do we take care about the alignment?
I mean, only on SAN systems or any disk =should be aligned?
"Mike Hodgson" wrote =in message news:O3f%230GgfGHA.=3364@.TK2MSFTNGP05.phx.gbl...This is a subject we've recently started focusing on at the place I =work. Basically for all new LUNs we're assigning to hosts we align the =partition before formatting. I've heard varying reports of read & =write performance improvements of anywhere from 30-80% depending on the type =of system (email, database, etc.) using the LUN and the kind of access involved. But I haven't done direct comparison tests myself (my =latest tests have been FC vs ATA disk and are therefore not comparable in =terms of aligned partitions vs non-aligned partitions).We use EMC SANs =so I assume it will be slightly different for you but EMC wrote a good PDF whitepaper on partition alignment with regard to EMC SANs (I'm sure =the principles would apply equally to Hitachi SANs). It's too big to =attach to an NNTP post (1.5MB), even zipped & split into multiple parts, =so I uploaded it onto dropfiles.net: http://quick.dropfiles.net/download.php?file=3Df5ec179375=1f815e702d65770cbaaef6Hope this helps.
--mike =hodgsonhttp://sqlnerd.blogspot.com smoss wrote: What Is the estimated impact on SQL =Server 2005 I/O of aligning the partitions on our SAN with DiskPart? We are deploying several SQL Server 2005 instances on a new Hitachi =Thunder SAN and have formatted the LUNs but didn't use the DiskPart utility. =We're getting close to implementation, so now that we've heard about this, we =are wondering whether it would be worth the performance benefit to go back =and do this. If so any guidelines for running it would also be appreciated.
Any insight would be appreciated!
thanks

--=_NextPart_000_00C3_01C67DE6.FFEC1590--|||This is a multi-part message in MIME format.
--090505040106000401090209
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
I'm not really a disk expert (so I'm happy to be corrected on this) but
from what I gather it's really only relevant for SANs, where another OS
(the SAN OS) is responsible for doing the actual writes to disk. When
Windows itself writes to local disk I believe it writes blocks as
appropriate based on the physical disk configuration it had determined
(which is normally a bit of a guess of 63 sectors/track & 255
heads/cylinder I think), but it can't do that with a SAN disk - it just
passes the write request off to the SAN and lets the SAN worry about it,
so it's likely to get the track boundaries wrong.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Jeje wrote:
> good article.
> thanks the reference.
> when do we take care about the alignment?
> I mean, only on SAN systems or any disk should be aligned?
>
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:O3f%230GgfGHA.3364@.TK2MSFTNGP05.phx.gbl...
> This is a subject we've recently started focusing on at the place
> I work. Basically for all new LUNs we're assigning to hosts we
> align the partition before formatting. I've heard varying reports
> of read & write performance improvements of anywhere from 30-80%
> depending on the type of system (email, database, etc.) using the
> LUN and the kind of access involved. But I haven't done direct
> comparison tests myself (my latest tests have been FC vs ATA disk
> and are therefore not comparable in terms of aligned partitions vs
> non-aligned partitions).
> We use EMC SANs so I assume it will be slightly different for you
> but EMC wrote a good PDF whitepaper on partition alignment with
> regard to EMC SANs (I'm sure the principles would apply equally to
> Hitachi SANs). It's too big to attach to an NNTP post (1.5MB),
> even zipped & split into multiple parts, so I uploaded it onto
> dropfiles.net:
> http://quick.dropfiles.net/download.php?file=f5ec1793751f815e702d65770cbaaef6
> Hope this helps.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> smoss wrote:
>>What Is the estimated impact on SQL Server 2005 I/O of aligning the
>>partitions on our SAN with DiskPart?
>>We are deploying several SQL Server 2005 instances on a new Hitachi Thunder
>>SAN and have formatted the LUNs but didn't use the DiskPart utility. We're
>>getting close to implementation, so now that we've heard about this, we are
>>wondering whether it would be worth the performance benefit to go back and do
>>this.
>>If so any guidelines for running it would also be appreciated.
>>Any insight would be appreciated!
>>thanks
>>
--090505040106000401090209
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I'm not really a disk expert (so I'm happy to be corrected on this)
but from what I gather it's really only relevant for SANs, where
another OS (the SAN OS) is responsible for doing the actual writes to
disk.Â When Windows itself writes to local disk I believe it writes
blocks as appropriate based on the physical disk configuration it had
determined (which is normally a bit of a guess of 63 sectors/track
& 255 heads/cylinder I think), but it can't do that with a SAN disk
- it just passes the write request off to the SAN and lets the SAN
worry about it, so it's likely to get the track boundaries wrong.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Jeje wrote:
<blockquote cite="midOLMAIigfGHA.4892@.TK2MSFTNGP02.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">
<meta content="MSHTML 6.00.3790.2666" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">good article.</font></div>
<div><font face="Arial" size="2">thanks the reference.</font></div>
<div>Â </div>
<div><font face="Arial" size="2">when do weÂ take care about the
alignment?</font></div>
<div><font face="Arial" size="2">I mean, only on SAN systems or any
disk should be aligned?</font></div>
<div>Â </div>
<div>Â </div>
<div>Â </div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a href="http://links.10026.com/?link=mailto:e1minst3r@.gmail.com">e1minst3r@.gmail.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:O3f%230GgfGHA.3364@.TK2MSFTNGP05.phx.gbl">news:O3f%230GgfGHA.3364@.TK2MSFTNGP05.phx.gbl</a>...</div>
<tt>This is a subject we've recently started focusing on at the
place I work.Â Basically for all new LUNs we're assigning to hosts we
align the partition before formatting.Â I've heard varying reports of
read & write performance improvements of anywhere from 30-80%
depending on the type of system (email, database, etc.) using the LUN
and the kind of access involved.Â But I haven't done direct comparison
tests myself (my latest tests have been FC vs ATA disk and are
therefore not comparable in terms of aligned partitions vs non-aligned
partitions).<br>
<br>
We use EMC SANs so I assume it will be slightly different for you but
EMC wrote a good PDF whitepaper on partition alignment with regard to
EMC SANs (I'm sure the principles would apply equally to Hitachi
SANs).Â It's too big to attach to an NNTP post (1.5MB), even zipped
& split into multiple parts, so I uploaded it onto dropfiles.net: <a
class="moz-txt-link-freetext"
href="http://links.10026.com/?link=http://quick.dropfiles.net/download.php?file=f5ec1793751f815e702d65770cbaaef6</a><br>">http://quick.dropfiles.net/download.php?file=f5ec1793751f815e702d65770cbaaef6">http://quick.dropfiles.net/download.php?file=f5ec1793751f815e702d65770cbaaef6</a><br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
smoss wrote:
<blockquote
cite="mid4B6DDCF8-3B45-4C54-8917-AB4CAD3FC8EE@.microsoft.com"
type="cite">
<pre wrap="">What Is the estimated impact on SQL Server 2005 I/O of aligning the
partitions on our SAN with DiskPart?
We are deploying several SQL Server 2005 instances on a new Hitachi Thunder
SAN and have formatted the LUNs but didn't use the DiskPart utility. We're
getting close to implementation, so now that we've heard about this, we are
wondering whether it would be worth the performance benefit to go back and do
this.
If so any guidelines for running it would also be appreciated.
Any insight would be appreciated!
thanks
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--090505040106000401090209--|||Thanks, Mike, for the very helpful reply.
The only thing that I could ask in addition is that if anyone has specific
experience with SQL 2005 and/or Hitachi, I'd appreciate hearing about their
experience.
thanks again!
"Mike Hodgson" wrote:
> This is a subject we've recently started focusing on at the place I
> work. Basically for all new LUNs we're assigning to hosts we align the
> partition before formatting. I've heard varying reports of read & write
> performance improvements of anywhere from 30-80% depending on the type
> of system (email, database, etc.) using the LUN and the kind of access
> involved. But I haven't done direct comparison tests myself (my latest
> tests have been FC vs ATA disk and are therefore not comparable in terms
> of aligned partitions vs non-aligned partitions).
> We use EMC SANs so I assume it will be slightly different for you but
> EMC wrote a good PDF whitepaper on partition alignment with regard to
> EMC SANs (I'm sure the principles would apply equally to Hitachi SANs).
> It's too big to attach to an NNTP post (1.5MB), even zipped & split into
> multiple parts, so I uploaded it onto dropfiles.net:
> http://quick.dropfiles.net/download.php?file=f5ec1793751f815e702d65770cbaaef6
> Hope this helps.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> smoss wrote:
> >What Is the estimated impact on SQL Server 2005 I/O of aligning the
> >partitions on our SAN with DiskPart?
> >
> >We are deploying several SQL Server 2005 instances on a new Hitachi Thunder
> >SAN and have formatted the LUNs but didn't use the DiskPart utility. We're
> >getting close to implementation, so now that we've heard about this, we are
> >wondering whether it would be worth the performance benefit to go back and do
> >this.
> >
> >If so any guidelines for running it would also be appreciated.
> >
> >Any insight would be appreciated!
> >
> >thanks
> >
> >
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01B6_01C67DF5.B72A2590
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
You are correct. When the OS writes to a local disk, alignment is not =an issue. One of the tricks a SAN uses to improve performance is to use =large data blocks to manage internal usage. The disks work very well at =these large block reads and writes. These blocks are internal and not =visible to the attached computers. If the OS blocks do not fit evenly =into the large internal blocks, the SAN will issue two internal I/O =requests for every OS block request that crosses an internal boundary. =Also, on some platforms, the SAN must copy each OS block to realign it =with its internal block structure during the read or write process. In =short, this is a VERY BAD THING. Given that the NTFS MBR block is not =aligned with the other block sizes, unless you manually adjust the MBR =record size, you will have alignment problems. And yes, all NTFS disks =have a MBR section, even if they aren't "boot" disks.
As you noted, EMC has an excellent paper on the subject.
I use this information as a "litmus test" for SAN vendors. Unless they =can provide a technical expert who warns about this and will set things =up correctly, I keep looking. They will not be able to help =troubleshoot SQL and SAN performance issues.
-- Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:epnVBKhfGHA.3652@.TK2MSFTNGP02.phx.gbl...
I'm not really a disk expert (so I'm happy to be corrected on this) =but from what I gather it's really only relevant for SANs, where another =OS (the SAN OS) is responsible for doing the actual writes to disk. =When Windows itself writes to local disk I believe it writes blocks as =appropriate based on the physical disk configuration it had determined =(which is normally a bit of a guess of 63 sectors/track & 255 =heads/cylinder I think), but it can't do that with a SAN disk - it just =passes the write request off to the SAN and lets the SAN worry about it, =so it's likely to get the track boundaries wrong.
--
mike hodgson
http://sqlnerd.blogspot.com=20
Jeje wrote: good article.
thanks the reference.
when do we take care about the alignment?
I mean, only on SAN systems or any disk should be aligned?
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:O3f%230GgfGHA.3364@.TK2MSFTNGP05.phx.gbl...
This is a subject we've recently started focusing on at the place =I work. Basically for all new LUNs we're assigning to hosts we align =the partition before formatting. I've heard varying reports of read & =write performance improvements of anywhere from 30-80% depending on the =type of system (email, database, etc.) using the LUN and the kind of =access involved. But I haven't done direct comparison tests myself (my =latest tests have been FC vs ATA disk and are therefore not comparable =in terms of aligned partitions vs non-aligned partitions).
We use EMC SANs so I assume it will be slightly different for you =but EMC wrote a good PDF whitepaper on partition alignment with regard =to EMC SANs (I'm sure the principles would apply equally to Hitachi =SANs). It's too big to attach to an NNTP post (1.5MB), even zipped & =split into multiple parts, so I uploaded it onto dropfiles.net: =http://quick.dropfiles.net/download.php?file=3Df5ec1793751f815e702d65770c=
baaef6
Hope this helps.
--
mike hodgson
http://sqlnerd.blogspot.com=20
smoss wrote: What Is the estimated impact on SQL Server 2005 I/O of aligning the partitions on our SAN with DiskPart?
We are deploying several SQL Server 2005 instances on a new Hitachi =Thunder SAN and have formatted the LUNs but didn't use the DiskPart utility. =We're getting close to implementation, so now that we've heard about this, we =are wondering whether it would be worth the performance benefit to go back =and do this.
If so any guidelines for running it would also be appreciated.
Any insight would be appreciated!
thanks
--=_NextPart_000_01B6_01C67DF5.B72A2590
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

You are correct. When the OS =writes to a local disk, alignment is not an issue. One of the tricks a SAN =uses to improve performance is to use large data blocks to manage internal =usage. The disks work very well at these large block reads and writes. =These blocks are internal and not visible to the attached computers. If =the OS blocks do not fit evenly into the large internal blocks, the SAN will =issue two internal I/O requests for every OS block request that crosses an internal boundary. Also, on some =platforms, the SAN must copy each OS block to realign it with its internal block =structure during the read or write process. In short, this is a VERY BAD THING. Given that the NTFS MBR block is not aligned with the =other block sizes, unless you manually adjust the MBR record size, you will =have alignment problems. And yes, all NTFS disks have a MBR section, =even if they aren't "boot" disks.
As you noted, EMC has an excellent =paper on the subject.
I use this information as a "litmus =test" for SAN vendors. Unless they can provide a technical expert who warns =about this and will set things up correctly, I keep looking. They will not be =able to help troubleshoot SQL and SAN performance issues.
-- Geoff N. HitenSenior Database =AdministratorMicrosoft SQL Server MVP
"Mike Hodgson" wrote =in message news:epnVBKhfGHA.3652=@.TK2MSFTNGP02.phx.gbl...I'm not really a disk expert (so I'm happy to be corrected on this) but =from what I gather it's really only relevant for SANs, where another OS (the SAN =OS) is responsible for doing the actual writes to disk. When Windows =itself writes to local disk I believe it writes blocks as appropriate based =on the physical disk configuration it had determined (which is normally a bit =of a guess of 63 sectors/track & 255 heads/cylinder I think), but it =can't do that with a SAN disk - it just passes the write request off to the SAN =and lets the SAN worry about it, so it's likely to get the track =boundaries wrong.
--mike =hodgsonhttp://sqlnerd.blogspot.com Jeje wrote:
good article.
thanks the reference.

when do we take care about =the alignment?
I mean, only on SAN systems or any =disk should be aligned?

"Mike Hodgson" =wrote in message news:O3f%230GgfGHA.=3364@.TK2MSFTNGP05.phx.gbl...This is a subject we've recently started focusing on at the place I =work. Basically for all new LUNs we're assigning to hosts we align the =partition before formatting. I've heard varying reports of read & =write performance improvements of anywhere from 30-80% depending on the =type of system (email, database, etc.) using the LUN and the kind of =access involved. But I haven't done direct comparison tests myself =(my latest tests have been FC vs ATA disk and are therefore not =comparable in terms of aligned partitions vs non-aligned partitions).We =use EMC SANs so I assume it will be slightly different for you but EMC =wrote a good PDF whitepaper on partition alignment with regard to EMC SANs =(I'm sure the principles would apply equally to Hitachi SANs). =It's too big to attach to an NNTP post (1.5MB), even zipped & split =into multiple parts, so I uploaded it onto dropfiles.net: http://quick.dropfiles.net/download.php?file=3Df5ec179375=1f815e702d65770cbaaef6Hope this helps.
--mike =hodgsonhttp://sqlnerd.blogspot.com smoss wrote: What Is the estimated impact on SQL =Server 2005 I/O of aligning the partitions on our SAN with DiskPart? We are deploying several SQL Server 2005 instances on a new Hitachi =Thunder SAN and have formatted the LUNs but didn't use the DiskPart utility. =We're getting close to implementation, so now that we've heard about this, we =are wondering whether it would be worth the performance benefit to go back =and do this. If so any guidelines for running it would also be appreciated.
Any insight would be appreciated!
thanks
=

--=_NextPart_000_01B6_01C67DF5.B72A2590--

Saturday, February 25, 2012

SAN impact on performance

SAN impact on performance

SAN impact on performance

SAN Alignment Impact on SQL Server 2005

SAN Alignment Impact on SQL Server 2005

Sample db problem

Blog Archive

About Me