disk sector alignment

Wher can i read more about disk sector alignment that helps speed IO for
SQL.
Or can someone just explain it in lay man terms? Why do we need it ? How do
do it and also how can we confirm its done ?On older HP storage, I found about a 10% gain with SQLIO testing sequential
writes to simulate tlog activity. The best setting I found was 128 offset
and 64k NT allocation unit. However, I would test for the optimal settings
for the drive based on the type of IO you expect and what the vendor
recommends. I have see HP recommend both a 64 and 128 offset depending on
where you look.
This blog can give you some info on the type of IO a data drive, log or
backup drive would have as well as info on SQLIO.
http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
Look up the help on diskpart.exe as well.
HTH
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.hotmail.com> wrote in message
news:erUAWMpPIHA.2000@.TK2MSFTNGP05.phx.gbl...
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How
> do do it and also how can we confirm its done ?
>|||use diskpart to see information about alignment and also to create volumes
that are aligned
search the web for SQL Server Predeployment I/O Best Practices for a great
document.
search microsoft.com for diskpart for documentation on that tool
in a nut shell, un-aligned sectors can result in 2 physical I/Os for a
single I/O request because of the way partitions are laid down on the disk
structure. 2-for-1 is obviously a bad thing for performance. :)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Hassan" <hassan@.hotmail.com> wrote in message
news:erUAWMpPIHA.2000@.TK2MSFTNGP05.phx.gbl...
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How
> do do it and also how can we confirm its done ?
>|||Hassan,
This is pretty good:
http://sqlblogcasts.com/blogs/christian/archive/2007/10/09/disk-sector-alignment.aspx
RLF
"Hassan" <hassan@.hotmail.com> wrote in message
news:erUAWMpPIHA.2000@.TK2MSFTNGP05.phx.gbl...
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How
> do do it and also how can we confirm its done ?
>|||Also take a look here:
http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx
and
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/19/disk-partition-alignment-in-the-published-tpc-benchmarks.aspx
Linchi
"Hassan" wrote:
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How do
> do it and also how can we confirm its done ?
>
>

disk sector alignment

Wher can i read more about disk sector alignment that helps speed IO for
SQL.
Or can someone just explain it in lay man terms? Why do we need it ? How do
do it and also how can we confirm its done ?
On older HP storage, I found about a 10% gain with SQLIO testing sequential
writes to simulate tlog activity. The best setting I found was 128 offset
and 64k NT allocation unit. However, I would test for the optimal settings
for the drive based on the type of IO you expect and what the vendor
recommends. I have see HP recommend both a 64 and 128 offset depending on
where you look.
This blog can give you some info on the type of IO a data drive, log or
backup drive would have as well as info on SQLIO.
http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
Look up the help on diskpart.exe as well.
HTH
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.hotmail.com> wrote in message
news:erUAWMpPIHA.2000@.TK2MSFTNGP05.phx.gbl...
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How
> do do it and also how can we confirm its done ?
>
|||Hassan,
This is pretty good:
http://sqlblogcasts.com/blogs/christian/archive/2007/10/09/disk-sector-alignment.aspx
RLF
"Hassan" <hassan@.hotmail.com> wrote in message
news:erUAWMpPIHA.2000@.TK2MSFTNGP05.phx.gbl...
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How
> do do it and also how can we confirm its done ?
>
|||use diskpart to see information about alignment and also to create volumes
that are aligned
search the web for SQL Server Predeployment I/O Best Practices for a great
document.
search microsoft.com for diskpart for documentation on that tool
in a nut shell, un-aligned sectors can result in 2 physical I/Os for a
single I/O request because of the way partitions are laid down on the disk
structure. 2-for-1 is obviously a bad thing for performance.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Hassan" <hassan@.hotmail.com> wrote in message
news:erUAWMpPIHA.2000@.TK2MSFTNGP05.phx.gbl...
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How
> do do it and also how can we confirm its done ?
>
|||Also take a look here:
http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx
and
http://sqlblog.com/blogs/linchi_shea/archive/2007/07/19/disk-partition-alignment-in-the-published-tpc-benchmarks.aspx
Linchi
"Hassan" wrote:

> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How do
> do it and also how can we confirm its done ?
>
>

disk sector alignment

