Re: Determine Disk Block Size ?

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/31/05 

Date: Mon, 31 Jan 2005 08:03:20 -0500

An I agree with your comments except for the last one:
>>>
 Furthermore, more files per Filegroup will spawn
additional disk threads per request.
<<

That was true with 7.0 but is not necessarily true with SQL2000. In 2000
Sql Server can spawn multiple threads to read a single file as well. So
just having multiple files does not mean it will be more effecient or spawn
more threads than a single file. 

-- 
Andrew J. Kelly  SQL MVP
"Anthony Thomas" <ALThomas@kc.rr.com> wrote in message 
news:ep16rw1BFHA.3592@TK2MSFTNGP09.phx.gbl...
> Not that I disagree with the RAID-10 argument, I don't; however, I do
> disagree with your comments about NTFS Cluster Sizes (a.k.a., block size).
>
> The MS SQL Server Operations Manual makes a recommendation of using 64-KB
> cluster sizes.  We have done so with a dramatic total disk I/O throughput.
> It makes sense.  SQL Server reads and writes data in 8KB data pages, but
> issues Scatter-Gather I/O requests in 4 or more Extents (8 x 8KB pages)
> simultaneously.  If your block sizes are on these 8KB boundaries, SQL 
> Server
> Disk I/O requests can be more efficient.
>
> Your biggest disk throughput bottleneck is total number of I/O Operations 
> a
> single spindle can handle.  You're throughput will be based on type of
> access, sequential versus random, and the size of each request. 
> Optimizing
> the request sizes coupled with a fixed maximum number of I/O Ops, should
> make your system as efficient as possible.
>
> Nevertheless, and along the same lines as the RAID arguments already
> presented, this is also suggested from the Operations Manual: more smaller
> disks are more efficient than fewer larger disks.  This would come into 
> play
> in the 0 part of the RAID-10 argument.  Striping allows more physical
> spindles to fulfill requests, each one capable of servicing a theoretical
> maximum number of I/O Ops.  Furthermore, more files per Filegroup will 
> spawn
> additional disk threads per request.  This coupled with number of disks 
> can
> greatly improve throughput.
>
> Sincerely,
>
>
> Anthony Thomas
>
>
> -- 
>
> "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
> news:uY2FhGsBFHA.3840@tk2msftngp13.phx.gbl...
> Hi
>
> Rather make sure that your drives are configured correctly.
> Make sure your logs on RAID-10, and preferably your data on a separate
> RAID-10 volume. RAID-5 hurts more than it helps.
> As Geoff said, block size is not important, but getting maximum IO
> throughput is.
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: mike@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "Geoff N. Hiten" <SRDBA@Careerbuilder.com> wrote in message
> news:uhfJBgXBFHA.2180@TK2MSFTNGP12.phx.gbl...
>> Unless you have an overriding reason, go with the hardware manufacturer's
>> recommendations.  These are typically the default settings.  Changing the
>> NTFS block size usually has no measurable effect on system performance.
>>
>> -- 
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>>
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>>
>> "Joe K." <Joe K.@discussions.microsoft.com> wrote in message
>> news:B77A9710-86AA-4319-B539-6F65D91E5ECA@microsoft.com...
>> >
>> > I have a SQL Server 2000 Enterprise database that processes 300
>> > transactions/sec, approximately 300 users, and 250 GB in size.  How can
> I
>> > determine the the appropriate disk block size that should be on my
> system.
>> >
>> > Thanks,
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: Determine Disk Block Size ?
    ... > "SQL Server limits a single read-ahead request depth to 128 pages on most ... Microsoft SQL Server Enterprise Edition raises the ... > that it may state you need multiple files to spawn multiple threads. ... We have done so with a dramatic total disk I/O ...
    (microsoft.public.sqlserver.server)
  • Re: Determine Disk Block Size ?
    ... "SQL Server limits a single read-ahead request depth to 128 pages on most ... The read ahead is actually dynamic in the size of each read ahead request based somewhat on how fragmented the data is. ... Most of the documentation for 2000 is wrong in that it may state you need multiple files to spawn multiple threads. ... We have done so with a dramatic total disk I/O>> throughput. ...
    (microsoft.public.sqlserver.server)
  • Re: Determine Disk Block Size ?
    ... that it may state you need multiple files to spawn multiple threads. ... > mentioned that EE can exceed the number of concurrent extents per request ... > Sql Server can spawn multiple threads to read a single file as well. ... We have done so with a dramatic total disk I/O ...
    (microsoft.public.sqlserver.server)
  • Re: Determine Disk Block Size ?
    ... more files per Filegroup will spawn additional disk threads per request. ... In 2000 Sql Server can spawn multiple threads to read a single file as well. ...
    (microsoft.public.sqlserver.server)
  • [PATCH -mm] [3/3] Add the Elevator I/O scheduler
    ... This is the Elevator I/O scheduler. ... with a couple tunables for reducing latency or starvation. ... disk controllers and multiple brands of disk. ... * We usually wish to take the next request from the head queue. ...
    (Linux-Kernel)