Pls recommend Raid 10 stripe size for SQL Server

Is there a rough rule of thumb for the stripe size to use for SQL Server
2000?
I am going to reorganise our disks. Assume 4 disk raid 10 for log and two 6
disk raid 10 sets for data.
Environment fairly mixed. Probably more reads than writes but still plenty
of updates etc.
Controllers have 128Mb battery backed up cache.
Thanks
Paul
Unless the controller manufacturer recommends otherwise specifically for SQL
server, go with their default stripe size. That is typically what the
hardware and firmware has been tuned to run. Any minor gains at the SQL
layer would likely be offset by a less than optimal setting at the
controller layer.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Is there a rough rule of thumb for the stripe size to use for SQL Server
> 2000?
> I am going to reorganise our disks. Assume 4 disk raid 10 for log and two
> 6 disk raid 10 sets for data.
> Environment fairly mixed. Probably more reads than writes but still plenty
> of updates etc.
> Controllers have 128Mb battery backed up cache.
> Thanks
> Paul
>
|||Thanks Geoff.
I guess I was just wondering give the more random nature of database calls
and sequential nature of log I/O.
Does Sql read/write in a particular batch/block size eg 8K.
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Unless the controller manufacturer recommends otherwise specifically for
> SQL server, go with their default stripe size. That is typically what the
> hardware and firmware has been tuned to run. Any minor gains at the SQL
> layer would likely be offset by a less than optimal setting at the
> controller layer.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>
|||Data pages are 8K. Extents (allocation units and read-ahead increments) are
64K. SQL servers tend to do a log more reading than writing. Sequential
data reads will probably be in 64K increments while random reads will be in
8K blocks. Log writes tend to be small and sequential, hence the
recommendation to separate logs and data onto separate spindle sets as you
have done. As I recommended before, let the controller work at its best
which is almost always the default settings.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
> Thanks Geoff.
> I guess I was just wondering give the more random nature of database calls
> and sequential nature of log I/O.
> Does Sql read/write in a particular batch/block size eg 8K.
> Paul
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>
|||It's a very rare opportunity to work on the system (24/7). Just wanted to do
all I could.
Thanks again
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:umcRsSbfFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Data pages are 8K. Extents (allocation units and read-ahead increments)
> are 64K. SQL servers tend to do a log more reading than writing.
> Sequential data reads will probably be in 64K increments while random
> reads will be in 8K blocks. Log writes tend to be small and sequential,
> hence the recommendation to separate logs and data onto separate spindle
> sets as you have done. As I recommended before, let the controller work
> at its best which is almost always the default settings.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
>

Pls recommend Raid 10 stripe size for SQL Server

Is there a rough rule of thumb for the stripe size to use for SQL Server
2000?
I am going to reorganise our disks. Assume 4 disk raid 10 for log and two 6
disk raid 10 sets for data.
Environment fairly mixed. Probably more reads than writes but still plenty
of updates etc.
Controllers have 128Mb battery backed up cache.
Thanks
PaulUnless the controller manufacturer recommends otherwise specifically for SQL
server, go with their default stripe size. That is typically what the
hardware and firmware has been tuned to run. Any minor gains at the SQL
layer would likely be offset by a less than optimal setting at the
controller layer.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Is there a rough rule of thumb for the stripe size to use for SQL Server
> 2000?
> I am going to reorganise our disks. Assume 4 disk raid 10 for log and two
> 6 disk raid 10 sets for data.
> Environment fairly mixed. Probably more reads than writes but still plenty
> of updates etc.
> Controllers have 128Mb battery backed up cache.
> Thanks
> Paul
>|||Thanks Geoff.
I guess I was just wondering give the more random nature of database calls
and sequential nature of log I/O.
Does Sql read/write in a particular batch/block size eg 8K.
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Unless the controller manufacturer recommends otherwise specifically for
> SQL server, go with their default stripe size. That is typically what the
> hardware and firmware has been tuned to run. Any minor gains at the SQL
> layer would likely be offset by a less than optimal setting at the
> controller layer.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>|||Data pages are 8K. Extents (allocation units and read-ahead increments) are
64K. SQL servers tend to do a log more reading than writing. Sequential
data reads will probably be in 64K increments while random reads will be in
8K blocks. Log writes tend to be small and sequential, hence the
recommendation to separate logs and data onto separate spindle sets as you
have done. As I recommended before, let the controller work at its best
which is almost always the default settings.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
> Thanks Geoff.
> I guess I was just wondering give the more random nature of database calls
> and sequential nature of log I/O.
> Does Sql read/write in a particular batch/block size eg 8K.
> Paul
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>|||It's a very rare opportunity to work on the system (24/7). Just wanted to do
all I could.
Thanks again
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:umcRsSbfFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Data pages are 8K. Extents (allocation units and read-ahead increments)
> are 64K. SQL servers tend to do a log more reading than writing.
> Sequential data reads will probably be in 64K increments while random
> reads will be in 8K blocks. Log writes tend to be small and sequential,
> hence the recommendation to separate logs and data onto separate spindle
> sets as you have done. As I recommended before, let the controller work
> at its best which is almost always the default settings.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
>