Wher can i read more about disk sector alignment that helps speed IO for
SQL.
Or can someone just explain it in lay man terms? Why do we need it ? How do
do it and also how can we confirm its done ?On older HP storage, I found about a 10% gain with SQLIO testing sequential
writes to simulate tlog activity. The best setting I found was 128 offset
and 64k NT allocation unit. However, I would test for the optimal settings
for the drive based on the type of IO you expect and what the vendor
recommends. I have see HP recommend both a 64 and 128 offset depending on
where you look.
This blog can give you some info on the type of IO a data drive, log or
backup drive would have as well as info on SQLIO.
http://blogs.msdn.com/sqlcat/archiv.../17/493944.aspx
Look up the help on diskpart.exe as well.
HTH
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.hotmail.com> wrote in message
news:erUAWMpPIHA.2000@.TK2MSFTNGP05.phx.gbl...
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How
> do do it and also how can we confirm its done ?
>|||Hassan,
This is pretty good:
http://sqlblogcasts.com/blogs/chris...nt.a
spx
RLF
"Hassan" <hassan@.hotmail.com> wrote in message
news:erUAWMpPIHA.2000@.TK2MSFTNGP05.phx.gbl...
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How
> do do it and also how can we confirm its done ?
>|||use diskpart to see information about alignment and also to create volumes
that are aligned
search the web for SQL Server Predeployment I/O Best Practices for a great
document.
search microsoft.com for diskpart for documentation on that tool
in a nut shell, un-aligned sectors can result in 2 physical I/Os for a
single I/O request because of the way partitions are laid down on the disk
structure. 2-for-1 is obviously a bad thing for performance.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Hassan" <hassan@.hotmail.com> wrote in message
news:erUAWMpPIHA.2000@.TK2MSFTNGP05.phx.gbl...
> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How
> do do it and also how can we confirm its done ?
>|||Also take a look here:
http://sqlblog.com/blogs/linchi_she...salignment.aspx
and
http://sqlblog.com/blogs/linchi_she...benchmarks.aspx
Linchi
"Hassan" wrote:

> Wher can i read more about disk sector alignment that helps speed IO for
> SQL.
> Or can someone just explain it in lay man terms? Why do we need it ? How d
o
> do it and also how can we confirm its done ?
>
>

Disable updates via triggers - how?

Hi,
We use push transactional replication successfully and are about to bring a
few remote on-sites into the fold. The connection speed will be low - in the
64k/128k range. As I understand it "Not for replication" means that a trigger
on the subcriber will not fire if the update is generated via a replication
process, i.e. all updates are pushed from the publisher. If this is correct
is there any way to reverse the logic, i.e. don't replicate updates on the
publisher that are caused by triggers and assume the subscriber will have the
appropriate triggers in place to do the update.
We are interested in doing this to reduce the amount of network bandwidth
consummed be replication (this will be critical when the remote sites come
on-line).
Can this be done?
Cheers, Peter
The not for replication switch means that replication related activity
will not cause the trigger, constraint, identity property to be enforced
if the triggering activity occurs by a replication process. This could
happen on the publisher or subscriber.
There is a way to bypass the replication process. What you do is add
filters to your tables which look like this
select * from published tables where 1=1.
A filter proc will be created which the log reader agent uses when
figuring out what commands are to be written to the distribution agent.
Change this filter to always return 0 when you want the commands to be
ignored. Change it back to 1 when you want the commands to be processed.
The problem is you can't alter procs via a trigger.
The way I handle things like this is to build the logic into the
replication proc that applies these transactions on the subscriber(s).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Hi Hillary,
Thanks for the response.
Where do I go to add the filter: "select * from published tables where 1=1."?
When and where would I change the filter?
I do have a copy of your fine book - what sections are relevent to this
particular issue.
I presume the following isn't relevent to my circumstance as what I'm trying
to do reduce network traffice. Or I have I misunderstood?

> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
Cheers, Peter
"Hilary Cotter" wrote:

> The not for replication switch means that replication related activity
> will not cause the trigger, constraint, identity property to be enforced
> if the triggering activity occurs by a replication process. This could
> happen on the publisher or subscriber.
> There is a way to bypass the replication process. What you do is add
> filters to your tables which look like this
>
> A filter proc will be created which the log reader agent uses when
> figuring out what commands are to be written to the distribution agent.
> Change this filter to always return 0 when you want the commands to be
> ignored. Change it back to 1 when you want the commands to be processed.
> The problem is you can't alter procs via a trigger.
> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
|||You are correct, making these change to the replication procs used to
synchronize your data will not minimize network traffic. Replicating the
execution of stored procedures will, if the majority of your transactions
occurring on the publisher affect more than one row.
Have a look at this post for an example of how to make the log reader agent
bypass transactions.
http://groups-beta.google.com/group/...c?dmode=source
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:87E55BFF-BCB2-4C20-B5F5-9C48B3B8064D@.microsoft.com...[vbcol=seagreen]
> Hi Hillary,
> Thanks for the response.
> Where do I go to add the filter: "select * from published tables where
> 1=1."?
> When and where would I change the filter?
> I do have a copy of your fine book - what sections are relevent to this
> particular issue.
> I presume the following isn't relevent to my circumstance as what I'm
> trying
> to do reduce network traffice. Or I have I misunderstood?
>
> Cheers, Peter
>
> "Hilary Cotter" wrote: