files/filegroups and overlapped I/O

MS SQL SERVER

    Sponsored Links

    Next

  • 1. Setup fail for SQL Server 2005
    I got the following error while install SQL Server 2005 Developer edition: 'The wrong volume is in the drive. Please insert volume SQLSRVRS into drive E:.' E is my installation CD drive. Can any one give me some idea about this?
  • 2. sql 2000 and sbs 2003
    We are about to buy a new server. I was wondering that in Small Business Server premium edition you get Sql server 2000. We already have a server with Server 2003 that is running sql server 2000. We plan on using the new server for your web server with a backend of sql server 2000. My question is does anyone see a problem with running sql 2000 on a standard 2003 server and sql 2000 on sbs 2003 server with the same database. If we were able to do this it would save us a great deal of money. Thanks, dennis basham
  • 3. Install Service Broker
    There are several options for installing SQL Server 2005 Dev, They are Database Service, Analysis Service, Report Service, Nitification Service, Integration Service, Workstation Components and others. However, I don't see an install option for installing Service Broker, why? Because it contains in Database Service? or contains in others? What kind of style does Service Broker run as? Does it run as a window service?

files/filegroups and overlapped I/O

Postby John Mott » Thu, 20 Oct 2005 22:19:00 GMT

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.

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?

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.

thanks,

john




Re: files/filegroups and overlapped I/O

Postby Andrew J. Kelly » Thu, 20 Oct 2005 23:33:45 GMT

See answers in-line:

-- 
Andrew J. Kelly  SQL MVP







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.


Answered above.



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.





Re: files/filegroups and overlapped I/O

Postby John Mott » Fri, 21 Oct 2005 00:06:35 GMT

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
threads
irrelevant.
program
you
on
too



Re: files/filegroups and overlapped I/O

Postby Andrew J. Kelly » Fri, 21 Oct 2005 13:59:46 GMT

gt; It does occur to me that a SQL server file is a container file with its

Well it's not that complicated really. TempDB on the other hand should
always be configured with multiple files if you have a lot of objects being
created due to the way SQL Server deals with new object creations such as
temptables and table vars.


And a single file can handle a lot more than 100K a day worth of inserts
without breaking a sweat. That is assuming you have a proper schema,
indexing scheme etc. As well as the proper hardware to support it. I can
tell you now that a two disk Raid 0 is not the kind of hardware you want for
that type activity if you want peak performance. The disks will limit your
performance far sooner than a single file will. Especially if the log file
is on the same drive as the data.



--
Andrew J. Kelly SQL MVP


"John Mott" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...



Re: files/filegroups and overlapped I/O

Postby Mercury » Fri, 21 Oct 2005 17:24:33 GMT

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...



Similar Threads:

1.file/filegroup backup vs copy the mdf, ndf, ldf file directly

Dear all, 
I have a question about the difference between file/filegroup backup and 
copy the mdf, ndf, ldf file directly
1.) If i copy the mdf,ndf,ldf file and then replace the file(recreate the db 
with the same name)  to the sql server, will it works and ok, what is the 
difference with backup file/filegroup backup?
2.) if i backup with sql script backup (select all object), is it the same 
output with full backup?

2.How to connect WINCE-OS and Win2000-OS with ActiveSync

Dear all!
I am going to try to connect WINCE OS system to the WIN2000 OS system wit
ActiveSync.but when I runed the ActiveSync on the Win2000 OS system,It's
cannot connect to the WINCE OS,by the way,I used Ethernet,and the Wince OS
can access the server's share file,that mean's the ethernet's successful,and
also can be used access the internet with IE in wince OS system.
Can anybody tell me how to do it?
Thanks.
Carol.


3.How to migrate 32bit OS SQL2005 Cluster to a 64bit OS SQL2005

Thanks for replying Geoff!  I actually failed to mention that they will be 
doing this on new hardware.  So with the old OS and SQL still intact, we will 
build a new Windows OS 64 bit, and build a new SQL 2005 32 bit cluster.

So being that we are using completely new hardware, is it just a matter of 
detaching the database from old cluster, and re-attaching to the new cluster? 
 Would I be able to refer to documents that talk about moving SQL database 
for this scenario?

SQL 2005 32 bit can run on Windows 64 bit OS ok?

Thanks!



"Geoff N. Hiten" wrote:

> That is pretty much a nuke and pave operation.
> 
> You can evict a node, reformat it, then build the new cluster around that 
> hardware, but that is tricky.  The data is fairly simple since the on-disk 
> formats do not vary.  You can detach from one cluster, switch zoning, import 
> the disks to the new cluster, define resources arounbd them and apply to the 
> new SQL instance.
> 
> Unfortunately I don't have any good public resources on that.  I have done 
> it before for a client, but I used Windows 2008 along with a mix of old and 
> new hardware since they wanted to expand the cluster anyway.
> 
> However you do this, careful planning is the key.  I am probably biased 
> towards the consultant business model, but this is a place where you might 
> find it worthwhile to hire somebody who has experience in cluster 
> migrations.
> 
> -- 
> Geoff N. Hiten
> Principal SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> 
> 
> "eternoamar" < XXXX@XXXXX.COM > wrote in message 
> news: XXXX@XXXXX.COM ...
> >I have a customer that currently has Windows 32 bit OS, running a SQL 2005 
> >32
> > bit cluster.  They want to install Windows 64 bit OS and a new SQL 2005 32
> > bit cluster.  Are there any procedures out there, on how the SQL data will 
> > be
> > migrated from one to the other?
> > 
> 
> 

4.How to migrate 32bit OS SQL2005 Cluster to a 64bit OS SQL2005 Clus

I have a customer that currently has Windows 32 bit OS, running a SQL 2005 32 
bit cluster.  They want to install Windows 64 bit OS and a new SQL 2005 32 
bit cluster.  Are there any procedures out there, on how the SQL data will be 
migrated from one to the other?

5.How to make two filegroups to one filegroup

Hello,

I have to filegroups like

database_data1  stored on d:
database_data2  stored on e:

How can I delete the database_data2 and tell SQL 2005 to copy all
data to database_data1?

Thanks for any help in advance!

A. Klemt 


6. Replication from Single-Filegroup to Multi-Filegroups

7. Create Database with Filegroups restore Database without Filegroup

8. DATA FILES/ FILEGROUPS



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 36 guest