Pls recommend Raid 10 stripe size for SQL Server

Is there a rough rule of thumb for the stripe size to use for SQL Server
2000?
I am going to reorganise our disks. Assume 4 disk raid 10 for log and two 6
disk raid 10 sets for data.
Environment fairly mixed. Probably more reads than writes but still plenty
of updates etc.
Controllers have 128Mb battery backed up cache.
Thanks
PaulUnless the controller manufacturer recommends otherwise specifically for SQL
server, go with their default stripe size. That is typically what the
hardware and firmware has been tuned to run. Any minor gains at the SQL
layer would likely be offset by a less than optimal setting at the
controller layer.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Is there a rough rule of thumb for the stripe size to use for SQL Server
> 2000?
> I am going to reorganise our disks. Assume 4 disk raid 10 for log and two
> 6 disk raid 10 sets for data.
> Environment fairly mixed. Probably more reads than writes but still plenty
> of updates etc.
> Controllers have 128Mb battery backed up cache.
> Thanks
> Paul
>|||Thanks Geoff.
I guess I was just wondering give the more random nature of database calls
and sequential nature of log I/O.
Does Sql read/write in a particular batch/block size eg 8K.
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Unless the controller manufacturer recommends otherwise specifically for
> SQL server, go with their default stripe size. That is typically what the
> hardware and firmware has been tuned to run. Any minor gains at the SQL
> layer would likely be offset by a less than optimal setting at the
> controller layer.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Is there a rough rule of thumb for the stripe size to use for SQL Server
>> 2000?
>> I am going to reorganise our disks. Assume 4 disk raid 10 for log and two
>> 6 disk raid 10 sets for data.
>> Environment fairly mixed. Probably more reads than writes but still
>> plenty of updates etc.
>> Controllers have 128Mb battery backed up cache.
>> Thanks
>> Paul
>>
>|||Data pages are 8K. Extents (allocation units and read-ahead increments) are
64K. SQL servers tend to do a log more reading than writing. Sequential
data reads will probably be in 64K increments while random reads will be in
8K blocks. Log writes tend to be small and sequential, hence the
recommendation to separate logs and data onto separate spindle sets as you
have done. As I recommended before, let the controller work at its best
which is almost always the default settings.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
> Thanks Geoff.
> I guess I was just wondering give the more random nature of database calls
> and sequential nature of log I/O.
> Does Sql read/write in a particular batch/block size eg 8K.
> Paul
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Unless the controller manufacturer recommends otherwise specifically for
>> SQL server, go with their default stripe size. That is typically what
>> the hardware and firmware has been tuned to run. Any minor gains at the
>> SQL layer would likely be offset by a less than optimal setting at the
>> controller layer.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Paul Cahill" <nospam@.hotmail.com> wrote in message
>> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Is there a rough rule of thumb for the stripe size to use for SQL Server
>> 2000?
>> I am going to reorganise our disks. Assume 4 disk raid 10 for log and
>> two 6 disk raid 10 sets for data.
>> Environment fairly mixed. Probably more reads than writes but still
>> plenty of updates etc.
>> Controllers have 128Mb battery backed up cache.
>> Thanks
>> Paul
>>
>>
>|||It's a very rare opportunity to work on the system (24/7). Just wanted to do
all I could.
Thanks again
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:umcRsSbfFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Data pages are 8K. Extents (allocation units and read-ahead increments)
> are 64K. SQL servers tend to do a log more reading than writing.
> Sequential data reads will probably be in 64K increments while random
> reads will be in 8K blocks. Log writes tend to be small and sequential,
> hence the recommendation to separate logs and data onto separate spindle
> sets as you have done. As I recommended before, let the controller work
> at its best which is almost always the default settings.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
>> Thanks Geoff.
>> I guess I was just wondering give the more random nature of database
>> calls and sequential nature of log I/O.
>> Does Sql read/write in a particular batch/block size eg 8K.
>> Paul
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Unless the controller manufacturer recommends otherwise specifically for
>> SQL server, go with their default stripe size. That is typically what
>> the hardware and firmware has been tuned to run. Any minor gains at the
>> SQL layer would likely be offset by a less than optimal setting at the
>> controller layer.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Paul Cahill" <nospam@.hotmail.com> wrote in message
>> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Is there a rough rule of thumb for the stripe size to use for SQL
>> Server 2000?
>> I am going to reorganise our disks. Assume 4 disk raid 10 for log and
>> two 6 disk raid 10 sets for data.
>> Environment fairly mixed. Probably more reads than writes but still
>> plenty of updates etc.
>> Controllers have 128Mb battery backed up cache.
>> Thanks
>> Paul
>>
>>
>>
>

