Re: files/filegroups and overlapped I/O

I agree with Andrew.

Overlapped IO is a red herring in this context as SQL Server makes extensive
use of advanced programming techniques well beyond the average programmers
'toolkit'. IE it uses async IO everywhere it is appropriate & probably more
efficiently than veteran Win32 programmers would normally be able to achieve
(I am talking of myself). For an indication of this, I suggest you look at
and understand TPM/c (sp?) benchmarks to get an idea of the efficiency of
the s/w. Whatever F/S layout / RAID config the h/w has, SQL Server will use
the most efficient algorithmns appropriate to get the most out of the
hardware (including using multiple CPU's, RAM, and other system resources to
their maximum) - using filegroups for performance is wise under certain
configs - you will see where and when in BOL, but is irrelevant to
performance on a 2 disc RAID 0 config. This is not at all to say that
filegroups should not be used - they should be used - correctly.

" I'm aware that breaking items across drives will give SQL Server a chance
to do overlapped I/O". Breaking items across 2 HDD? I take it you mean RAID
0 striping. Not related to each other - with a RAID 0 config, the RAID can
do 2 concurrent IO's if they happen to be for the 2 different volumes. SQL
Server will issues multiple IO requests asynchronously when it wants to
already - if the disc subsystem has striping then it can service them
possibly concurrently. On a lightly loaded system this will be rarely. On a
loaded server class system then this will be more usual. RAID 0 is *never*
never worth it for performance (SQL Server) unless a) you can afford total
data loss at any point in time or b) it is actually RAID 10 or better
(mirrored and striped).

I suggest you get rid of the RAID 0 config before it does it for you - it is
prone to fail sooner than 2 x 1 discs.

Read up in BOL on SQL Profiler, MSDN for Windows Performance Monitor and
other related topics if performance is an issue. BOL is your SQL Server
Bible.

100,000 inserts is a small workload on a small system, so if the system is
having issues then I suggest a good definition of each of the issues is a
good place to start then try again.

I don't mean to discourage - my comments may sound like a bollocking,
however this is a "wrong track" - a lot of people look at RAID 0 without
looking at the consequences and sooner or later regret it.

HTH & BOL

"John Mott" <johnmott59@xxxxxxxxxxx> wrote in message
news:eRcjy6L1FHA.3300@xxxxxxxxxxxxxxxxxxxxxxx
>
> Thank you for your response. By overlapped I/O I meant either two schemes,
> asyncronous I/O operations or operations within a thread, so that answers
> the question.
>
> Performance is driving this but unfortunately I don't control the hardware
> environment so i'm trying to work with what i've got.
>
> It does occur to me that a SQL server file is a container file with its
> own
> mini-file system within it, so I do wonder what sorts of savings can be
> accomplished by breaking items across different files, even on a single
> drive and controller, so that SQL isn't having to manage space allocation
> between competing items. Especially for a database that changes a lot
> (100,000 inserts a day) that could lead to a lot of intra-file
> fragmentation, it would seem.
>
> john
>
> "Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message
> news:uCxsdoL1FHA.560@xxxxxxxxxxxxxxxxxxxxxxx
>> See answers in-line:
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>>
>> "John Mott" <johnmott59@xxxxxxxxxxx> wrote in message
>> news:evMgr%23K1FHA.3560@xxxxxxxxxxxxxxxxxxxxxxx
>> > Hello all,
>> >
>> > I'm working on a system with two disks as a striped RAID. I'm aware
>> > that
>> > breaking items across drives will give SQL Server a chance to do
>> > overlapped
>> > I/O, and that creating multiple files in filegroup will do that as
>> > well.
>> > It
>> > probably doesn't 'see' the raid, it probably sees the single file.
>>
>> Do you mean it is a Raid 0? What exactly do you mean by "overlapped
>> I/O"?
>> If you are referring to the ability of SQL Server to spawn multiple
> threads
>> to read or write to a file then the number of files is somewhat
> irrelevant.
>> In SQL 2000 (not 7.0) SQL Server can access a single file with multiple
>> threads all on it's own, you do not need multiple files. BOL is wrong in
>> this respect. When you get to very high volumes or tran rates it may
>> make
>> sense to have multiple files but if you only have a 2 disk Raid 0 it is
>> probably not worth your effort to use multiple files.
>>
>> >
>> > I have one table in one file in the primary filegroup on this system.
>> > If
>> > SQL
>> > Server sees one file will it be doing overlapped I/O operations to that
>> > one
>> > file, or does that only occur if items are in different files?
>>
>> Answered above.
>>
>>
>> > The answer may be different for inserts and for queries; i have a
> program
>> > doing insert operations and asp.net pages performing queries.
>> >
>> > With two drives on one controller there can be a maximum of two I/O's
>> > at
>> > once. Would it be better to create two files in this filegroup for this
>> > one
>> > table? I'm thinking that SQL Server might not want to operate multiple
>> > overlapped I/O's to any one file at a time, especially if insert
>> > operations
>> > are being performed.
>>
>> Each individual Insert for the most part is always single threaded
>> anyway.
>>
>> What exactly is driving this? If you are worried about performance then
> you
>> should consider getting different hardware. You ideally want the log
>> files
>> on their own Raid 1 and the data on a different Raid. What type depends
> on
>> your volume. If you are only doing a few trans a second it won't matter
> too
>> much what you use.
>>
>>
>> >
>> > thanks,
>> >
>> > john
>> >
>> >
>> >
>>
>>
>
>


.

Relevant Pages

  • RE: IIS & SQL
    ... it is best to place SQL Server on a separated ... database on a RAID 5 disk. ... RAID 5 provides redundancy of all data on the ... 298475 HOW TO: Troubleshoot Application Performance Issues ...
    (microsoft.public.sqlserver.setup)
  • Re: -- SQL 2005 memory (x64) --
    ... Test RAID5 and RAID10 for your environment if you have time. ... However, if write rate is high in your environment, than a RAID10 would be more appropriate for your data files. ... After deciding which RAID configurationis the best for your particular environment, then locate your database log files and data files on different physical disks according to those RAID choices. ... MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT ...
    (microsoft.public.sqlserver.setup)
  • Re: Can performance improve using RAID 0 if have only one CPU?
    ... Wayne Snyder, MCDBA, SQL Server MVP ... > "Frank" wrote in message ... >> I just read about the RAID, so I am wondering about this. ... >> performance improvement for the processor to go to different strips. ...
    (microsoft.public.sqlserver.server)
  • Re: Best way to install data files?
    ... With the disk setup that server has, install the SQL Server executables on ... the RAID 1, and the user database on the RAID 5. ...
    (microsoft.public.sqlserver.connect)
  • Re: Veritas storage foundation HA for windows
    ... This is a solution to manage your data, not the services like SQL Server. ... To make an application cluster suitable ("cluster aware" is for used for ... Most of the time that we have hardware failure, ... Hope you have multiple generators, multiple UPS's, multiple switches, ...
    (microsoft.public.sqlserver.clustering)
Loading