Please help! SQL Server crash dump

Help,
A server became unresponsive yesterday, starting with disk write errors that
we've seen before,
but then around 9am this morning a crash dump was written.
Can anybody help me to chase this down. I think the best resolution is PSS,
but I'd like to get
as much info as I can before making a call.
2003-07-30 16:40:40.90 server Error: 17883, Severity: 1, State:
2003-07-30 16:42:19.68 spid54 Time out occurred while waiting for buffer
latch type 2,bp 0x278e640, page 1:4425087), stat 0x40d, object ID
46:517576882:0, EC 0x5973F580 : 0, waittime 300. Not continuing to wait.
2003-07-30 16:40:40.90 server Process 0:0 (b18) UMS Context 0x12863740
appears to be non-yielding on Scheduler
2003-07-31 09:00:35.96 spid54 Waiting for type 0x2, current count
0x100022, current owning EC 0x5973F580.
2003-07-31 09:00:37.64 server Sleeping until external dump process
completes.
2003-07-31 09:00:46.53 server Resuming after waiting on external debug
process for 2 seconds.
2003-07-31 09:00:46.53 server Stack Signature for the dump is 0x00000000
TIA.Have a look at
PRB: Common Causes of Error Message 844 or Error Message 845 (Buffer Latch
Time Out Errors)
http://support.microsoft.com/default.aspx?scid=kb;en-us;310834
and also
INF: New Concurrency and Scheduling Diagnostics Added to SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;319892
which contains this note about 17883 errors
"Microsoft tries to keep all content up-to-date with the latest 17883
conditions. However, the 17883 error message is a health detection message
that can be triggered for many reasons. Microsoft has not only corrected
known issues with the SQL Server software product but has also encountered
the 17883 error in a variety of situations that are unrelated to the SQL
Server software. For example, the error has occurred with external
application CPU consumption and hardware failures. You must determine the
root cause of the 17883 error message if you want to avoid an unwanted
reoccurrence of the error. "
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Stressed" <k@.c.co.uk> wrote in message
news:uQ4C7a0VDHA.2156@.TK2MSFTNGP11.phx.gbl...
Help,
A server became unresponsive yesterday, starting with disk write errors that
we've seen before,
but then around 9am this morning a crash dump was written.
Can anybody help me to chase this down. I think the best resolution is PSS,
but I'd like to get
as much info as I can before making a call.
2003-07-30 16:40:40.90 server Error: 17883, Severity: 1, State:
2003-07-30 16:42:19.68 spid54 Time out occurred while waiting for buffer
latch type 2,bp 0x278e640, page 1:4425087), stat 0x40d, object ID
46:517576882:0, EC 0x5973F580 : 0, waittime 300. Not continuing to wait.
2003-07-30 16:40:40.90 server Process 0:0 (b18) UMS Context 0x12863740
appears to be non-yielding on Scheduler
2003-07-31 09:00:35.96 spid54 Waiting for type 0x2, current count
0x100022, current owning EC 0x5973F580.
2003-07-31 09:00:37.64 server Sleeping until external dump process
completes.
2003-07-31 09:00:46.53 server Resuming after waiting on external debug
process for 2 seconds.
2003-07-31 09:00:46.53 server Stack Signature for the dump is 0x00000000
TIA.